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

Posted on 2011-09-28
Medium Priority
Last Modified: 2013-11-27
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?
Question by:andre_st
LVL 14

Accepted Solution

Bill Ross earned 2000 total points
ID: 36716170

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.


Author Comment

ID: 36813067
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

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

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.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

607 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