querry newest service date

[Guest]

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#]));

 

[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#]));



.

 

[Guest]

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

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#]));



.