Update Columns in MS Access Table

Posted on 2012-08-16
Last Modified: 2012-08-25
Hi All,

I have the following table in Access

sku  (text)
price (number)
quantity (number)
map  (Yes/No)
map-price (number

sku              price      quantity      map-price
A001      10                2            
A002      5                1            
A003      3                3            

I have a text file that has the SKU & the Map-Price

What steps would I need to take to update the existing table in MS Access with the Map-Price currently sitting in a text file.

Thanks in advance.
Question by:eeclint
    LVL 25

    Expert Comment

    1. import the file into a table within access ... call it "map_price_table" for example
    2. run the update:
    update InventoryTable
    Set [map-price] = (select [map-price] from map_price_table where map_price_table.SKU = InventoryTable.SKU)
    Where EXISTS (select 1 from map_price_table where map_price_table.SKU = InventoryTable.SKU)

    3. Delete the "map_price_table" table.
    LVL 61

    Accepted Solution

    For an Access database, you would import the text file into a table (called tblImport for the purpose of example), naming the columns appropriately.

    Your update query syntax in Access would be:

    UPDATE InventoryTable INNER JOIN tblImport ON InventoryTable.SKU = tblImport.SKU
    SET InventoryTable.[Map-Price] = tblImport.[Map-Price]

    Open in new window

    If your yes/no Map column is used to determine whether this update should take place, you could modify the query like this:

    UPDATE InventoryTable INNER JOIN tblImport ON InventoryTable.SKU = tblImport.SKU
    SET InventoryTable.[Map-Price] = tblImport.[Map-Price] 
    WHERE Map = TRUE

    Open in new window


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now