Solved

Update query with multiple joins

Posted on 2009-05-06
6
187 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now