How to Update only record fields (fax #'s) that have changed ?

  • Thread starter Thread starter JoeR
  • Start date Start date
J

JoeR

Searching for most efficient way to design a query
that will update changes to fields not add/append values
that have changed from table1 to end of table2. Table 1
and 2 have identical data structure. Table 1 is fact
table contains a list of unique customer numbers (primary
key) and other fields including phone# and fax # fields
that I am interested in maintaining. Table 2 Table 3
etc. will contain partial lists of the customer records
from table 1 some of which have had phone and fax numbers
changed.

What I want to do is run a query that will modify/update
only those fax numbers that have changed in table 2 to the
corresponding fax # field in table 1. Common field in
Table 1 and 2 is customer #.

inside and outside joins don't get me what I want. Update
and append queries don't do what I want.

Help - I want to automate updating
 
I would think that an update query would do what you
want. You could just use an inner join between your
tables and specify that you want to update records in
Table 2 where Table 1 FaxNo is not equal to Table 2
FaxNo.

To do this, you would drag the Table 2 FaxNo field to the
design grid. in the criteria section, you would enter: <>
[Table1].[FaxNo]. Then, in the update to section you
would enter: [Table1].[FaxNo]. You could also check and
update multiple fields at the same time. In that case
though, you would want to make sure that your criteria
were placed on different lines so that it would be
an "or" condition and do the update if any of the fields
were different.

Hope this helps.
 
Searching for most efficient way to design a query
that will update changes to fields not add/append values
that have changed from table1 to end of table2. Table 1
and 2 have identical data structure.
Click to expand...

This appears to be redundant. Why two tables?
Table 1 is fact
table contains a list of unique customer numbers (primary
key) and other fields including phone# and fax # fields
that I am interested in maintaining. Table 2 Table 3
etc. will contain partial lists of the customer records
from table 1 some of which have had phone and fax numbers
changed.
Click to expand...

So if you have the same customer with two different phone numbers in
the different tables - how can you tell which one is correct? I
suspect that tables 2, 3, etc. SHOULD NOT EXIST, at least not in their
present form! Might it not be better to just use a Select query
selecting the desired records from Table1? If you need a different
phone number, at the most I'd have a Table2 with nothing except the
CustomerID (primary key) and AlterenatePhone fieldss.
What I want to do is run a query that will modify/update
only those fax numbers that have changed in table 2 to the
corresponding fax # field in table 1. Common field in
Table 1 and 2 is customer #.
Click to expand...

There is no builtin way to determine if a record has changed. Do you
mean to update only those records where the Phone field is different
in Table1 than in Table2 (making the two tables fully redundant)? If
so a criterion of

<> [Table1].[Phone]

on the Table2 Phone field should limit the update to only those
records.
inside and outside joins don't get me what I want. Update
and append queries don't do what I want.
Click to expand...

Perhaps you could explain (maybe with an example) what you *do* want,
and/or post the SQL of the Update query which is not giving the
desired result.
 
Back
Top