How to Combine Data from multiple tables or sources?

[Mark Welch]

I am at a complete loss on how to do this. I am using Microsoft Access
2000, to implement a preliminary version of a reasonably modest
relational database.

I have two Access Tables (imported from MS Excel spreadsheets), and I
need to somehow get the data for a "Person_ID" brought over from one to
the other.

In Table_A, there are many records for each person, and the first and
last name are included, but the Person_ID field is empty. There are
more than 65,000 records.

In Table_B, there is only one entry for each person, and each record
contains a field called Person_ID. There are more than 15,000 records.

I cannot figure out any way to bring the Person_ID data from Table_B to
Table_A, to allow me to create a relationship through the Person_ID
field to several other tables.

Can someone point me to a resource that would tell me how to do this?
If the data sources were smaller, I could export them to Excel and
manipulate them there, and then re-import tham, but there are more than
65,000 records.

[pietlinden]

user an update query to set the value of the TableA.personID to the
value of TableB.personID where

UPDATE TableA
SET PersonID = TableB.PersonID
WHERE TableA.FirstName=tableB.FirstName
AND TableA.Lastname=tableB.Lastname
AND TableA.SSN=TableB.SSN ...

but you need a combination of fields in the two tables to define an
accurate relationship.
How do you know which records are related just by looking at them? You
need to join on that/those field(s) in your update query.

[Mark Welch]

This sounds like the right sequence of directions, but I don't know how
or where to input this kind of script into Access 2000.

UPDATE TableA
SET PersonID = TableB.PersonID
WHERE TableA.FirstName=tableB.FirstName
AND TableA.LastName=tableB.LastName

[Mark Welch]

Can anyone point me to a resource that explains "Update Queries" in a
useful way? Or perhaps suggest what I may be doing wrong here?

I have now tried this a number of different ways, but no matter what I
do, Access simply prompts me for the value of each field, rather than
drawing any data from the database tables.

There are two tables: "Quotes_Table" and "Persons_Table", each
containing fields called
First_Name, Last_Name, and Person_ID. In the "Quotes_Table" there are
multiple entries for each person, including first and last name but NOT
Person_ID. I want to "bring over" the Person_ID field from the
Persons_Table. I have tried doing this with the Person_ID fields in
the two databases set to be related, and again in several permutations
without any relationship set; it seems to make no difference. (I cannot
relate the other fields (First_Name or Last_Name), apparently because
they are not primary keys?)

No matter what I do, the system prompts me to enter values. At one
point, I thought this was an issue related to Access inserting brackets
around some fields but not others, but the query functions exactly the
same with or without brackets:

UPDATE Quotes_Table SET Quotes_Table.Person_ID =
[Persons_Table].[Person_ID]
WHERE (([Quotes_Table].[Person_ID]=[Persons_Table].[First_Name]) And
([Quotes_Table].[Person_ID]=[Persons_Table].[LastName]));

UPDATE Quotes_Table SET Quotes_Table.Person_ID =
Persons_Table.Person_ID
WHERE ((Quotes_Table.Person_ID=Persons_Table.First_Name) And
(Quotes_Table.Person_ID=Persons_Table.LastName));

Can anyone point me to a resource that explains "Update Queries" in a
useful way? Or perhaps suggest what I may be doing wrong here?

[pietlinden]

you might want to start with a select query where you join the tables
together to get the data together. then turn that into an update query.

[Mark Welch]

I don't understand how I'd do that, but I think the logical sequence of
what I want is:

For each record in [Quotes_Table]:
Find the entry in [Persons_Table] with the same First_Name and
Last_Name;
SET [Quotes_Table].[Person_ID] = [Persons_Table].[Person_ID]

or

For each record in [Persons_Table]
Find all entries in [Persons_Table] with the same First_Name and
Last_Name;
For all matching records, SET [Quotes_Table].[Person_ID] =
[Persons_Table].[Person_ID]

[pietlinden]

Try this... the problem is that with 65K+ records, I don't know if
you're going to get a proper relationship working.

UPDATE Quotes_Table INNER JOIN Persons_Table ON
(Quotes_Table.PersonLast = Persons_Table.LastName) AND
(Quotes_Table.PersonFirst = Persons_Table.FirstName) SET
Quotes_Table.CustomerID = [Persons_Table].[PersonID];
Copy and paste the above statement into a query.

Create a new query. Click on the SQL button in DESIGN view. paste.

Oh, make a backup of your table before you do it... don't want to mess
up the data you have with no back door.

[Mark Welch]

Thanks for the detailed response, but Access just prompts me manually
for each field again. I am exporting the data to Excel, where I will
break it into chunks to merge manually -- it will probably take 3-4
hours but I don't think I will find a faster way.

[pietlinden]

Mark Welch wrote:

Thanks for the detailed response, but Access just prompts me manually
for each field again. I am exporting the data to Excel, where I will
break it into chunks to merge manually -- it will probably take 3-4
hours but I don't think I will find a faster way.

Once way to get the prompts is to not have those fields in your table.
What happens if you try to create a relationship between the two tables
(on FirstName and LastName)? If that part works, you _may_ be able to
run an update. The problem is that you have to have a one-to-many
relationship between the two tables.

If you have to do it in Excel, you can use H/VLookup, but it's about
the same thing. I'd spend a few more minutes in Access.

Try running the Find Unmatched query wizard and see if there are values
in one table that are not in the other. That will tell you which
records are screwing things up for you. Lots easier than doing
everything manually.