• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

How to set up a condition for datacopying a specific field from one database to another?

I have two databases (which consist of two imported csv-files).

The first database contains data like this:

user-ID            Full name      Surname            Firstname
user-ID2      Full name2      Surname2      Firstname2
user-ID3      Full name3      Surname3      Firstname3

The second databse contains data like this:

Full name      Surname            Firstname      Class
Full name2      Surname2      Firstname2      Class2
Full name3      Surname3      Firstname3      Class3

The names/users are mostly the same in both databases, but there are some extra users in the first one, but only one of them contains the user-ID's, and only one of them contains the class-ID. What I need to do is to create a list of all the users who exist in both databases. And therefore I guess that the appropriate approach would be to copy the user-ID from the first database to the second.

So the result would be like this:

user-ID            Full name      Surname            Firstname      Class
user-ID2      Full name2      Surname2      Firstname2      Class2
user-ID3      Full name3      Surname3      Firstname3      Class3

But I need to set up a condition for the copying. Something like:

if "surname" and "firstname" in database 1 exist in database 2, copy the field "user-ID" to database 2.

I am trying to achieve this in Microsoft Access 2010, but I am new to Access, so if you can help - you don't need to worry about beeing to precise in your explanation ,-)

p.s. as the databases consist of imported csv-files, I wonder if the procedure above could as easily be done in Excel?
1 Solution
Bill RossCommented:

1. Create a new database.
2. Use Link Table Manager to link both tables from the other 2 databases.
3. Create a new query.
4. Add both tables.
5. Join the 2 tables by Surname=Surname and Firstname=Firstname (this will get a list of matches between the 2 tables - assuming there are no duplicates)
6. Make the output Surname, Firstname and user-ID
7. View and if OK change to Make Table query
8. Run it
Now you have a list of Surname and FirstName with the associated user-ID you can then use to update the correct table.

If you need more detail just le tme know.

andre_stAuthor Commented:
Hey Bill!

Thanks for you reply! I got your suggestion to work ,-) Even though many follow up question turned up, I still got the answer to my initial question. But I'm gonna try to figure out the rest myself.

Kind regards,

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now