Creating a new table based on information from other tables (1 Viewer)

I have the following tables & fields within the tables…
Burnings = BurningID(PrimaryID), Part No, Description, Cost
Raw Steel = RawSteelID(PrimaryID), Description, Cost
Labor = LaborID(PrimaryID), Description, Cost Per Minute

…Now I need to create additional parts for our inventory that have one or more Burnings from the "Burnings" Table, some Raw Steel from the "Raw Steel" Table, and Labor from the "Labor" Table all put together for form a new part on a new Table. Obviously I want to draw from my original tables so that I only have to update the price one place. I need my new (combination) parts to be on a table with their costs also so that I can use them for my inventory, etc.

However, when I set up a Relationship and create lookup fields between the above tables and my New Table (COMBINATIONS), I have to use my PrimaryID fields for the Lookup field since my “Cost” fields may not always be unique. My problem is – I need to calculate a new cost for my “Combination” part based on the cost of my original parts – by adding together the cost of the burning(s), raw steel, and labor minutes(s). When I create a calculated field based on my Lookup field – it multiples my Qty by the PrimaryID instead of by the cost. I think I am probably missing something – but I am not sure how to go about it.

I can come up with the new costs by creating Subforms on my Entry Form for the Combination table. But if I do it that way, I don’t know how to link my calculated field from my form back to a field on my Combinations table.

…Now I need to create additional parts for our inventory that have one or more Burnings from the "Burnings" Table, some Raw Steel from the "Raw Steel" Table, and Labor from the "Labor" Table all put together for form a new part on a new Table.

Click to expand...

If a burning is a part and the part you are making is a part they should all be in 1 table. You can have a field that distinguishes a burning part from a non-burning part.

I would almost be tempted to put the steel in as a part as well.

tblParts
-pkPartID primary key, autonumber
-txtPartNo
-txtPartName
-fkPartTypeID foreign key to tblPartTypes


tblPartTypes
-pkPartTypeID primary key, autonumber
-txtPartType

You might have 3 records in the above table: burning, production, raw material.

You would need a table to relate the part that is produced to the parts that are used to make it

tblManufacturedParts
-pkManfPartID primary key, autonumber
-fkPPartID foreign key to tblParts (refers to the part being made)
-fkSPartID foreign key to tblParts (refers to the part used to make the above part)
-QTY
-fkUOMID foreign key to tblUnitsOfMeasure

If a fkPPartID takes several sub parts to make it, you would have multiple records in the is table with the same fkPPartID value but different fkSPartID values.


tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUnitOfMeasure

I would guess that if there is only one labor would be tied to the part in tblParts, but that really depends on how you handle labor in your process.

Thank you - I spent some time making and playing around with the database that you suggested. It definitely makes sense to me to have all of my parts on one table but there are a couple of things I can't figure out how to do...
1) How can I incorporate my pricing so that I can see the total price for my manufactured parts - the price of all the different components added together...?
2) The whole reason I am setting up this database is to make it very simple for other users to easily -check prices, update prices, and update some of the data in the manufactured parts.
3) Do you have some ideas how I can set up a user friendly form(?) that will be used to view and enter all the subparts of a Manufactured Part & can easily be accessed to change/update parts and/or labor?

1) How can I incorporate my pricing so that I can see the total price for my manufactured parts - the price of all the different components added together...?

Click to expand...

You would use a query to calculate the price of a manufactured part.

Do you have some ideas how I can set up a user friendly form(?) that will be used to view and enter all the subparts of a Manufactured Part & can easily be accessed to change/update parts and/or labor?

Click to expand...

Now that the table structure is setup, it is time to get your forms setup. You can then enter some test data in order to create the query mentioned above.

In terms of forms, you would initially base a main form on tblParts with a subform based on tblManufacturedParts. That subform would have to have a combo box tied to fkSPartID. The combo box's row source would be based on tblParts

Now in terms of the price, you have to put that field somewhere in the table structure. The question of where depends on how you want to handle prices. If your intent is to only have the current price available and that is all you will ever care about, then it would go in tblParts. However, if you want to keep a history of price changes, that describes a one-to-many relationship (one part has many prices over time). So how do you want to handle prices?

I would love to keep a history of the price changes if it would not take me hours and hours to figure it all out. I've breifly read how to do one to many relationships but I don't really have a handle on it.
I think I need to learn more about queries regarding the pricing - That is possibly the link I am missing for getting this all put together. All I could think of and know how to do is to create subforms that I would use on a form to be able to see the details and pricing of the record that I chose in my Combo Box. Then add all of the pricing of my subforms together to get the total price of a manufactured item. That is fine for the first level of Manufactured parts. However, those manufactured parts make more manufactured parts and those make even more. Does that make sense...?

Actually, the table structure to handle the price history is relatively simple

tblPartPrices
-pkPartPriceID primary key, autonumber
-fkPartID foreign key to tblParts
-dteEffective effective date of the price
-currPrice

I've breifly read how to do one to many relationships but I don't really have a handle on it

Click to expand...

Understanding relationships is key to any relational database. You will need to understand them in order to design effective forms, queries and reports.

That is fine for the first level of Manufactured parts. However, those manufactured parts make more manufactured parts and those make even more. Does that make sense...?

Click to expand...

I understand the level issue when it comes to manufactured parts and I have helped others on forums such as this design such systems, but that is as far as I typically go since I personally do not have a need to design my own system.

Do you know what the maximum number of levels you have for your manufacutured parts?

You might do a search on bill of material type applications since that is what you are basically designing. I have done some research on my own and a bill of material system is quite a challenge if doing it in Access.

I will check into some pricing on the Bill of Materials. I've researched some stuff but I never knew the correct term "Bill of Materials". Thank you very much! I'll let you know if I decide to research Access anymore and have anymore questions!

You're welcome; post back if you decide to move forward. I'll help as much as I am able.