How to make table Company/ProdOut/Products.

[petrovlis]

I have now a working report system that any client in
ProdOut(table ProdOutID(autonumber)-CustomerID-ProductID
gets an updated price-list.

Some clients have only for two/three products,others a few
more.
I have a price(product)list of 1500 items and like to
build a new table **PRODOUT** with above fields.
Example:
Client AAA-PRODOUT-table- oneCustomerID-many1500 ProductIDs

Client BBB - One CustomerID- 1500 ProductIDs

Client CCC - One Custmer ID- 1500 ProductIDs

I have made a MAKE-Table "PRODOUT" already for the
1500 products, but how do I insert my 2000 Customers in that
table.
Example: CustomerID 1 1- 1500 productID
CustomerID 2 1- 1500 productID
I know the total table should be like 2000(customers) multiplied by 1500, so 2000x1500 is a long list.
I limit the output by mailing by some country parameters.
Thanks for any ideas/help.
William Rippen

 

[KenReay]

Hi

First question why are you using a make table query?

You are duplicating the data in the table, and this will lead to problems of data integrity

Why not create a SELECT query to produce a recordset of the data you want

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

What you want is named a cartesian product:
SELECT CustomerID, ProductID
FROM tblCustomer, tblProduct
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244