2

I have an MS Access inventory database where I imported some data about our food purchases from Excel. In Excel, the Vendors were simply listed in a "Vendor Name" column, which looks like this:

Vendor name
------
Bearded Lady
Blue Heron Bakery
Calliope Farm
Cash and Carry
...

In my Access database, I have a table called "Vendors" that looks like this:

ID  Vendor Name
1   Bearded Lady
2   Blue Heron Bakery
3   Calliope Farm
4   Cash and Carry

The "Food purchases" table in Access looks like this:

ID  Item    Vendor ID   Vendor  Orderer ID  Orderer # of units purchased    Purchase unit   Price   Price per unit
2   Agave       Earthly Gourmet     bulk (wet)  5   lbs $25.00  $5.00
3   Allspice        OGC     bulk (spices)       3       
4   Anise seed      OGC     bulk (spices)       3   

Right now the "Vendor ID" field is blank, and I want to use the values in the "Vendor name" field (Earthly Gourmet, OGC, etc) to automatically fill in the correct values, based on matching the name to the corresponding key in the "Vendors" table, instead of having to manually type in the correct ID number for each vendor ... How can I do this?

2

Assuming that you know how to edit and run a SQL query in Access, I think you need to execute a SQL Query similar to the following:

    UPDATE FoodPurchases
    INNER JOIN Vendors ON FoodPurchases.VendorName = Vendors.VendorName
    SET FoodPurchases.VendorId  = Vendors.VendorId

I have not verified the above, so let me know if it does not work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.