Results 1 to 3 of 3
  1. #1
    wrigst is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    2

    Automating a search and replace task in Access 2010

    Hi All,



    Firstly let me apologise for being a complete newbie when it comes to Access 2010. I've searched this forum (and several other forums) for an answer to this but I've been unsuccessful in my quest. Here's what I am trying to do. I have 3 tables in an Access database: Table1 has a list of people that I need to go through and check if they exist in tables 2 and 3. Table2 has all the people listed by first name and last name, along with a special ID. Table 3 has system account information of each person, for which I need to find each person in table1 and enter the corresponding "special ID" from table2 for each person found.

    Since I have no idea how to automate this, I have been doing this manually but there are 8000 people to go through =(

    Put simply, I am hoping to achieve something like, a loop through the surnames in Table1, Find a match in Table2 and matched get the "Special ID", Find the same surname match in Table3 and enter the "Special ID" into the specified field. Is this even possible?

    I know I haven't provided much information here, and willing to do so, but can anyone help me with suggestions please? Thanks all.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    If you are using names in all tables, back fill the new person id's via:

    import the tPerson data.
    this creates IDs.
    make a query the pulls these newly added people and shows their new personID,name. (QsNewPersons)

    import the next tables. Join the new qsNewPersons query to the other tables on NAME.
    Update tables 2 & 3 with the new personID by joining on name.

    now the tPersons table has related child tables.

  3. #3
    wrigst is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    If you are using names in all tables, back fill the new person id's via:

    import the tPerson data.
    this creates IDs.
    make a query the pulls these newly added people and shows their new personID,name. (QsNewPersons)

    import the next tables. Join the new qsNewPersons query to the other tables on NAME.
    Update tables 2 & 3 with the new personID by joining on name.

    now the tPersons table has related child tables.
    Thanks Ranman256, sorry to be such a noob but when you say import the tPerson data, do you mean create a table with all the names and a primary key? Sorry i am still learning....thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-22-2014, 12:29 PM
  2. Replies: 16
    Last Post: 04-01-2014, 12:22 PM
  3. Replies: 10
    Last Post: 11-16-2012, 05:02 AM
  4. Replies: 2
    Last Post: 09-11-2012, 12:34 PM
  5. Keyword search with Access 2010 FE
    By Brian62 in forum SQL Server
    Replies: 1
    Last Post: 06-15-2012, 06:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums