Howdy there,

Could you please be more specific? What software are you using?

Thanks,
Jenny
Community Manager

Sorry, I am using Access database. I have one database with customer information linked to another that show the multiple dates that service was performed for each customer. I want to come up with a list of customers and their information that shows only the latest date that service was performed for each. I don’t know how to filter the dates to select only the latest.

thanks

Check out this link on working with duplacates.
http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CL100570041033#12
In the query sort buy customer then sort the date field desending and have it remove the dup’s.

Thanks for the answer on duplicates, but I am not trying to remove duplicate dates, just the dates prior to the most recent. Hope this helps.

The “cleanest” way is to use a function that returns the latest date of service, but you can achieve the same result using a “not exists” clause.
Assume you have:
MyAddrTable (
TheAccount varchar(255),
TheAddress varchar(255));
MyServiceTable (
TheAccount varchar(255),
DateOfService datetime);

Then:

select
addr.TheAddress, mst1.DateOfService
from
MyAddrTable addr with (nolock)
join MyServiceTable mst1 with (nolock) on (mst1.TheAccount = addr.TheAccount)
where not exists (select 1 from MyServiceTable mst2 with (nolock)
where mst2.TheAccount = mst1.TheAccount and mst2.DateOfService > mst1.DateOfService)

This will return one row for each of the address records, with the latest date of service for that address.

How about:

SELECT a.name, a.address, max(s.DateOfService)
FROM MyAddrTable a JOIN MyServiceTable s
ON a.address = s.address
GROUP BY a.name, a.address

?

Something went wrong with the formatting…

How about something like this ?

SELECT a.name, a.address, max(s.DateOfService)
FROM MyAddrTable a JOIN MyServiceTable s
ON a.address = s.address
GROUP BY a.name, a.address