Adding values with an update query

[dimbodoyle]

Hi folks
I have what I reckon is a complex problem that I need to solve for my warehouse.

We hold replacement parts for the machines we sell. Some parts can be subsititued for others, so where we possible we use these parts to simplify our warehousing. Every day we get a file with demand for each part in and we look to see where we could move this demand to for those parts with a common alternative. When we find one we remove the demand from that part in the database and add it to the demand for the common part.

In operational terms we upload the demand file (.xls) to a table of the following structure
Date_added - Date
Part_number - Text
QTY - number

Thus the table holds daily demand by part. What I would like to do is use a update query to:
a) Identify the parts that have common parts to which they can be moved.
b) Add that demand to that of the common part today.
c) remove it from the original part (set to zero).

Idenitfying the parts and restricting to todays date is not a problem, however the additions and deletions are.

Any suggestions?

Thanks in advance!!

 

[neileg]

I would create two new records for the adjustments. One with a negative value to reduce it from the original part and one with a positive value to add it to the alternate part. Create a totals query on the table to give you the net adjusted totals part by part.