I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables.
Which JOIN type should I use to do this?
SELECT SLD.SLD_Number,
SLD.Date_Received,
SLD.Time_Received,
[Payment table].Date_Received,
IIf(Payment_Status = 'NOT PAID', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr1,
IIf(Payment_Status='PENDING', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr2,
DateDiff("d",[Invoice table].Date_Received,
[Payment table].Date_To_Payment) AS Expr3,
DateDiff("d",[Order table].Date_Quotes_Requested,SLD.Date_Received) AS Expr4,
DateDiff("d",[Order table].Date_Of_Order_Number,SLD.Date_Received) AS Expr5,
IIf(Payment_Status = 'PAID', DateDiff("d",[Invoice table].Date_Received, [Payment table].Date_Authorised_Paid)) AS Expr6,
IIf([Invoice table].Date_Received=NULL,'TRUE','FALSE') AS Expr7,
Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM (([Payment table]
INNER JOIN [Invoice table]
ON [Payment table].SLD_ID = [Invoice table].SLD_ID)
INNER JOIN [Order table]
ON [Payment table].SLD_ID = [Order table].SLD_ID)
LEFT JOIN SLD
ON [Payment table].SLD_ID = SLD.SLD_ID;
RIGHT JOIN is bringing up this error:
Join expression not supported. (Error 3296)
Best Answer
This seems to have worked: