Run a query to update only the current record of table 1 to another table

[ifclick]

Idea is to only update the new record called amount that reside in table 1 and update it to table 2.

I am trying to update amount of assets in a kit. So table named kit is the source and asset table is destination.

The problem I am having is query keeps updating every single record on kit table to asset table.

UPDATE Assets INNER JOIN Kits ON Assets.[Barcode ID] = Kits.[Barcode ID] SET Assets.[In Kit] = [Kits]![Amount]+[Assets]![In Kit];
 

[Stewart Ross]

There is no WHERE clause in your query that could limit the update you are performing. You mention something about a new record containing an amount, but there is no reference to one and only one record in your update query - the join clause joins all records in your Assets table to all corresponding records in your Kits table on the common barcode ID field.

As you have not mentioned how you are identifying your new record I cannot suggest a suitable WHERE clause for you.

I think you may need to consider whether your design is properly normalised and implemented, as there is in general no need to store calculated fields in a table.

-Stewart

[ifclick]

i forgot to post the WHERE clause

WHERE (((Kits.[Date of creation])=Date()));

i did some research it says you can add a datetime/timestamp to the table that would store the insert/updated time of the record, and query based on that.

i actually added a column that says the date() However, when you create multiple records with the same barcode ID in the same day the amount that i want to update will be doubled.

[Stewart Ross]

The date of creation is not unique and cannot be used to select just one record. Again, I think you need to look seriously at your design as it is almost certainly not in third normal form or higher from what you've told us so far.

-Stewart

[zmbd]

ifclick... what SR is referring to is discussed here:
Database Normalization and Table Structures

Normalization of the database is an essential first step and not always easily done.

I usually suggest using the autonumber or guid as the primary key. It's unrelated to the data and no matter how the data is... "labeled" now or in the future, that PK field stays unchanged and easily used.