Multiple Tables Question

[stripedfrog via AccessMonster.com]

I want to make sure I understand how to handle multiple tables. I have a
table created for dealers that sell "product A". There are also two other
products that are sold but not every dealer carries all three products.

I understand I need two other tables for "products B and C" but am a bit
confused. What fields need to be in these two tables? The primary key will
be the dealer's account number. Then what do I put?

Thanks.

 

[Duane Hookom]

You should have tables of dealers, products, and dealer/products.
Minimally, I would create:

tblDealers
=============
dlrDlrID autonumber primary key
dlrName dealer name

tblProducts
===========
prdPrdID autonumber primary key
prdName product name

tblDealerProducts
=============
dlpDlPID autonumber primary key
dlpDlrID relates to tblDealers.dlrDlrID
dlpPrdID relates to tblProducts.prdPrdID

The tblDealerProducts table is referred to as a Junction table.

 

[John Vinson]

I want to make sure I understand how to handle multiple tables. I have a
table created for dealers that sell "product A". There are also two other
products that are sold but not every dealer carries all three products.

I understand I need two other tables for "products B and C" but am a bit
confused. What fields need to be in these two tables? The primary key will
be the dealer's account number. Then what do I put?

Thanks.

See my reply in your other thread.

John W. Vinson[MVP]

 

[stripedfrog via AccessMonster.com]

Thanks all!!!

 

[guru_ross]

I don't know that your premise is correct. Are the three products s
different that they have separate column requirements? In a standar
situation, you should have three tables:
- Dealers
- DealerProducts
- Products

You would have a primary key in Dealers (e.g. DealerGUID), a primar
key in Products (e.g. ProductGUID) and a two-column primary key i
DealerProdcuts (LinkDealerGUID, LinkProductGUID). Last you creat
foreign keys between DealerGUID and LinkDealerGUID, and betwee
ProductGUID and LinkProductGUID.

Now you have three tables, one a list of dealers, one a list o
products, and one linking each dealer to each of the available product
for that dealer.

Hope that's what you're looking for (and wasn't too much)