2 identical tables, updated Query (1 Viewer)

[hfsitumo2001]

Instead of typing in the new table one by one, how can we make updated query to update unit price & UOM of the table B to table A Unit price and UOM fields on the same Itemcode of the 2 tables. Thank you for any samples

 

[arnelgp]

you don't Duplicate info of another table, if there is a Common
field to Link them (in Query).

if you create the Query, it will save you space and the worries.

but if the update comes from External source, to Update TableA with update coming from TableB:

update tableA Inner Join tableB On tableA.ItemCode = tableB.ItemCode Set tableA.UOM=tableB.UOM, tableA.IUnitPrice=tableB.IUnitPrice;

 

[hfsitumo2001]

you don't Duplicate info of another table, if there is a Common
field to Link them (in Query).

if you create the Query, it will save you space and the worries.

but if the update comes from External source, to Update TableA with update coming from TableB:

update tableA Inner Join tableB On tableA.ItemCode = tableB.ItemCode Set tableA.UOM=tableB.UOM, tableA.IUnitPrice=tableB.IUnitPrice;

no duplicate Arnel, As I have ever raised my question, on how to make multi unit of measures in Access, it seems difficult so I decided to 2 databases, one in Case, and one in EACH Unit of measures. So instead of key in to the unit price of each, I just exported in excel, than I modified the unit price in each, by making formula in excel, than I copy paste into unit price of "Each database we can say". Then I import into database say the name of the table is TABLE B, so I want table unit price updated by unit price of table A for certain fields only. Sorry updated coming from TableA to Table B

 

[arnelgp]

on the query I showed you, just reverse the order of the Table names.
if you also want to Add (new item) while Updating the table use Right Join.
Always backup (for safety).

update tableB Right Join tableA On tableB.ItemCode = tableA.ItemCode Set tableB.Description = tableA.Description, tableB.UOM = tableA.UOM, tableB.IUnitPrice = tableA.IUnitPrice;

 

[hfsitumo2001]

on the query I showed you, just reverse the order of the Table names.
if you also want to Add (new item) while Updating the table use Right Join.
Always backup (for safety).

update tableB Right Join tableA On tableB.ItemCode = tableA.ItemCode Set tableB.Description = tableA.Description, tableB.UOM = tableA.UOM, tableB.IUnitPrice = tableA.IUnitPrice;

Thank you Arnel I will try it

 

[hfsitumo2001]

on the query I showed you, just reverse the order of the Table names.
if you also want to Add (new item) while Updating the table use Right Join.
Always backup (for safety).

update tableB Right Join tableA On tableB.ItemCode = tableA.ItemCode Set tableB.Description = tableA.Description, tableB.UOM = tableA.UOM, tableB.IUnitPrice = tableA.IUnitPrice;

Arnel, whem I tried in the example attached, there is an error message for one record. Did I miss something?

Thank You

 

[arnelgp]

i did not encounter any error.
check "40X48" itemcode of both tables.
one of them have space at the beginning.

 

[hfsitumo2001]

i did not encounter any error.
check "40X48" itemcode of both tables.
one of them have space at the beginning.

Ok Thank you Arnel, yes that is the clue, it works now.

 

[Pat Hartman]

Do NOT use separate tables. All items need to be in a single table. If the conversion is standard, you can select all the items with EACH as the UOM and insert them as CASE UOM with the proper multiplication. If case is always 10 units then the price would be Price * 10. However, you cannot use ItemCode as the primary key. You will need to use an autonumber. Then to prevent duplicates, you will need to add a unique index on the two fields - ItemCode and UOM. You have to do this using the indexes dialog since the table design only allows you to make single field indexes.

 

[hfsitumo2001]

Do NOT use separate tables. All items need to be in a single table. If the conversion is standard, you can select all the items with EACH as the UOM and insert them as CASE UOM with the proper multiplication. If case is always 10 units then the price would be Price * 10. However, you cannot use ItemCode as the primary key. You will need to use an autonumber. Then to prevent duplicates, you will need to add a unique index on the two fields - ItemCode and UOM. You have to do this using the indexes dialog since the table design only allows you to make single field indexes. View attachment 89585

Thank you Pat. I will learn it