relationships and data management (new user)

[dhersch]

I am creating a database to manage promotions. I have all my basic information (administrative names, numbers, etc.) and now I am trying to figure out how to create a system where I can have multiple "products" within a record. Sometimes a promotion will have two products with a certain quantity per product and sometime sometimes there could be 20 products with a different quantity per product. I need to store this information and cannot think of a way unless I create a table with "product 1", "Quantity for Product 1", "product 2", "quantity for product 2",. Although I could do this, when I move to a form for easy data entry I don't want to view 30 empty spaces when I might only need to use a few. I also will use some of these products more than once, however the quantities could be different every time! I am having some trouble with the logic. Can someone outline what a table could look like? Thanks!

 

[mdmilner]

Your guess is correct, relationships are how to handle this.
What you need are two 'related' tables.

The first table will hold the promotion name as an indexed field (no duplicates). Table number two will hold a field also containing this value but it is indexed allowing duplicates. Additional fields in the second table will hold information related to your products.

On the relationships window, you will need to add both tables, then click on the first (promotion field name) and hold then drag it over to the second table. This will create a relationship. I'd go ahead and select 'Enforce Referential Integrity' AND both of the Cascade options.

The latter three selections will disallow putting records into the second table that do not have corresponding records in the first.

Mike