querry newest service date

J

justsomedumbguy

I have a querry named "reminder labels" that get data from "Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest service date for multiple customers, every customer has multiple dates. I tried DMax("Date of Service","Service") but if john doe has a date 7/12/2004 which is the newest i will only get that date even though joe blows newest date is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name], Customers.[Last Name], Customers.Address, Customers.City, Customers.State, Customers.[Zip Code], Customers.[Filter?], Service.[Date of Service]
FROM Customers INNER JOIN Service ON Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And [EndingTank#]));
 
G

Gerald Stanley

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a querry named "reminder labels" that get data from
"Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest
service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date
Here is my SQL
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]
FROM Customers INNER JOIN Service ON Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And [EndingTank#]));



.
 
J

justsomedumbguy

Thank so much it worked great! however I changed my mind on selecting Starting Tank & ending Tank I would like to select between dates

Gerald Stanley said:
Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a querry named "reminder labels" that get data from
"Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest
service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date
Here is my SQL
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]
FROM Customers INNER JOIN Service ON Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And [EndingTank#]));



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top