Update Value in Table based on to that of Another Field

[LaDiDa]

Beginning user & appreciate any help.
I have a table (Line Item) which has imported data for "item" and "units".
I need to update another field in the table "revised units" to have the same
value at the "units" field. The users will change the value in the "revised
units" field at some time in the future if needed, but the initial value
should match in both the "units" and "revised units" fields.

I have tried writing an update query that updates the value of "revised
units" to match that of "units". I got it to work one time, but now I get
nothing - no value is returned.

I'm using
Field: revised units
Table: Line Item
Update To: [Line Item]![units]

Any suggestions?
Thanks,
B

 

[vanderghast]

Untested, and not sure it suits your needs i the first place, so, start by
making a backup. In SQL view of a new query

UPDATE LineItems RIGHT JOIN RevisedUnits
ON LineItems.item = RevisedUnits.item
AND LineItems.units = RevisedUnits.units
SET LineItems.item=RevisedUnits.items,
LineItems.units = RevisedUnits.items,
LineItems.RevisedUnits = RevisedUnits.RevisedUnits



I assumed:
- Your fields name have no space in them, if they have, use [field name]
instead of fieldname as I did;
- Your tables name have no space in them, if they have, use [table name]
rather than tableName as I did;
- RevisedUnits hold the new information, LineItems hold the old
information;
- you meant "both ITEM and units" must match, rather than "both units
and revised units must match"

Note: - if there is NO match for both fields, a new record will be appended
to the old table. (assuming it is a new (item, units) pair to store in the
table).
- you can edit the query in the design view, once you entered the
statement in SQL view, if you want to customize the query further on.




Vanderghast, Access MVP