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.
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.