Show all rows for one side of a many-to-many

[Sean]

Hello,

I have a many-to-many relationship like:

tblStores:
storeid
storename

tblProducts:
productid
productname

tblStock:
storeid
productid
quantity


I can create a subform showing for tblStock in the Stores form.
However, it will only show products for which an entry exists in
tblStock for the store. I want to show a list of all products whether
there is an entry in tblStock for that store-product or not.

Is there a simple way to do this?

Thanks,

Sean

 

[Allen Browne]

Use an outer join.

Create a query that uses both tblProducts and tblStock.
Double-click the line joining the 2 tables in the upper pane of query
design.
Access pops up a dialog with 3 options.
Choose the one that says:
All recorsd from tblProducts, and any matches from tblStock.

More info in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Sean]

Thanks for the reply but that's not exactly what I'm after.

Just an outer join doesn't capture product-site combinations for which
there is no entry in Stocks.

I figured it out for anyone wanting to do something similar.

You need to use a subselect -- a cross join of Products and Stores --
and left join it to Stocks

So:

SELECT *
FROM (SELECT * FROM tblProducts, tblStores) a LEFT JOIN tblStocks ON
tblStores.storeid = tblStocks.storeid

Sean