querie

[Sherry-Septic Tank Pumper]

I am trying to run build a querie that will give me each customer I have done
a job for, but only list them by the last date. (we may have performed the
same service numerous times for some customers.) problem: When I run the
querie, It will list the same customer for every date we have done them. How
can I restrict this to listing them only one time, the last time we serviced
them?
My knowledge is very limited. This original business program was designed
by an expert 11 years ago & with the updates in Access & windows versions,
the program is no longer working as designed.

 

[Tom Ellison]

Dear Sherry:

If the only columns you need to see are the customer and the date:

SELECT Customer, MAX(ServiceDate) AS MostRecentService
FROM YourTable
GROUP BY Customer

If you want to see all the details of the service performed at that time:

SELECT *
FROM YourTable T
WHERE ServiceDate = (SELECT MAX(ServiceDate)
FROM YourTable T1
WHERE T1.Customer = T.Customer)

Of course I'm just guessing about the table and column names. You'd need to
fix that up.

The first query is a simple "totals query" with the aggregate MAX() function
to find this date.

The second is a "correlated subquery" that allows you to see all the details
stored in that row. It is largely based on the first query. You might want
to look up topics for "correlated subquery" and "alias".

If you have two rows for the same customer with the same SerivceDate, and
that's the most recent service for that customer, then both will show up.

Tom Ellison


"Sherry-Septic Tank Pumper" <Sherry-Septic Tank
(e-mail address removed)> wrote in message
news:[email protected]...