Updating table

[Cowley]

Hi,



I have a database with all the COGS(Cost of goods sold) for our ITEMS, a few times each year we do a update based on purchase prices. (not always all)

So in Table A we have the total list of items with cogs, and in table B we have the list of items that has had it's price increased or decreased.

Common key in the tables are the item number.

I then thought I could do an update query that would go something like this.(not trying to write the actual code, just how I think)

Update tableA.COGS
With tableB.COGS
Where tableA.ITEMNUMBER = tableB.ITEMNUMBER

Any help would be appreciated.

//Cow

[Bob Fitz]

Perhaps you could use an "Update" query

[orange]

Richard Rost has just put out a youtube video that shows similar situation. I suggest you watch the video for related info and approach.

SQL reference with examples of UPDATE
Good luck.

[Bob Fitz]

Cowley

Using your table and field names, perhaps something like:

UPDATE tableA INNER JOIN tableB ON tableA.ITEMNUMBER = tableB.ITEMNUMBER SET tableA.COGS = [COGS];