|  | Show all rows for one side of a many-to-many | | |
| | | Sean |  |
|  Posted: Wed Sep 10, 2008 12:52 pm Post subject: Show all rows for one side of a many-to-many |  |
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 | |
| | Posted: Wed Sep 10, 2008 12:52 pm Post subject: Re: Show all rows for one side of a many-to-many | |
| |  | |
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:
LINK
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"Sean" <seanasy@gmail.com> wrote in message
news:dd653fdf-84ba-420a-a1ad-83617591ae9d@a1g2000hsb.googlegroups.com...
| Quote: | 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 |
|
| |
| | | Sean | |
| | Posted: Wed Sep 10, 2008 2:51 pm Post subject: Re: Show all rows for one side of a many-to-many | |
| | | |
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
On Sep 10, 9:52 am, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
| Quote: | 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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Sean" <sean...@gmail.com> wrote in message
news:dd653fdf-84ba-420a-a1ad-83617591ae9d@a1g2000hsb.googlegroups.com...
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 |
|
| |
|
|
