02-27-10, 13:22 #1Registered User
- Join Date
- Aug 2005
Unanswered: Link materials in a subform to products without duplicating data?
I have a sort of recipe/cost breakdown database that I am working on.
I have 4 tables right now. Materials, Products, Suppliers, and Contents.
The breakdown is as follows:
Materials (ID(PK), material, supplier_id, part_no, part_no_link)
Products (ID(PK), item_sku, item_name, batch_count, item_size, item_note, item_retail)
Suppliers (ID(PK), supplier, e-mail address, business phone, fax, address, etc)
Contents (ID(PK), item_sku, qty, material, supplier, stock_no, cost_freight, base_price)
The point of this database is to keep track of what it takes to make products, who supplies them, and where you can get the materials. I have a form to input Product information such as name, sku etc. Then a subform called Contents that I input what materials it takes to make said product. I already have most of the material information in the Material table and I am trying to find out how I can stop duplicating data.
How can I link the Materials and Products tables without duplicating the materials? A lot of products will use the same materials just different quantities. Basically I would like for the user to go to the subform select material from drop down and pull the supplier and part no automatically to the subform.
If anyone has any tips or solutions please point me in the right direction.
Thanks for any assistance,
02-28-10, 00:24 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
"How can I link the Materials and Products tables without duplicating the materials? A lot of products will use the same materials just different quantities. Basically I would like for the user to go to the subform select material from drop down and pull the supplier and part no automatically to the subform."
- You link the tables by creating an autonumber field ie. CustomerID (or primary key) in your main table and then an identically named ID integer type field in your relational table. Then join them in the relational diagram, making sure to click the join type and make it 1 to many (typically).
- To prevent duplicates in a table, you create a primary key on that/those fields (ie. highlight them and then click the primary key button.) You want to establish the primary key on field where you don't want the data duplicated (so for example, your table would have an autonumber type field and then also 1 and/or 2, 3.. fields are the primary key.
I personally prefer to link on the autonumber type field but it depends on the project.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)