merge certain data from 1 table to another

[sparky]

TBL1 has the following fields: ID, GPSX, GPSY, NAME and has 200 records.
TBL2 has the following fields: ID, GPSX, GPSY, NAME and has 2000 records.
TBL1's GPSX and GPSY fields are blank, while TBL2's are not. I want to
compare the ID numbers of the 2 tables, and if they are equal, move TBL2's
GPS (X & Y) fields into TBL1's GPS (X & Y) fields.

I believe I should use update query but I can't come up with the correct
method to do this.

Anyone want to offer some easy methods?

Thanks

Rick

 

[Beetle]

Update tbl1 Inner Join tbl2 On tbl1.ID = tbl2.ID
Set tbl1.GPSX = tbl2.GPSX, tbl1.GPSY = tbl2.GPSY

 

[KARL DEWEY]

First thing is to BACKUP DATABASE! BACKUP DATABASE!

Create a query in design view, placing both tables in the space above the
grid.

Click on the ID field of TBL1 and drag to TBL2 ID field.

Click on TBL1 GPSX and drag down tpo the Field row of the grid. Repeat for
TBL1 GPSY.
In the Criteria row below each field type Is Null on the same line.

Run the query at this point to check data results.

Based on which verison of Access you have, click Update icon or select
Update from Query Type pull down.

In the Update To row type [TBL2].[GPSX] under TBL1 GPSX and
[TBL2].[GPSY] under TBL1 GPSY.

 

[Jeff Boyce]

Are you saying that they do NOT have the same 2000 records?

Because if they did, you could just make a copy of Table2 and rename it ...

By the way, why have two tables with identical structure? That's not a
well-normalized design...

Regards

Jeff Boyce
Microsoft Office/Access MVP