Update Query, I must be doing something wrong (1 Viewer)

[mcgilla]

A somewhat complicated question and problem that I’m sure I designed without knowing.

Before you ask, I cannot show any structure so I’ll try to be as descriptive as possible in a generic sense. it’s a design for work and I cannot divulge the information.

To give some perspective, I’ll describe that this system is for making paints. It tracks cost, raw material use, finished product shipments, etc.

So, my problem…

We make “Blue” paint using raw materials 1,2,3,4. We make a “Yellow” paint using raw materials 3,4,5,6. Both Blue and Yellow are finished products.

However, we make a “Green” paint using “Yellow” and “Blue”. So my finished product also have to be listed as Raw Materials with associated Costs. So, let’s say Not only do I have raw materials to make the yellow and blue, I have yellow and blue listed as raw materials in order to make green.

When I first enter a paint into the system, it will automatically add itself to the Raw Material with all needed data.The append query works great. However, it traps information for the day it was entered and only that day.

So I print a report today showing me the cost of each finished product.
1+2+3+4 = $10 (Cost for Blue)
3+4+5+6 = $23 (Cost for Yellow)
Blue + Yellow = $18 (Cost for Green)

Tomorrow, I get a price change for Raw material 1. It’s a manual entry and the updated report now shows this.
1+2+3+4 = $12 (Cost for Blue)
3+4+5+6 = $23 (Cost for Yellow)
Blue + Yellow = $18 (Cost for Green). ß-Here is my problem that I’m trying to automate because it didn’t change the raw material cost of Blue, only the finished cost.

My issue is that I’m having a problem updating the Raw Material table. My query extracts just the final cost for Blue, Yellow and Green and then tries to put it into the Raw Material Table. When I try to update the data in the table from a query, it deletes the cost completely from Blue, Yellow and Green. I’ve also tried to create an empty shadow table that the new cost can be appended, and then update the old data to the new. When I do this, nothing happens, the old data remains.

Am I using the update query in the wrong way? I would have thought that either method would work like a charm, but my mind is obviously easier than Access. As it stands today, what I’m looking for is to update 68 out of 200 records by doing this.

Office 2010, Windows 7 32bit, Multiuser Front End/Back End configuration, and because I’m not the one entering data, I don’t know when costs have changed. I’m just trying to supply people with live costs when they pull up the report and can make these updates trigger when a raw material cost is manually entered.

 

[jzwp22]

I personally would only update those ingredients that are true RAW materials. Those materials that are either intermediates or end products (or both like blue and yellow) would not be updated. You would calculate these on the fly when you need the cost. You will probably need a custom function to do this, I'm guessing.

 

[mcgilla]

I personally would only update those ingredients that are true RAW materials. Those materials that are either intermediates or end products (or both like blue and yellow) would not be updated. You would calculate these on the fly when you need the cost. You will probably need a custom function to do this, I'm guessing.

Raw material changes affect the cost of Intermediates and End Products. The report is for the sales people to know the cost of each product in order to be able to sell it at the proper markup. If Blue as an End Product changes, I need to put it into the Raw Material (As an intermediate) in order to capture the cost change in Green. I didn't give full examples because it also may be that I use blue paint, plus raw material 9 in order to get purple. The recipes aren't always the same method in order to get a different end product and we don't restrict what someone does so we're promoting creativity. So I had to come up with a consistent way to build the paints that allowed the most freedom.

on a separate issue, I was almost there with appending the shadow table. After adding data to the empty shadow table, I found (In Microsoft Help nonetheless) that if you do an update query with both tables and link on the unique "Raw Material" identifier, you can then make one table update to the data in the other for the rest of the fields that you desire to update. $/Unit from table 1, Updates to the exported $/Unit from table 2, etc.

 

[jzwp22]

Since both the intermediates and the end products are dependent upon the raw materials, only those materials should have the costs. You would not store the costs associated with end products and intermediates in the table, but rather calculate them on the fly (i.e. in a query, form or report) when needed. BTW do you need to keep track of raw material price changes i.e. keep a history? If so, that will require an additional table.

 

[mcgilla]

Since both the intermediates and the end products are dependent upon the raw materials, only those materials should have the costs. You would not store the costs associated with end products and intermediates in the table, but rather calculate them on the fly (i.e. in a query, form or report) when needed. BTW do you need to keep track of raw material price changes i.e. keep a history? If so, that will require an additional table.

A little late for my reply, thanks for the input.

I do have another table to track raw material price changes. It's automatically updated on the first of each month.

 

[jzwp22]

It's automatically updated on the first of each month.

Do you mean updated or are new price records appended ? If you need to track history, then you need new price records added with an effective date. You would then use the effective date to determine which price to use. For example if a customer buys a product at the end of the month but the invoice is not generated until the second of the next month, the invoice better reflect the price at the time of the order (previous month price) rather than the current price.