Mixing INNER and OUTER joins : "JOIN expression not supported" (AC2007)

[AOB]

Hi guys,

I'm trying to run a query across multiple tables, from a 'main' table of logged entries. In some cases, there will always be a corresponding value in the linked table (by design), so I use INNER JOINS. For others, there may not be a corresponding value in the linked table, but I still want to return the record (with a null in that field), so I use LEFT JOINS.

But when I try to build the query in the Query Builder, I get the following error :

JOIN expression not supported

Can anybody help me to put this SQL together?

Here's the command so far :

[FONT=Century Gothic][SIZE=1]SELECT tblLoggedEntries.EntryID, tblLoggedEntries.TransID, (tblUsers.FirstName & " " & tblUsers.LastName) AS LoggedBy, tblLoggedEntries.EntryTime, tblLoggedEntries.ValueDate, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblEntities.Entity, tblCurrencies.CurrencyCode, tblLoggedEntries.Amount, tblFXRates.FXRate, tblFMRRates.FMRRate, tblClassifications.ClassType, tblTransactions.TransType, tblOwners.Owner, (Amount/FXRate) AS AmountUSD [/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]INTO [tblTemp] [/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]FROM (((((((((tblLoggedEntries[/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]INNER JOIN tblAccounts ON tblLoggedEntries.AccountID = tblAccounts.AccountID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblCurrencies ON tblAccounts.CurrencyID = tblCurrencies.CurrencyID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblClassifications ON tblLoggedEntries. ClassTypeID = tblClassifications. ClassTypeID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblTransactions ON tblLoggedEntries.TransTypeID = tblTransactions.TransTypeID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblUsers ON tblLoggedEntries.UserID = tblUsers.NBKID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblBanks ON tblAccounts.BankID = tblBanks.BankID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblEntities ON tblAccounts.EntityID = tblEntities.EntityID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblOwners ON tblLoggedEntries.OwnerID = tblOwners.OwnerID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblFXRates ON (tblLoggedEntries.ValueDate = tblFXRates.RateDate) AND (tblCurrencies.CurrencyID = tblFXRates.CurrencyID))[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblFMRRates ON (tblLoggedEntries.ValueDate = tblFMRRates.RateDate) AND (tblCurrencies.CurrencyID = tblFMRRates.CurrencyID)[/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]WHERE (tblLoggedEntries.ValueDate Between #05/01/2014# And #05/31/2014#)[/SIZE][/FONT]

(I suspect it's because of the last two LEFT JOIN's which are based on two criteria; the 'left-side' in each case referencing different tables? I could understand that being an issue but then the question is, how do I query this if I can't join in this manner?...)

 

[AOB]

This seems to work...

I wrote a separate query to retrieve the available rates for each logged entry :

[FONT=Century Gothic][SIZE=1][COLOR=black][B]qryFXRatesPerEntry[/B][/COLOR][/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]SELECT tblLoggedEntries.EntryID, tblFXRates.FXRate[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]FROM (tblAccounts [/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblLoggedEntries ON tblAccounts.AccountID = tblLoggedEntries.AccountID) [/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblFXRates ON (tblAccounts.CurrencyID = tblFXRates.CurrencyID) AND (tblLoggedEntries.ValueDate = tblFXRates.RateDate)[/SIZE][/FONT]

Then used a LEFT JOIN on the query :

[FONT=Century Gothic][SIZE=1]SELECT tblLoggedEntries.EntryID, tblLoggedEntries.TransID, (tblUsers.FirstName & " " & tblUsers.LastName) AS LoggedBy, tblLoggedEntries.EntryTime, tblLoggedEntries.ValueDate, tblAccounts.AccountNumber, tblAccounts.AccountName, tblBanks.BankName, tblEntities.Entity, tblCurrencies.CurrencyCode, tblLoggedEntries.Amount, [COLOR=red]qryFXRatesPerEntry.FXRate[/COLOR], tblClassifications.ClassType, tblTransactions.TransType, tblOwners.Owner, (Amount/FXRate) AS AmountUSD [/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]INTO [tblTempReview] [/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]FROM ((((((((tblLoggedEntries[/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]INNER JOIN tblAccounts ON tblLoggedEntries.AccountID = tblAccounts.AccountID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblCurrencies ON tblAccounts.CurrencyID = tblCurrencies.CurrencyID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblClassifications ON tblLoggedEntries.ClassTypeID = tblClassifications.ClassTypeID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblTransactions ON tblLoggedEntries.TransTypeID = tblTransactions.TransTypeID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]INNER JOIN tblUsers ON tblLoggedEntries.UserID = tblUsers.NBKID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblBanks ON tblAccounts.BankID = tblBanks.BankID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblEntities ON tblAccounts.EntityID = tblEntities.EntityID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1]LEFT JOIN tblOwners ON tblLoggedEntries.OwnerID = tblOwners.OwnerID)[/SIZE][/FONT]
[FONT=Century Gothic][SIZE=1][COLOR=red]LEFT JOIN qryFXRatesPerEntry ON tblLoggedEntries.EntryID = qryFXRatesPerEntry.EntryID[/COLOR][/SIZE][/FONT]
 
[FONT=Century Gothic][SIZE=1]WHERE (tblLoggedEntries.ValueDate Between #05/01/2014# And #05/31/2014#)[/SIZE][/FONT]