non-match query

[Guest]

I'm comparing two tables and I would like to see all the records where first
and last name DON'T match. I know there are several instances where names
aren't spelled the same in each table, and I want to correct that.

What is the command/statement for pulling out these records?

 

[John Spencer]

Do you have some other data in the records that would allow you to identify
which record in table a matches which record in table b?

Or do you just want a list of records in table a that are not in table b and
those in table b that are not in table a. The SQL to get all the records in
TableA that don't have a match in TableB where you only have the names to
build the match would look like the following.

SELECT TableA FirstName, TableA.LastName
FROM TableA LEFT JOIN TableB
ON TableA.FirstName = TableB.FirstName AND
TableA.LastName = TableB.LastName
WHERE TableB.LastName is Null and TableB.FirstName is Null

That tells you which records in A don't exist or are not matched in TableB.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Question: Is a LEFT JOIN in sql the same as making the drag-connect join in
Access Design view?

I made those joins in the query, but I just didn't know what to type in the
Criteria section to get my non-match results. Typing "Is Null" under
lastname.tableA, and firstname.tableA doesn't find any non-matched records -
when there are potentially dozens.

 

[Jason Lepack]

For the following example refer to these tables:

tbl_customer:
cust_ID, cust_name
1, Bob
2, Joe
3, Jason

tbl_purchase:
cust_id, item, qty
1, Blowdryer, 1
1, Bottle of Pepsi, 10
2, Bottle of Pepsi, 5

Question: Is a LEFT JOIN in sql the same as making the drag-connect join in
Access Design view?

No. Just dragging to connect a join in Design View is an "INNER
JOIN". An INNER JOIN selects data from both tables where a record
exists in each. If you joined these two tables on cust_id you would
be returned this:

cust_name, item, qty
Bob, Blowdryer, 1
Bob, Bottle of Pepsi, 10
Joe, Bottle of Pepsi, 5

The SQL for this looks like this:
SELECT tbl_customer.*, tbl_purchase.*
FROM tbl_customer
INNER JOIN tbl_purchase
ON tbl_customer.cust_id = tbl_purchase.cust_id

Now, a LEFT JOIN displayes all records from the leftmost table as well
as all the records from the other table where the value is equal.
With a LEFT JOIN you would get this:

cust_name, item, qty
Bob, Blowdryer, 1
Bob, Bottle of Pepsi, 10
Joe, Bottle of Pepsi, 5
Jason, ,

*Notice that the fields that didn't have matches in tbl_purchase are
null.

SQL for above:
SELECT cust_name, item, qty
FROM tbl_customer
LEFT JOIN tbl_purchase
ON tbl_customer.cust_id = tbl_purchase.cust_id

Now if I wanted to find the customers that didn't have purchases (such
as Jason above) I would use this LEFT JOIN and set the criteria for
the tbl_purchase.cust_id field to Is Null.

SQL:
SELECT cust_name
FROM tbl_customer
LEFT JOIN tbl_purchase
ON tbl_purchase.cust_id = tbl_customer.cust_id
WHERE tbl_purchase.cust_id Is Null

This returns:
cust_name
Jason

Cheers,
Jason Lepack

 

[John W. Vinson]

Question: Is a LEFT JOIN in sql the same as making the drag-connect join in
Access Design view?

No. To add to Jason's answer, you can open the query (created by
drag-connect) in design view; doubleclick on the Join line in the
query tables window, and choose Option 2 (or 3) - and select the
option "Show all records in <one side table> and matching records in
<many side table>".

John W. Vinson [MVP]

 

[Guest]

None of that is working for me - or else I'm just not understanding it
correctly (more likely)

I have tableA with test records for several thousand students. TableB
contains roster records for slightly fewer students. These tables share high
school code as the connection I'm using to filter the students. There's no
common ID#.

When I query to get the firstname and lastname matches, here is what the SQL
looks like:

SELECT PreTest2006.LASTNAME, PreTest2006.FRSTNAME, PreTest2006.HSCODE,
[NGA/ACT Pilot Project Roster 2-12-07].last_name, [NGA/ACT Pilot Project
Roster 2-12-07].first_name
FROM ([NGA/ACT Pilot Project Roster 2-12-07] INNER JOIN PreTest2006 ON
(PreTest2006.FRSTNAME = [NGA/ACT Pilot Project Roster 2-12-07].first_name)
AND (PreTest2006.LASTNAME = [NGA/ACT Pilot Project Roster 2-12-07].last_name)
AND ([NGA/ACT Pilot Project Roster 2-12-07].site_ID = PreTest2006.HSCODE))
INNER JOIN [teacher participation info] ON ([NGA/ACT Pilot Project Roster
2-12-07].site_ID = [teacher participation info].HSCODE) AND ([NGA/ACT Pilot
Project Roster 2-12-07].teacher = [teacher participation info].Teacher)
WHERE (((PreTest2006.HSCODE)="372680"));

This gets me all the exact matches in a somewhat duplicate format. That's
OK. What I really need is to see all the rows where the names do not match,
so that I can find the students who are "Joe" in one table and "Joseph" in
another - then edit those records to that they are true matches.

Putting Is Null as the criteria doesn't pull any records.

 

[John W. Vinson]

This gets me all the exact matches in a somewhat duplicate format. That's
OK. What I really need is to see all the rows where the names do not match,
so that I can find the students who are "Joe" in one table and "Joseph" in
another - then edit those records to that they are true matches.

Putting Is Null as the criteria doesn't pull any records.

It won't and can't, because the Inner Join ONLY finds matches.

Try a LEFT JOIN from the PreTest2006 table:

SELECT PreTest2006.LASTNAME, PreTest2006.FRSTNAME, PreTest2006.HSCODE,
[NGA/ACT Pilot Project Roster 2-12-07].last_name, [NGA/ACT Pilot
Project Roster 2-12-07].first_name
FROM ([NGA/ACT Pilot Project Roster 2-12-07] LEFT JOIN PreTest2006 ON
(PreTest2006.FRSTNAME = [NGA/ACT Pilot Project Roster
2-12-07].first_name)
AND (PreTest2006.LASTNAME = [NGA/ACT Pilot Project Roster
2-12-07].last_name)
AND ([NGA/ACT Pilot Project Roster 2-12-07].site_ID =
PreTest2006.HSCODE))
INNER JOIN [teacher participation info] ON ([NGA/ACT Pilot Project
Roster
2-12-07].site_ID = [teacher participation info].HSCODE) AND ([NGA/ACT
Pilot
Project Roster 2-12-07].teacher = [teacher participation
info].Teacher)
WHERE (((PreTest2006.HSCODE)="372680"));

Note that joining by names is multiply risky: not only will you have
variant spellings, but also with any reasonably large list, you'll
have two students who happen to have the same name. Identifying these
may be tricky.

John W. Vinson [MVP]