Left Join?

[Seb]

Hi Guys

I'm kind of a newbie to Access but am learning
I've found myself stuck with a join statement

I have 2 tables: tblFleet & tblService

I need to find the most recent service date from tblService for each entry under tblFleet....

This is my code thus far, but it shows all the records for both tables where the fleet_Num field is equal....

SELECT tblFleet.Fleet_Num, tblFleet.In_Yard, tblService.SrvDate
FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num
ORDER BY tblService.SrvDate DESC;

 

[ColinEssex]

Just as a check. Can you list the fields you have in your tables and indicate where they are joined.

Your query is working correctly (I think) because you are not specifying any dates so it will pull all data.

Col

 

[Seb]

G'day Colin

thanks for the reply

here's the the list of fields:
tblFleet
id
fleet_Num
Fleet_type
Bearing_Next
Bearing_Last
In_Yard
tblService
id
SrvDate
Fleet_Num
Odometer
ServiceType
ServicedBy
DiffOils
GearBoxOils
ValveAdjust

What I need to do is get a list of each tblFleet.fleet_Num and then find any services thats happened within the last 4 days....Even if the fleet_num wasn't serviced within the last 4 days (ie. just draw a blank)