fill column data for matching key rows

[Guest]

I am try9ing to figure out how to merge two tables. What I have right now is
one table that has clientCode and clientName as columns, and another that has
clientCode as one column and address information for the rest of them. I
would like to merge these two so I end up with clientCode, clientName,
Address...

The only other catch is I don't have addresses for all clients in the first
table so those that don't have addresses I would still want in the merged
table but with blank for addresses.

I have looked into appendQueries, unionQueries, but with not success. Thanks

 

[George Hepworth]

Not a good idea.

The whole POINT to a relational database is that you have one table for each
entity (e.g., client and address) and that you RELATE them to one another
via the Primary and Foreign Keys (e.g., ClientCode).

If you want to create a query that DISPLAYS the combined names and
addresses, say for use in a report, you can, just do that in a normal select
query.

Otherwise, it would be a good investment of your time to learn more about
how relational databases work by studying normalization.

George

 

[Guest]

first build your ONE table with all the fields and make the ClientCode the
primary key (no duplicates.). Use an append query to add all client codes
form first table. Next append all client code from the second table - it will
say error as some duplicates exist and will not be appended due to uniqueness
of primary key.
Then use an update query joined on the table to update all fields in your
ONE table from first table. Then use an update query joined on the table to
update all fields that are null in your ONE table from second table.

 

[lbwbob]

first build your ONE table with all the fields and make the ClientCode the
primary key (no duplicates.). Use an append query to add all client codes
form first table. Next append all client code from the second table - it will
say error as some duplicates exist and will not be appended due to uniqueness
of primary key.
Then use an update query joined on the table to update all fields in your
ONE table from first table. Then use an update query joined on the table to
update all fields that are null in your ONE table from second table.

Thanks