Include a field in an aggregate query that doesn't affect the query 1

[TheFoxy]

Hey,

I have a query on a table ('Services') with fields: ID, ItemNumber and Date. There are many services for each item. ID is the keyfield.

I want to pull out the record with the most recent date for each individual ItemNumber. I have done this with what I think is called an 'aggregate' query , using 'Max' on Date and 'Group By' on ItemNumber. The query works, but I also need the ID field to identify the record. As soon as I put it in the query, whichever option I choose it wrecks the query. On 'Where' it doesn't wreck it, but the field isn't included, so when I pass the results of the query to another query, the field isn't there.

How do I include the ID field in the query result's records without altering the query result? I basically need to include it with a 'Don't use this as criteria' option, if you get what I mean.

Any help on this would be very much appreciated.

(I'm using Access 2000)

 

[lupins46]

You can't include it in the Totals query.

You have to create a second query which is based on a join between the original table and the totals query.
Join the tables on Itemnumber to itemnumber and [Date] to maxofdate. You can then include other fields from the original table.

 

[TheFoxy]

Ummm... how would I go about doing that? lol (I'm not a frequent Access user)

 

[lespaul]

Why doesn't this work:

Select ID, ItemNumber, Max([Date]) from Services Group By ItemNumber, ID

You should be able to add any field to the select statement as long as you also add it to the Group By statement.

 

[JonFer]

Use a subquery to get the max date for the item in a single query:

Select ID, ItemNumber, [Date]
from Services
Where [Date]=(Select Max(a.[Date]) from Services as a
              Where a.ItemNumber=Services.ItemNumber)

 

[lupins46]

Lespaul:
It doesn't meet the stated requirement:
'I want to pull out the record with the most recent date '
Even assuming that you get the corresponding ID for the max date in the grouped query, you are not looking at the record, you are looking at a grouped query result.
I'm not saying that you may not meet some specific viewable information requirements using your idea but you can't get to tha actual record.

 

[TheFoxy]

Your method works JonFer, thanks!