Null values

[browny]

Hi all
I have 3 linked tables in a vehicle database. 1 keeps a record of Service Due date, 1 has the Fleet numbers and the last contains Vehicle Condition reports.
They are linked by the Fleet numbers. I have a query that returns the Services due between 2 dates. What I would like is to have the same query return any outstanding Vehicle Condition items as well for any Vehicles due for servicing. Currently, I can include the field in the query but if there are no current items for a vehicle in the Vehicle Condition table then the query does not return that vehicle as a result. Any suggestions would be appreciated.

Regards
Craig

 

[neileg]

You need to change your joins. The 'master table' is the one with the Fleet Numbers. You need to join each of the other two tables to this one on the fleet number. Do not join Service Due and Vehicle condition tables to each other only to the fleet number table. These joins need to be left joins, ie return all of the records from the fleet number table and those records from the other tables that match.

You should then get a record for every fleet number regardless of whether there is a matching entry in the other tables.

 

[browny]

Hi Neil,
Worked perfectly, thanks for the advise, double checking and modifying the relationship propeties made all the difference.

Regards
Craig