if the string is part of the base string, then...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. The tbl1 has [lastName], [firstName], [studNum]. The tbl2
has [lastName], [studNum]. I would like to write codes or make a query that
compares tbl2 to tbl1 by [lastName]. If there is a match, update
tbl2.[studNum] to tbl1.[studNum]
Can you help me to do that?
Thank you.
 
Well, it can be done, but I would think it would be very dangerous, unless
you have no last names being duplicated.

UPDATE Tbl2 INNER JOIN Tbl1
ON tbl2.LastName =tbl1.LastName
SET tbl2.StudNum = [tbl1].[StudNum]

in the query grid
-- Add both tables to the query
-- Drag from lastName to Lastname to create the join
-- Drag tbl2.StudNum into a field
-- Select Query: Update from the menu
-- Enter [tbl1].[StudNum] in the Update To "cell" under tbl2.StudNum
(DO NOT forget the brackets. If quote marks appear when you leave the
field, STOP and try again.)
-- Select Query: Run from the menu.

WARNING: BACK UP your data first, this process cannot be undone.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I have two tables. The tbl1 has [lastName], [firstName], [studNum]. The tbl2
has [lastName], [studNum]. I would like to write codes or make a query that
compares tbl2 to tbl1 by [lastName]. If there is a match, update
tbl2.[studNum] to tbl1.[studNum]
Can you help me to do that?
Thank you.
Click to expand...

Ummm...

Do you really want to do that!?

Suppose you have students Jim Brown, Jim Brown, Jane Brown, Phyllis Brown and
Larry Brown. Which one of these should be updated to Jane Brown's student
number?

IF - and it's very questionable in general! - you have one and only one
student in your entire table for each value of LastName, you can create a
query joining tbl1 to tbl2 by LastName (add both tables to the query grid and
drag LastName to LastName). Change the query to an Update query and put

[tbl1].[StudNum]

on the Update To line underneath tbl2.StudNum. Run the query by clicking the !
icon. This may need a unique Index on LastName in order to work, but try it
without first.

John W. Vinson [MVP]
 
Thank you, both of you for alerting me the dangerousness of updating many
poeple who have the same last name. I need to get enough information to
working on this. I will keep you posted. Thanks again.
tim

John W. Vinson said:
I have two tables. The tbl1 has [lastName], [firstName], [studNum]. The tbl2
has [lastName], [studNum]. I would like to write codes or make a query that
compares tbl2 to tbl1 by [lastName]. If there is a match, update
tbl2.[studNum] to tbl1.[studNum]
Can you help me to do that?
Thank you.
Click to expand...

Ummm...

Do you really want to do that!?

Suppose you have students Jim Brown, Jim Brown, Jane Brown, Phyllis Brown and
Larry Brown. Which one of these should be updated to Jane Brown's student
number?

IF - and it's very questionable in general! - you have one and only one
student in your entire table for each value of LastName, you can create a
query joining tbl1 to tbl2 by LastName (add both tables to the query grid and
drag LastName to LastName). Change the query to an Update query and put

[tbl1].[StudNum]

on the Update To line underneath tbl2.StudNum. Run the query by clicking the !
icon. This may need a unique Index on LastName in order to work, but try it
without first.

John W. Vinson [MVP]
Click to expand...
 
Back
Top