SQL Twit here... Can anybody help me with this?

[cathie]

I am trying to return all rows even if there are no expense entries in the ExpenseLog table. I'm pretty sure I have superfluous stuff in here. Unfortunately, I know close to nothing about SQL . I am getting a "'JOIN' expression not supported" error from Access 2016. I am trying to link 2 queries and a table with the commonality being LoanID (I only need to see it once). I tried adding all 3 LoanID's but that's not helping... The other 2 will always have data in them, the expense log, however, may not. Please help!

SELECT [Purchase Info per Note].LoanID, [Purchase Info per Note].PurchasePrice, Sum(ExpenseLog.NetAmount) as TotalExpenses, NoteInformationQuery.Term, NoteInformationQuery.LoanID, ExpenseLog.LoanID
FROM [Purchase Info per Note] LEFT JOIN (NoteInformationQuery LEFT JOIN ExpenseLog ON ExpenseLog.LoanID = NoteInformationQuery.LoanID) ON [Purchase Info per Note].LoanID = ExpenseLog.LoanID;

 

[arnelgp]

not tested yet, but you can try

SELECT [Purchase Info per Note].LoanID, [Purchase Info per Note].PurchasePrice, Sum(ExpenseLog.NetAmount) AS SumOfNetAmount, NoteInformationQuery.Term, NoteInformationQuery.LoanID, ExpenseLog.LoanID
FROM ([Purchase Info per Note] LEFT JOIN NoteInformationQuery ON [Purchase Info per Note].LoanID = NoteInformationQuery.LoanID) LEFT JOIN ExpenseLog ON [Purchase Info per Note].LoanID = ExpenseLog.LoanID
GROUP BY [Purchase Info per Note].LoanID, [Purchase Info per Note].PurchasePrice, NoteInformationQuery.Term, NoteInformationQuery.LoanID, ExpenseLog.LoanID;

 

[cathie]

That worked beautifully! Thanks so much for the assist! It is indeed greatly appreciated!

 

[arnelgp]

you're welcome maam.