Update query

[Guest]

I have two tables with the following fields:

Table A(100 records):
FirstName
LastName
Type

Table B(550 records)
First Name
LastName
Type
Sex
Finish

I would like to create a query in the query design window that would match
the records in table A with table B and update the Type field in table B with
the data in the type field in table A. Table A type field contains more
recent updated information that I need to get into the Type field in table B.

I know who to do the join of LastName and FirstName to match the correct
records in Table A to Table B. How do I get table B's Type field updated?

Thanks

 

[John Vinson]

I have two tables with the following fields:

Table A(100 records):
FirstName
LastName
Type

What will you do when you have a record for Bill Jones, the tall lanky
blond guy, and Bill Jones, the stocky tough brunet?

Names are NOT unique and do not make good keys. You really should
consider having a PersonID field (perhaps an autonumber) as a unique
identifier - that way Billy could have ID 312, and Bill 398, and
you'ld not have that particular problem!

Table B(550 records)
First Name
LastName
Type
Sex
Finish

I would like to create a query in the query design window that would match
the records in table A with table B and update the Type field in table B with
the data in the type field in table A. Table A type field contains more
recent updated information that I need to get into the Type field in table B.

I know who to do the join of LastName and FirstName to match the correct
records in Table A to Table B. How do I get table B's Type field updated?

You're storing data redundantly. The Type field should be in one table
or the other, NOT both, so this kind of discrepancy will not arise!

That said... select FirstName and LastName in Design view in TableA;
click the Key icon to make the two fields a joint primary key.

Now create a Query adding the two tables. Link FirstName to FirstName,
LastName to LastName. Change the query to an Update query and put

[TableA].[Type]

including the brackets under the Type field of TableB, on the Update
To line. Run the query by typing the ! icon.

John W. Vinson[MVP]

 

[Guest]

Change the query to an update query and set the Update To under TableB.Type to
[TableA].[Type]

This will update tableB type to the value in TableA.Type. This assumes
FirstName and LastName combine to a unique index in TableA.