Query that returns LAST date IF Last date is more than 30 days ago

[joey]

Hi,
We have service reps that call on stores to service the display racks. I need a report that shows all stores that have not been serviced in the last 30 days and the result should only show the Last service date (one record) for that store.



Table Fields are:

• Service Rep
• Store Name
• Service Date

Thank you!
Joey

[orange]

Not LAST, but Max. For each store you want the Max (ServiceDate).

[joey]

Thank you - that worked and it returns the last service date for each store.
I now need that to be as of 30 days ago - meaning if there was no service for the last 30 days then that store (with service date) should be in the result. If there was a service within that last 30 days then it shoul not be in the result. Thank you!

[orange]

Where datediff("d",Date, Max(ServiceDate)) >30