Easy query, Last Date..?

[RHomsey]

Im attempting to create a query in which will show the most recent pump dates for all entries ... or for specific entries.. (meaning i dont want to have to type in every store number, becuase sometimes it will be over 100 stores that i need to find the last pump date). This shouldnt be too hard for you access wizards out there... Thanks a bunch!

 

[Fizzio]

In your date field in the query, set the totals to Max and the Store Number to Group By.

If you don't already know, to enable the totals row, click the sigma (button on the toolbar similar to pointy E).

 

[RHomsey]

It doesnt seem to be working. When i set my date field to max... it still brings up everything. Any ideas??

 

[Fizzio]

Have you got group by on more than one field and if so, could they be returning all records? Post your SQL.

 

[RHomsey]

The group by is present on all fields except for the one field (pump dates) which is set to max. It still brings up all records... here is my SQL:

SELECT [Grease Trap Maintenance].[Company Name], [Grease Trap Maintenance].[Store #], [Grease Trap Maintenance].City, [Grease Trap Maintenance].State, [Grease Trap Maintenance].Zone, Max([Grease Trap Maintenance].[Pump Date]) AS [MaxOfPump Date], [Grease Trap Maintenance].NOTES
FROM [Grease Trap Maintenance]
GROUP BY [Grease Trap Maintenance].[Company Name], [Grease Trap Maintenance].[Store #], [Grease Trap Maintenance].City, [Grease Trap Maintenance].State, [Grease Trap Maintenance].Zone, [Grease Trap Maintenance].NOTES
HAVING ((([Grease Trap Maintenance].[Company Name])="Waffle House"));

Thanks a bunch for the help...