Group By Query Problem

[Marty]

’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
Edit/Delete Message

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[Marty]

Hello,
I followed your advice and went to the link you suggested. I used the query
(with my table and field names of course):
SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q
WHERE DateOut = (SELECT Max(T.DateOut)
FROM TableName As T
WHERE T.BookID = Q.BookID)

But it created a tabel so large I don't even know. It should have selected
about 270 records. Very frankly, I do not understand the above query. Why
do I need to create an alias to achieve the results I'm looking for?

 

[Allen Browne]

You say that the problem is when you add extra fields?

You could solve this by using a subquery, but if you cannot get that to
work, pehaps you could save the query that does work, and then use it as the
source for another query.

 

[Marty]

For those who use the search (as I do) to find answers to common questions.
This Query worked:

SELECT
a.*
FROM
table_name a
INNER JOIN
(SELECT
[name],
MAX([date]) AS max_date
FROM
table_name
GROUP BY
[name]
) AS b
ON
(a.[name]=b.[name]) AND
(a.[date]=b.[max_date])

....the a and the b are aliases for table_name.

when you self join a table, as in this case, you must alias at least one of
the tables to clarify which instance your sql instructions are to be applied
to.

some people will also refer to the max_date in the imbedded select statement
as an alias (a column alias), but i prefer to refer to it as a label for
clarity's sake.

P.S. the above query & statement is written by a person with the handle of
bubbablaze.

Thank you to Allen Browne and others for taking the time to help. It was
very much appreciated!

Cheers, Marty