Update ID by matching last names in two tables

  • Thread starter Thread starter JadeG
  • Start date Start date
J

JadeG

I have two tables. One with clients and one with testing results. I'd like
to be able to run a query that can compare the two tables by using the last
and first name and then update the testing table with the ID number from the
clients table. I've done it before but for some reason I can't figure it out
again. Please help!
 
That can be dangerous as names are not unique except in very small
populations. Sometimes as little as one family - My Dad: John Spencer,
Me: John Spencer, and my son: John Spencer

IF we were all clients, then which id would be assigned to my test - my
dad, my son, or me?

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the
way you expect.
 
DANG NAB IT. Truncated again.

UPDATE Testing INNER JOIN Clients
ON Testing.LastName = Clients.LastName
AND Testing.FirstName = Clients.FirstName
SET Testing/ID = [Clients].[ID]

In the query grid,
Add both tables
Drag from lastname to lastName to create a join
Drag from firstname to firstname to create second part of join
Select the Testing Id field into the fields
Select Query: UPdate from the menu
In the UPDATE TO "cell" under the id field type the following - Note the
brackets are REQUIRED so the query will recognize you are inputting a
reference to a field and not a literal value.
[Clients].[ID]
Select Query: Run from the menu



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
as a note if two people have the same first and last name (AND I
ASSURE YOU IT HAPPENS the morons who set up the database i took over
didnt think about that and this one person cough two people are
causing me problems) your query wont work.

how do you know which record in the testing table is the record which
belongs to the client record or are you saying you haev first and last
name in both tables. if you do then after you do this update you
should remove that because it is duplication of data.

if that is the case use

update tbltesting,tblclient set tbltesting.clientid =
tblclient.clientid
where tbltesting.firstname = tblclient.firstname
and tbltesting.lastname = tblclient.lastname;

hope this helps

Regards
Kelvan
 
Back
Top