Ms-access – JOIN type for MS-Access report query

join;ms access

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:

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','No') AS Expr7, 
Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM (([Payment table] RIGHT OUTER JOIN SLD ON SLD.SLD_ID = [Payment 
table].SLD_ID) LEFT OUTER JOIN [Order table] ON [Payment table].SLD_ID = 
[Order table].SLD_ID) LEFT OUTER JOIN [Invoice table] ON [Invoice 
table].SLD_ID = [Payment table].SLD_ID;
Related Question