J
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?
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?