Group By Query

[Les]

Hello
I'm struggling to explain this but here goes.

I have a table which contains 6 fields which make up the location, 1 field
which gives the type of recording and 1 field which gives the date of the
recording.

Lets say there are 3 types of recording so I have 3 records for each location.
I'd like the query to return the most recent date for each location
regardles of type of recording but I still need to include the type of
recording data in the query.

Many thanks

 

[golfinray]

When you do group by, (the little E on the toolbar) and the group by comes up
on the query grid, you can change that to Last on your date field. That
should give you the last date.

 

[Les]

Sorry, no I'm still getting a record for each recording type, not the latest
one only.

Cheers.

 

[John Spencer]

NO!

LAST does not return the latest record by date. It returns the last record
that the database engine accesses (for the group) while creating the
recordset. If you want the latest date, you use the MAX function.

You need a query to give you the maximum date per location and then use that
to get identify the records to return.

SELECT FIELD1, Field2, Field3, Field4, field5, field6
, Max(DateField) as LatestDate
FROM SomeTable
GROUP BY FIELD1, Field2, Field3, Field4, field5, field6

Then you use that in another query where you join the seven fields of the
query to the corresponding seven fields of the table.

You can then include whatever fields you want to display from the table.

If you don't know how to construct the query in SQL view or can't figure out
from the above how to build the query in design view, construct a query that
uses the 8 fields involved and then post the sql of a query that shows the
eight fields involved. Someone should be able to help you create the necessary
query.

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


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[golfinray]

John, I knew that. Thanks for the correction. I'm way past brain dead this
morning. Milt

 

[KARL DEWEY]

Try this using your table and field names --
SELECT YourTable.*
FROM YourTable
WHERE YourTable.RecordingDate = (SELECT Max([XX].[RecordingDate]) FROM
YourTable AS [XX] WHERE YourTable.Location = [XX].Location)
ORDER BY YourTable.Location;