Outer Join Query issues

[John T Ingato]

OK. I need to be more specific. I alway try to be short and sweet so as not
to bore anyone. The last replier I am sure gave great info, but it was hard
to follow along since I was unspecific.

I am a manufacturer of 11 products sold to a large chain store.

I have a table of 1800 stores; "tblAllStores". This list gets update every
quarter and contains all stores in the chain, including stores that have
closed. I run a query on that table creating a recordset of open stores
only; "qryOpenStores"; with fields like "StoreNumber", "Address", etc

I also have a table with each of our products;"tblProducts" with fields like
"ItemNumber", "Description","Cost", "SortOrder" (for display purposes)

I also receive an excel file from this chain that contains inventory levels
for every store and for every item we have in those stores. This excel sheet
and every other weeks sheet have been imported into a table called
"tbl2006SalesData". Unfortunately, the excel sheets are not 100% complete.
Some sheets are missing stores completely, some sheets are just missing
items in many stores, most sheet are missing a combination of both. I just
accept it as unreported information.

The problem I would like to correct is this: I would like to run a query
that would assure every open store was in the dataset along with assuring
that every store had 11 products, regardless of whether or not it was ever
reported.So I would like to see this:
Store 1
item 1
...
...
item 11
....
....

Store 1800
item 1
...
...
item 11

I think you get it, huh. Now I am new to Access (having been programming
Excel for a while) and cannot grasp my hand around it. Access is much more
intense.

Can someone tell me how to set up this query... or series of queries?

 

[Smartin]

OK. I need to be more specific. I alway try to be short and sweet so as not
to bore anyone. The last replier I am sure gave great info, but it was hard
to follow along since I was unspecific.

I am a manufacturer of 11 products sold to a large chain store.

I have a table of 1800 stores; "tblAllStores". This list gets update every
quarter and contains all stores in the chain, including stores that have
closed. I run a query on that table creating a recordset of open stores
only; "qryOpenStores"; with fields like "StoreNumber", "Address", etc

I also have a table with each of our products;"tblProducts" with fields like
"ItemNumber", "Description","Cost", "SortOrder" (for display purposes)

I also receive an excel file from this chain that contains inventory levels
for every store and for every item we have in those stores. This excel sheet
and every other weeks sheet have been imported into a table called
"tbl2006SalesData". Unfortunately, the excel sheets are not 100% complete.
Some sheets are missing stores completely, some sheets are just missing
items in many stores, most sheet are missing a combination of both. I just
accept it as unreported information.

The problem I would like to correct is this: I would like to run a query
that would assure every open store was in the dataset along with assuring
that every store had 11 products, regardless of whether or not it was ever
reported.So I would like to see this:
Store 1
item 1
...
...
item 11
...
...

Store 1800
item 1
...
...
item 11

I think you get it, huh. Now I am new to Access (having been programming
Excel for a while) and cannot grasp my hand around it. Access is much more
intense.

Can someone tell me how to set up this query... or series of queries?

What you want (I think) is not an OUTER JOIN but a LEFT (or RIGHT) join.
This will show you all stores, and any matching records in
sales/products. Where there is no match the sales information will be null.

In your query design, join stores and sales on the appropriate
identifier. Right click the join line and pick "Include ALL records from
'Stores' and only those records from 'Sales' where the joined fields are
equal."

 

[Jamie Collins]

What you want (I think) is not an OUTER JOIN but a LEFT (or RIGHT) join.

LEFT JOIN is a contraction of LEFT OUTER JOIN. Perhaps the OP wants a
FULL OUTER JOIN, which is not supported by Jet, the workaround being a
UNION of the LEFT OUTER JOIN and the RIGHT OUTER JOIN.

Jamie.

--