Left join with multiple criteria

[jmac123]

I am new to some of the SQL syntax required by Access

I am having trouble joining a table on two field criterias eg:

Select * 
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID AND B.ProductsID = A.ProductsID)

Access will not allow this syntax and I can only think to put the 2nd criteria in the WHERE clause like so:

Select * 
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID)
WHERE B.ProductsID = A.ProductsID OR B.ProductsID IS NULL

This method appears to be removing some of the original lines from table A based on the B.ProductsID = A.ProductsID.
Surely this defeats the purpose of a left join??

Any help would be most appreciated

 

[jdraw]

Can you tell us in English the logic of the Query?
Access does have an unmatched query wizard.

 

[plog]

Try this:

SELECT *
FROM A LEFT JOIN B ON (A.ProductsID = B.ProductsID) AND (A.CustomersID = B.CustomersID);

 

[jmac123]

No probs. Here is an example.

TableA has forecast sales Data with following fields:
TableA.CustomersID
TableA.ProductsID
TableA.Date
TableA.ForecastUnits

TableB contains contract selling prices for each Customer with fields
TableB.CustomersID
TableB.ProductsID
TableB.UnitPrice

In order to calculate a value for each line in TableA, it must be joined to TableB on two fields like:

SELECT 
A.CustomersID,
A.ProductsID,
A.Date,
A.ForecastUnits,
A.ForecastUnits * B.UnitPrice AS ForecastValue
FROM TableA A
LEFT JOIN TableB B ON (B.CustomersID = A.CustomersID AND B.ProductsID = A.ProductsID)

If there is no Contract selling price found for a specific Customer & Product in TableA then I still want the record to be present in the query but with a NULL ForecastValue.

Hope it is clear what I am trying to achive

 

[jmac123]

Thanks

Although that method wasn't the answer, it did point me in the right direction.
Yesterday I didnt have access to the full DB from home so to test the suggested syntax form plog I made a couple of test tables. I found that Access DOES accept the following syntax:

Select * 
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID AND B.ProductsID = A.ProductsID)

It also accepts this (as suggested by plog)

Select * 
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID) AND (B.ProductsID = A.ProductsID)

It appears that access requires you to join to the base table when possible. My rejected SQL was:

Select * 
From (((TableA A
LEFT JOIN Products P ON (P.ID = A.ProductsID))
LEFT JOIN Customers C ON (C.ID = A.CustomersID))
LEFT JOIN TableB B ON (B.CustomersID = C.ID AND B.ProductsID = P.ID))

The SQL that solved my issue was to join to base table (TableA) like so:

LEFT JOIN TableB B ON (B.CustomersID = TableA.CustomersID AND B.ProductsID = TableA.ProductsID)

Thanks for the helpful pointers

 

[Niloct]

Thanks

Although that method wasn't the answer, it did point me in the right direction.
Yesterday I didnt have access to the full DB from home so to test the suggested syntax form plog I made a couple of test tables. I found that Access DOES accept the following syntax:

Select *
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID AND B.ProductsID = A.ProductsID)

It also accepts this (as suggested by plog)

Select *
From TableA A
Left Join TableB B ON (B.CustomersID = A.CustomersID) AND (B.ProductsID = A.ProductsID)

It appears that access requires you to join to the base table when possible. My rejected SQL was:

Select *
From (((TableA A
LEFT JOIN Products P ON (P.ID = A.ProductsID))
LEFT JOIN Customers C ON (C.ID = A.CustomersID))
LEFT JOIN TableB B ON (B.CustomersID = C.ID AND B.ProductsID = P.ID))

The SQL that solved my issue was to join to base table (TableA) like so:

LEFT JOIN TableB B ON (B.CustomersID = TableA.CustomersID AND B.ProductsID = TableA.ProductsID)

Thanks for the helpful pointers

That works even with Access 2007 =) Thanks for your insight.