combining two unrelated tables?

[newmusicmark]

Hi,
I have one table of stores. Store # is the primary key.

I have another table of product with a unique sku # for each product.

I need to make a new table that lists every product available for each store.

Example.
Store sku
1 1
1 2
1 3
2 1
2 2
2 3

Neither table has any common fields but the resultant table will.

Thanks,
Mark

 

[Norie]

Neither table has any common fields but the resultant table will

Why?

 

[bat17]

Will every product be available in every store? to get this just stick both tables in a query with out joining them.

HTh

Peter

 

[Andrew Fergus]

If you are trying to set up a many-to-many relationship (i.e. each store sells multiple products and each product can be sold by multiple stores) then you are going the right way about it.

The previous answer about setting up a query will show you the combinations based on the data entered into the two tables to date. If you are encountering "too complex" problems with subsequent queries or if you wish to set up all possible combinations (rather than rely on data entered to date) then you may consider storing the combinations in a new table with just the fields "store id" and "product id", setting up both fields as a joint primary key and then adding the table to your relationships.

If you link the stores table to the new table via "store id" as a one-to-many relationship and the products table to the new table via "product id", also as a one-to-many relationship, then you have got yourself a many-to-many relationship between the stores and products.

To populate this new table with all possible combinations, you will need a script or a macro with an append query to add every product for every store (you may be able to work that out yourself, if not let us know). You might also consider using the same sort of script / macro / append query every time you add either a new store or a new product. If you don't do this then this new table will be stuck in a time warp with your original data. As the number of stores and products increase you are going to want this task automated.

Hope this helps, Andrew