If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
non-match query
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? |
#2
|
|||
|
|||
non-match query
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 .. "johnny vino" wrote in message ... 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? |
#3
|
|||
|
|||
non-match query
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. "John Spencer" wrote: 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 .. "johnny vino" wrote in message ... 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? |
#4
|
|||
|
|||
non-match query
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 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. "John Spencer" wrote: 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 .. "johnny vino" wrote in message ... 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?- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
non-match query
On Wed, 21 Feb 2007 13:38:05 -0800, johnny vino
wrote: 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] |
#6
|
|||
|
|||
non-match query
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" wrote: On Wed, 21 Feb 2007 13:38:05 -0800, johnny vino wrote: 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] |
#7
|
|||
|
|||
non-match query
On Thu, 22 Feb 2007 09:51:00 -0800, johnny vino
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|