Solved Updating pricing for a vendors table (1 Viewer)

[slharman1]

I have a table with parts and their costs along with other information in various fields. I got an updated price list from one of my vendors and I need to update all of the pricing from that particular vendor. I have an excel spreadsheet with the new pricing, it includes the model number for the parts as well. I can remove all of the fields in the vendor spreadsheet that don’t translate to my table and I am left with the model number and new price, so two fields. Is there a way to update my parts table using the data I’m the excel spreadsheet. That would save me hours of work having to update each price individually.

 

[arnelgp]

you create a linked table on the modified excel (make sure column name match the table fieldnames).
create an Update query:

update yourTable A Right Join yourLinkedExcel B On A.partNumber = B.PartNumber Set A.Model = B.Model, A.Price = B.Price;

(i assumed that each partNumber is different for each vendors).

EDIT: if there is New partNumber from vendor that needs to be added:

update yourTable A Right Join yourLinkedExcel B On A.partNumber = B.PartNumber Set A.partNumber = B.partNumber, A.Model = B.Model, A.Price = B.Price;

 

[slharman1]

you create a linked table on the modified excel (make sure column name match the table fieldnames).
create an Update query:

update yourTable A Right Join yourLinkedExcel B On A.partNumber = B.PartNumber Set A.Model = B.Model, A.Price = B.Price;

(i assumed that each partNumber is different for each vendors).

EDIT: if there is New partNumber from vendor that needs to be added:

update yourTable A Right Join yourLinkedExcel B On A.partNumber = B.PartNumber Set A.partNumber = B.partNumber, A.Model = B.Model, A.Price = B.Price;

Thanks but I have a question: my parts table has many parts from many vendors and I am only wanting to update the parts from one vendor. Will this still work?

 

[arnelgp]

you also have vendorcode/name on your table, add it to the Join:

Update yourTable A
    Right Join yourLinkedExcel B
        On A.partNumber = B.PartNumber And
        A.VendorCode = B.VendorCode
    Set A.VendorCode = B.VendorCode,
        A.partNumber = B.partNumber,
        A.Model = B.Model,
        A.Price = B.Price;

 

[slharman1]

Thanks Arnelpg, That's what I needed.