MS Access – Auto-Fill ID Field Based on Another Field

ms access

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?

Best Answer

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.

Related Solutions

Ms-access – Create new table values based on partial column value – access

Since your starting position is fixed (after the 2nd _), you can use an Instr() function to find the position of the 3rd _, and just take the characters between.

Instr() returns the postion of the first instance of a character in a string. If the character is not found, it'll return 0. Note, Access considers the first character position to be 1.

Give this a try:

SELECT Mid(txn_id, 12, InStr(12, txn_id, '_')-12) 
FROM MyTable`

Just for reference: https://support.office.com/en-us/article/InStr-Function-85d3392c-3b1c-4232-bb18-77cd0cb8a55b

Edit:

This is pretty gross, but if the 2nd _ position was also variable, this should work:

SELECT Mid(txn_id
           , Instr(Instr(1, txn_id, '_')+1, txn_id, '_')+1
           , Instr(
                   Instr(
                         Instr(1, txn_id, '_')+1
                         , txn_id
                         , '_')+1
                   , txn_id
                   , '_')-1 -  Instr(Instr(1, txn_id, '_')+1, txn_id, '_')
            )
FROM MyTable

MS Access SQL – Fill Percentage of Total Field

You need UPDATE, not INSERT:

UPDATE MyTable
SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;

or:

UPDATE 
    MyTable AS my,
    (SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / total.fSum ;

As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) probably not even allowed in Access.

The above statements however do not work in version 2013. I think they worked in old (1997) version but some change in the Jet engine (3.5? 4.0?) made the queries throw error instead.

Using the DSUM() function instead, to do the summation, works:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;

Best Answer

Related Solutions

Ms-access – Create new table values based on partial column value – access

MS Access SQL – Fill Percentage of Total Field

Related Question