Updating multiply rows in a table

[Guest]

I have a Inventory table that has many fields but for simplicity sake I am
only interested in two fields. The first is the PART NUMBER and the second is
END BALANCE both are numbers. I have a linked spreadsheet that gives me the
PART NUMBER and QUANITY used each month for all of the parts used in
production.

How can I UPDATE(we have multiple parts used so I do not want to do this
one part at a time) the INVENTORY TABLE automatically to place the difference
of what we started with and what we ended with in END BALANCE field.

I can write a query that performs the calculation of the between the number
of parts used and the number left in inventory, but how do you get it to
write that number to the END BALANCE in my INVENTORY TABLE.

Just so you know I wrote some code behind a button that using (rs![Part
Number] = Forms![Part Number], rs![beg balance] = forms![beg balance])
RECORDSET and it works , but it adds another line to the INVENTORY TABLE and
I want to just update the entry that already exists in the INVENTORY TABLE.

Whew - that was long winded

Thanks in advance
Curt

 

[Arvi Laanemets]

Hi

To add new records, create a query, which collects all records from input
table, which don't exist in Inventory table (must be same structure, but you
can leave out fields which remain empty, and autonumeric fields). Change
this query to an Append query.

To edit existing records, create a query, which returns all records you want
to change. The query contains only fields values in which you want to
change, and it must contain new values in all those fields. Change this
query to an Update query.

To delete records from table, create a query which returns any field, and
contains all records you want to delete. Change this query to a Delete
query.

When you runing Append/Update/Delete query, according operation is
performed.