InnerJoin error (I think)

[libraccess]

I have a report where I wanted to add field [TankerRefNo] from tblCharges_Tanker


I have included the field in the query but now my report only returns values that include an entry in that field not all entries. I have been playing with the InnerJoin to no avail and now I am grey

SELECT tblMainDel.*, tblContractor.NameContr, tblSource.NameSource, tblCharges_Tanker.TankerRefNo
FROM tblSource INNER JOIN (tblContractor INNER JOIN (tblCharges_Tanker INNER JOIN tblMainDel ON tblCharges_Tanker.ID = tblMainDel.TankerRefFK) ON tblContractor.ID = tblMainDel.ContractorFK) ON tblSource.ID = tblMainDel.SourceFK
WHERE (((tblSource.NameSource)<>"Transfer From"));

where is this wrong please

[jzwp11]

You do not want an INNER JOIN where tblCharges_Tanker is involved. I am guessing, but I think this is what you may be after. I assume that you want all records from tblMainDel and the records from tblCharges_Tanker that match:

FROM tblSource INNER JOIN (tblContractor INNER JOIN (tblMainDel LEFT JOIN tblCharges_Tanker ON tblMainDel.TankerRefFK= tblCharges_Tanker.ID ) ON tblContractor.ID = tblMainDel.ContractorFK) ON tblSource.ID = tblMainDel.SourceFK

[libraccess]

Me.Satisfied I spliced your code in to my statement and all is good. yes I wanted to return records to the report even if they did not include a [TankerRefNo]
Thankyou Sir

[jzwp11]

Glad that worked for you.