|
Subject:
|
update tables
|
|
Posted By:
|
jjebb
|
Post Date:
|
10/23/2003 10:09:37 PM
|
Hi,
I have 2 databases, one with a live-client table and one with a constantly updated client table. Is there a way to automatically compare the two tables and update the live-client table with the updated data from the other table? I don't want any records deleted but I do need changes to records updated - addresses, contact details etc.
any thoughts?
|
|
Reply By:
|
sal
|
Reply Date:
|
10/24/2003 8:35:52 AM
|
Why don't you just link the tables from one database to another. This setup will only complicate your life.
It is very difficult to compare two tables field by field.
Sal
|
|
Reply By:
|
jjebb
|
Reply Date:
|
10/26/2003 8:20:09 PM
|
I've figured out that I can compare the two tables field by field by using the Find Unmatched Query Wizard and that works ok but the result is fields that are different as well as fields that are both blank. I only need the result to be fields that are different.
Can anyone help??
|
|
Reply By:
|
pmmgpgp
|
Reply Date:
|
10/26/2003 9:42:36 PM
|
jjebb is might be of benefit.
Your origional request was to " update " not just identify.
I think that you may be "there" with the query you have. Make each field update conditional on the contents of the field in the "constantly updated client table"; should it be 'not null and <>"" ' then do the update.
Dave
|
|
Reply By:
|
sal
|
Reply Date:
|
10/26/2003 11:33:59 PM
|
Please describe in detail the "business need" for this. I kow that this may seem drastic, but, this will help in coming up with a better solution for you.
Replication/syncronization may be best suited for this.
Are you also making changes to both tables? Are both tables on the same database?
Sal
|
|
Reply By:
|
jjebb
|
Reply Date:
|
10/27/2003 2:29:49 AM
|
OK,
I have one database with a table called 'Clients' in it. I have another database that has the Clients table linked to it and another table called 'Updated_Cliets'. The Updated table is updated once a week with client info eg. name, address, abn, phone number etc. The linked client table does not have all the clients that the updated one has.
I need a process of comparing the clients from the linked table with the updated table and i need it to produce an ouput of any differences between the two tables but not the clients that are not in the linked table.
I've used the find unmatched query wizard but this seems to give me only the differences in one field as well as all fields that null in both tables.
hope this explanation helps.
|
|
Reply By:
|
sal
|
Reply Date:
|
10/27/2003 11:26:09 AM
|
An unmathced query will find all new records from one table or deleted records.
For any changes of existing records, you will need something like pmmgpgp
mentions avobe.
Create a query that pulls all records from both tables where the ID is the same, and if any field <> to the other table, update the table that you have not updated.
You may be doing things the hard way. Why not just update the linked table?
Sal
|
|
Reply By:
|
sal
|
Reply Date:
|
10/27/2003 11:26:57 AM
|
What I meant by that is, why not just work out of one table, the linked table, and forget about re-freshing tables.
Sal
|
