Un matched query on more than one field

  • Thread starter Thread starter nath
  • Start date Start date
N

nath

Hello

I have two tables that i want to compare:

tbl_main_data and tbl_tmp_data

Tbl_main_data holds all the records that were current as
of a last refresh. tbl_tmp_data holds the same records
plus some new ones. I have no unique fields, but want to
compare the two tables and be left with the records from
tbl_tmp_data that arent in tbl_main_data. There are 4
fields that are semi-unique, these are forename, surname,
add1,add2,add3 and post code.

Can i do this using an umatched query?

TIA

Nath
 
I came to this board for assistance with essentially the
same issue. Two tables each with three fields LN FN and ID

ID is a primary key in both tables.

There are records that are common to both tables, some
records in both tables that are not in the other.

I only want to work with the subset of records that is
common to both tables based in the ID field.

Once I have those records I only want to see the records
where the ID's are the same but the LN field is different.

In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do
not.

Thanks!
 
nath said:
I have two tables that i want to compare:

tbl_main_data and tbl_tmp_data

Tbl_main_data holds all the records that were current as
of a last refresh. tbl_tmp_data holds the same records
plus some new ones. I have no unique fields, but want to
compare the two tables and be left with the records from
tbl_tmp_data that arent in tbl_main_data. There are 4
fields that are semi-unique, these are forename, surname,
add1,add2,add3 and post code.

Can i do this using an umatched query?
Click to expand...
Hi Nath,

There is a "Find Unmatched Query Wizard" that you
start by clicking on "New" in Query Object.

But it expects that you only want to find the "unmatched"
based on one field. It appears you want to find the
"unmatched" across 4 (or possibly 6 fields?).

You could run this wizard using any one of the fields,
then go back and add the LEFT JOIN's for the other fields
and "IS NULL" criteria for them.

After you run the wizard, you will get an idea of how an
"unmatched" query works.

Basically, you will be using an outer join that wants to return
all of the records from tbl_tmp_data, but you will filter all of
those records for where the "unmatch" field that you join the
2 tables on cannot find a match in tbl_main_data, i.e.,
WHERE tbl_main_data.unmatchfield IS NULL.

This initial query will probably look something like
(if you chose surname as the "unmatched" field):

SELECT tbl_tmp_data.*
FROM
tbl_tmp_data LEFT JOIN tbl_main_data
ON
tbl_tmp_data.surname = tbl_main_data.surname
WHERE tbl_main_data.surname IS NULL;

You see how this works?

The LEFT JOIN is saying

"I'd like to return all of tbl_tmp_data"

but the WHERE clause is saying

"but really just return those records from
tbl_tmp_data where we did not find a match
for surname in the tbl_main_data table."

Starting with this query, in Query Design View,
we can create more left joins for all your "unmatch"
fields.

Drag and drop forename from tbl_tmp_data
onto forename in tbl_main_data. This should produce
another line (inner join) between these 2 fields.
Right-mouse click on this line and choose "Join Properties"
Change join to "2" (Include all records from tbl_tmp_data...).

Now drag and drop tbl_main_data.forename down
to a Field row in an empty column of the grid.Your
new column should look like:

Field: forename
Table: tbl_main_data
Sort:
Show:
Criteria:
or:

uncheck Show (there's no reason to "show" a null).

All that's left is to type in "IS NULL" for criteria,
but whether you type it in the "Criteria:" row,
or the "or:" row depends on the "logic" of this
unmatched query.

If you consider an "unmatch" to be where there is
no match between surname AND no match between
forename, then put it in the "Criteria:" row.

SELECT tbl_tmp_data.*
FROM
tbl_tmp_data LEFT JOIN tbl_main_data
ON
(tbl_tmp_data.surname = tbl_main_data.surname)
AND
(tbl_tmp_data.forename = tbl_main_data.forename)
WHERE
(tbl_main_data.surname IS NULL)
AND
(tbl_main_data.forename IS NULL);

Do you see how the "unmatch logic" of this might be different
if you put the "IS NULL" in the "or:" row?

SELECT tbl_tmp_data.*
FROM
tbl_tmp_data LEFT JOIN tbl_main_data
ON
(tbl_tmp_data.surname = tbl_main_data.surname)
AND
(tbl_tmp_data.forename = tbl_main_data.forename)
WHERE
(tbl_main_data.surname IS NULL)
OR
(tbl_main_data.forename IS NULL);

As Access is processing a record in the first ("AND") query
and deciding whether to return it, there has to be
an "unmatch" between BOTH fields.

In the second ("OR") query, there needs to be only an
"unmatch" between EITHER field.

You know your data and what you want to define
as an "unmatch." My guess is you want to use "AND."

The process to include the next "unmatch field" will be
similar, except if you go the "OR" route. In that case,
your "IS NULL" will go in the "next or:" row (underneath
the "or:" row). Then for the next "unmatch field," the "IS NULL"
will go in row underneath that.

If you go the "AND" route (which I believe is what you want),
all "IS NULL" will go in the "Criteria:" row.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
"(e-mail address removed)"
I came to this board for assistance with essentially the
same issue. Two tables each with three fields LN FN and ID

ID is a primary key in both tables.

There are records that are common to both tables, some
records in both tables that are not in the other.

I only want to work with the subset of records that is
common to both tables based in the ID field.

Once I have those records I only want to see the records
where the ID's are the same but the LN field is different.

In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do
not.
Click to expand...
Hi m,

Did you try bringing both tables into a query,
join on the 2 fields, go into SQL view where
you might have something like:

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN = tbl2.LN;

and change "=" to "<>"

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN <> tbl2.LN;

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Gary, Thank you so much, it worked beautifully. I had
fiddled around in SQL but didn't really know what I was
doing. Your reply helped me understand a bit about basic
SQL syntax. Thanks again for taking the time to reply.

Mary
 
In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do
not.
Click to expand...
Hi Mary,

There was one aspect to the solution below
that I had not thought about until I just
read another post from Michel.

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN <> tbl2.LN;

It has to do with the final condition.

If either (or both) LN could be Null,
the above SQL would not return those
records.

LN <> Null = Null
Null <> LN = Null
Null <> Null = Null

Michel's solution was to move this
condition to the WHERE clause and
use NZ:

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
WHERE
NZ(tbl1.LN <> tbl2.LN, -1);

It will include where "Null=Null" though,
but will catch where one is null, so they
"do not match."

If you do not want to return "Null=Null"
case, I guess you could include second
test in WHERE clause

WHERE
NZ(tbl1.LN <> tbl2.LN, -1)
AND NOT
(tbl1.LN IS NULL
AND
tbl2.LN IS NULL);

One more thing learned.

Thank you Michel.


Gary Walter
 
Back
Top