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?
andre_stAsked:
Who is Participating?
 
Bill RossCommented:
Hi,

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.

Bill
0
 
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,
André
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.