Simple problem, but maybe I'm stupid

[Sam Summers]

I quite simply have this query and others similar to this (below).

But there may be records that do not have a 'CertNo' but I just want to show all the records of this type with or without a 'CertNo' therefore some fields on the form may be blank.

Here is the SQL:-

SELECT Equipment.EquipmentID, Location.JobNo, Equipment.LocationID, EquipLookup.EquipDescription, EquipLookup.ENNumber, Equipment.EquipRef, Equipment.ItemNo, Equipment.TestDate, ConformancyCerts.CertNo
FROM Location INNER JOIN (EquipLookup INNER JOIN (ConformancyCerts INNER JOIN Equipment ON (ConformancyCerts.CertID = Equipment.CertID) AND (ConformancyCerts.CertID = Equipment.CertID)) ON (EquipLookup.EquipRef = Equipment.EquipRef) AND (EquipLookup.EquipRef = Equipment.EquipRef)) ON (Location.LocationID = Equipment.LocationID) AND (Location.LocationID = Equipment.LocationID)
WHERE (((EquipLookup.EquipDescription)=[Forms]![FindAccess]![Item].[Text]))
ORDER BY EquipLookup.EquipDescription, Equipment.ItemNo;


I have tried various combinations like 'Is Null' in the 'CertNo' column etc but just can't seem to get my head round it.

If anyone can help

THANK YOU !!!!!!!

 

[FoFa]

Open your query, on your join to the table containing (or not containing) cert no, dblclick the join line and select the proper join. With those inner joins it means all data has to exist to return a row, you want an outer join (LEFT or RIGHT) and that is the easiest way to do it.

 

[Pat Hartman]

You do need to change the join type but you have another problem. The join criteria seems to be duplicated for each join. Look at the SQL after you change the join type and make sure that the duplication has been eliminated. If it hasn't you'll need to fix it yourself. The duplication is caused by an Access bug but I don't remember what precipitates it.

 

[Sam Summers]

Thanks very much to both of you for your help.

I have implemented your suggestions and it seems to be working fine now.