Solved

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

Posted on 2011-09-28
2
262 Views
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?
0
Comment
Question by:andre_st
2 Comments
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
Comment Utility
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
 

Author Comment

by:andre_st
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 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

11 Experts available now in Live!

Get 1:1 Help Now