JS-Q4: Merging tables

J

Jack Sheet

Sorry to trouble all y'all again.

I have two tables "T_Clients" and "T_YearEnds".
Each has an identical key field (ClientRef)
The tables have a one-to-one relationship set up on the key fields with
enforced referential integrity.
I find that there is no point in keeping the data in two datasheets, and I
wish to merge the "T_YearEnds" fields into "T_Clients" datasheet. I have
set up fields (all currently blank) in T_Clients, but have ground to a halt
trying to import the data from T_YearEnds.

It sounds like an "update query" is required, but cannot work out the
syntax. Any help appreciated.

PS: please ignore character string JS-Q4 in subject header.
 
J

John Spencer

Set up a select query and then change that to an update query.

UPDATE T_Clients INNER JOIN T_YearEnds
ON T_Clients.ClientRef = T_YearEnds.ClientRef
SET T_Clients.SomeField = T_YearEnds.SomeCorrespondingFields
T_Clients.AnotherField = T_YearEnds.AnotherCorrespondingField
 
B

Bob Miller

I would use a make table query of both tables joined by the ClientRef.
I'm pretty sure you already have a query that does that, just do a Make
Table out of it. Do this in design view. You can then create a pass
through query of the same name as the query that joined the two tables
and all references in Forms, Reports, etc. will be ok.
 
J

Jack Sheet

Worked like a charm. Thanks.

John Spencer said:
Set up a select query and then change that to an update query.

UPDATE T_Clients INNER JOIN T_YearEnds
ON T_Clients.ClientRef = T_YearEnds.ClientRef
SET T_Clients.SomeField = T_YearEnds.SomeCorrespondingFields
T_Clients.AnotherField = T_YearEnds.AnotherCorrespondingField
Click to expand...
 
J

Jack Sheet

Thanks Bob
I had thought that might be the way to go but John Spencer solved it for me.
Thanks for looking at it anyway.
 
Top