Auto-filling from another table... is this possible and how?

[nminaker]

I am in the middle of creating a database of products which go in different "kits." The kits are seperated by their own numbers and many of the kits contain the same products. All the products have a specific product number and product description.

What I would like to do is have one table with all the products that will possibly go in the kits(two fields, ProductID and Description) and another table with all the products in all the kits. (ProductID, Description, Kit#, ExpDate... etc). For the second table I would like the Description to autofill or fill somehow with the description in the first table. Is this possible at all?

Thanks!

Nick

 

[PHV]

Will this field be editable ?
If not, simply remove it from the kit table and make the ProductID a ForeignKey to the products table. You'll retrieve the description by joining the 2 tables.

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

 

[nminaker]

Thanks for the quick reply.

I'm not quite sure that I understand completely what you're saying. To make it simpler for me, I have two tables: "Products" and "Product Master." "Product Master" has all the Product ID's and Descriptions. "Products" is the table which contains ProID, Desc, Kit#, Exp Date, etc.

You're saying to remove the Desc field from the "Products" table and then make ProdID in the same table a ForeignKey?

Also, I apologize, but I don't know how to make a Foreign key either. Obviously I'm just a beginner.

Thanks again for all the help.

Nick

 

[lespaul]

First off, check out 'The Fundamentals of Relational Database Design'

Secondly, by adding the ProductID from ProductMaster to Products, you have created a Foreign Key! You don't want any fields that are in ProductMaster duplicated in Products EXCEPT the key field.

So you have:

tblProductMaster
ProductID
ProductDesc

tblProducts
ProductID
Kit#
ExpDate
etc

to get the description of the product you would then join the tables together in your query:

SELECT tblProducts.ProductID, tblProducts.Kit#, tblProductMaster.ProductDesc
FROM tblProducts
INNER JOIN tblProductMaster on tblProductMaster.ProductID = tblProducts.ProductID

Check out the link, it will help alot in understanding relational databases and how to design the most efficient database.

Leslie

 

[nminaker]

Thanks for all your help!

I believe (after reading the document you advised me to read) that you're basically saying that I should design my database so that it is at least 1NF.

I believe that I have completed this and everything seems to be working the way I want.

Thanks once again for the help.

Nick

 

[lespaul]

No, you should design your database to the THIRD normal form!

Leslie