Solved

Update query with multiple joins

Posted on 2009-05-06
6
191 Views
Last Modified: 2012-05-06
I have 2 tables from which I am comparing several fields. I want to update a non-compared field (checkbox) in the first table if any of the conditions are met.

I can do the join's and then union them together but can't figure out how to get the update part to work.

The example would be:
Table1 - Name, Address, Phone
Table2 - Name, Address, Phone

I want to only show results if
Table1.Name<>Table2.name joined on Table1.Address=Table2.Address
OR
Table1.Address<>Table2.Address joined on Table1.Name=Table2.name
OR
Table1.Phone<>Table2.Phone joined on Table1.Name=Table2.name

If any of these records show up then it would place a check in the box (or yes or true)

Thanks!

0
Comment
Question by:edrz01
  • 5
6 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24317823
You need to set the query type to 'Dynaset (Inconsistent updates)'; see image below.
You can open the Query Properties dialog by right-clicking your mouse in an open area of the 'Tables' window while in Query Design.

Query-Properties.jpg
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24317856
By the way, you can't do an update (inconsistent or otherwise) on a Union query.
You'd probably have better luck doing this as 3 separate queries.
Todd
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24317950
I'll admit I haven't been much help to this point. I'm putting together the actual queries for you, but I have to step out for about 45 minutes.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 7

Expert Comment

by:shambalad
ID: 24318736
Try this:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone OR B.Address Is Null

For the above query, I added a field 'MisMatchError' to table1.

Table1-Design.jpg
0
 
LVL 7

Accepted Solution

by:
shambalad earned 500 total points
ID: 24319019
The previous query update the MisMatch byte to true under the following circumstances:
1. Table1 Name = Table2 Name AND table1 Address <> table2 Address
    "A.Address<>B.Address"
2. Table1 Name = Table2 Name AND table1 Phone <> table2 Phone
   "A.Phone<>B.Phone"
3. Table1 Name does not match any of the Names in Table 2
   "B.Address Is Null"

There is a potential weakness in the 3rd criterion, since it would be true if the Names match and the Address in Table 2 is null, so to more correctly satisfy the 3rd condition then query should be:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone OR B.Name Is Null

If, per chance, both Names are Null, they still wouldn't be equal since two Nulls will never evaluate as True.

Beyond that, though, the 3rd criterion is not really what you originally asked for; and could be problematic. For instance, if  not having a matching Name in Table2 is not an error. Then the above query will be incorrectly taging records in table1 that don't have a matching record in Table2.

To match your specs (no more, no less), you really would need two queries:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone

UPDATE Table1 AS A INNER JOIN Table2 AS B ON A.Address = B.Address SET A.MisMatchError = True WHERE A.Name<>B.Name
0
 

Author Closing Comment

by:edrz01
ID: 31578585
Thanks!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question