bookkeeper1142
New member
- Local time
- Today, 02:06
- Joined
- Nov 16, 2011
- Messages
- 5
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.
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.