Replace data in access table

Posted on 2014-08-15
Last Modified: 2014-08-17

I have an access database which has a table called Assen, in the table is a field called AsNummer (unique numbers) and Description (Text).  This table is then used as a lookup value in a number of other tables one of which is called Merkerlijst.

I have a copy of the database called old.mdb and a copy called new.mdb.  In the new.mdb I have had to loose all the data from Merkerlist and change the Asnummer values for different row data. (ie Asnummer 12  with Description "Track 1" is now Asnummer 5)

I have also added some extra fields to new.mdb

I now need to import the data from old.mdb's Merkerlijst table but in a way that the new data has the correct and updated Asnummer.

I think I can create a list of old asnummers vs new as I guess thats necessary but not sure where to go from here.

Question by:simonwait
    LVL 31

    Accepted Solution

    You will need to run an update query to replace the old IDs with the new IDs in the table, linked by the Description field.  My recent Access Archon article on Fixing Normalization Errors may be helpful here.  Here is the link for downloading the article and sample database:
    and here is a typical update query:
    update query
    LVL 1

    Author Comment

    Sorry I have got myself in a right muddle here with data.  Will sift through and then look at your suggestion cheers

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    737 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

    17 Experts available now in Live!

    Get 1:1 Help Now