Select Max

[Marty]

I’m having the simplest query problem but for the life of me I cannot not
figure it out. I have a table it looks like this:

Name Date Price Location
Smith 03/03/05 12.00 Dallas
Smith 03/04/05 13.00 Dallas
Smith 03/05/05 12.00 Dallas
Smith 03/06/05 14.00 Dallas
Jones 03/03/05 12.00 Ft. Worth
Jones 03/04/05 14.00 Ft. Worth
Jones 03/05/05 12.00 Ft. Worth
Jones 03/06/05 12.00 Ft. Worth

I want to do a Group by Name, Date, Max(Price), Location. The results of the
Query I want would look like this:
Smith 03/06/05 14.00 Dallas
Jones 03/04/05 14.00 Ft. Worth

What keeps happening to me is: I can do a query that will Group by Name and
Max(Price) and I get:
Smith 14.00
Jones 14.00
But if I add any other field – say – Date then I get an output that looks
like this:
Smith 03/03/05 12.00
Smith 03/04/05 13.00
Smith 03/05/05 12.00
Smith 03/06/05 14.00
Jones 03/03/05 12.00
Jones 03/04/05 14.00
Jones 03/05/05 12.00
Jones 03/06/05 12.00
…meaning everything. I’m at the point I want to scream! I cannot not
believe I can’t figure this out. Can anyone help?

Marty

 

[Steve Schapel]

Marty,

Make a query with Name (Group By), Location (Group By), Price (Max)
Then make a second query, which includes this first query plus the
original table, joined on all 3 fields, so you can include the Date.

This will take care of the situation except where the maximum price
occurs on more than one date, so you will need to decide how to handle this.

By the way, as an aside, Name and Date are both Reserved Words (i.e.
have a special meaning) in Access, and as such should not be used as the
name of a field or control.