outer join issues

[John T Ingato]

I hope I can explain this well enough. I am new to Access.
I have a tble of 1800 stores, a table of Inventory levels for each week, and
a table of 10 products.

***tblStores***
StoreNumber
address

***tblInventoryData***
StoreNumber
ItemNumber

***tblProducts***
ItemNumber

I would like to perform a query that will be sure to include every store in
the stores table AND every product in the products table. Unfortunately, the
sales reports are not always complete and certain items just do not show up.
Obviously this is inconsistant.

I tried using outer join from stores to sales data AND an outer join from
products to sales data but I get an error stating ambiguous outer joins.

I am confused. Can someone help?

 

[Jamie Collins]

I hope I can explain this well enough. I am new to Access.
I have a tble of 1800 stores, a table of Inventory levels for each week, and
a table of 10 products.

***tblStores***
StoreNumber
address

***tblInventoryData***
StoreNumber
ItemNumber

***tblProducts***
ItemNumber

I would like to perform a query that will be sure to include every store in
the stores table AND every product in the products table.

That would be something like:

SELECT S1.StoreNumber, P1.ItemNumber
FROM tblStores AS S1, tblProducts AS P1;

Unfortunately, the
sales reports are not always complete and certain items just do not show up.

Plugging in the above query, something like:

SELECT DT1.StoreNumber, DT1.ItemNumber,
IIF(D1.StoreNumber IS NULL, FALSE, TRUE) AS has_sales
FROM (
SELECT S1.StoreNumber, P1.ItemNumber
FROM tblStores AS S1, tblProducts AS P1
) AS DT1
LEFT JOIN
tblInventoryData AS D1
ON DT1.StoreNumber = D1.StoreNumber
AND DT1.ItemNumber = D1.ItemNumber;

Jamie.

--