Quering on a portion of data in a field

[mrz via AccessMonster.com]

Hi All:

I have no idea how to start this task.

Table 1:
Students that are registered this semester. The unique Identifier = full 9
digits of SS#'s

Table 2:
Students and regular people current working for the college. Unique identifer
= only the last 4 digits of their SS#'s

Objective:
I need to know what registerd students or regular people are currently
working in the college.
In other words, compare table 1 and table 2. The problem is I do not have a
common unique identifier in both tables.

Additional Info:
Each table also has:
* Last_Name
* First_Name

I was thinking many do a DUAL comparison...Let's say compare the last four
digits numbers and the last and first name from table 2 vs the last four
digits and the last and first name from table 1.

Can this be done?


Thank you very much in advance for any ideas.

MRZ

 

[Smartin]

Hi All:

I have no idea how to start this task.

Table 1:
Students that are registered this semester. The unique Identifier = full 9
digits of SS#'s

Table 2:
Students and regular people current working for the college. Unique identifer
= only the last 4 digits of their SS#'s

Objective:
I need to know what registerd students or regular people are currently
working in the college.
In other words, compare table 1 and table 2. The problem is I do not have a
common unique identifier in both tables.

Additional Info:
Each table also has:
* Last_Name
* First_Name

I was thinking many do a DUAL comparison...Let's say compare the last four
digits numbers and the last and first name from table 2 vs the last four
digits and the last and first name from table 1.

Can this be done?


Thank you very much in advance for any ideas.

MRZ

Yes you can compare the last digits, last name and first name. But
Table2 does not identify people uniquely. What will you do when you have
two different people named John Smith with different SSN's but the last
four digits are '1234'? This is not probable, but it can happen.

Moreover, I suspect Table1 and Table2 come from different sources. This
increases the likelihood that data entry in the name fields is not
consistent. Perhaps one table has "Smith, John M" and the other "Smith
M, John" or worse, "Smith PhD, John M". Are these the same person? There
is no way to tell with such limited information. Again, Table2 does not
do a good job of uniquely identifying people.

If you want to try things out, you could
- add both tables to a query
- draw joins between first name and last name in each table
- in SQL view put this in the WHERE clause (insert appropriate table and
field names):

Right(Table1.SSN) = Table2.LastFourSSN

I question how successful any of this will be however.

 

[mauricioze via AccessMonster.com]

Dear Smartin:

Thank you very much for taking your time to reply to my post.
I certainly agree with you regarding the lack of unique identifiers in table
2 (aka RF)
I am very curious to find out what the result of this attempt will yield.

I have a problem. This is my SQL statement:

SELECT
FROM SIMS INNER JOIN RF ON (SIMS.First_Name = RF.First_Name) AND (SIMS.
Last_Name = RF.Last_Name);
WHERE Right(RF.SS#)= SIMS.LastFourSS#

When trying to run this...I get the following:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

I am sorry I am not very familiar with SQL but I can learn very quick

Thanks again for any thoughts on this step.

MRZ

Hi All:

[quoted text clipped - 28 lines]

MRZ

Yes you can compare the last digits, last name and first name. But
Table2 does not identify people uniquely. What will you do when you have
two different people named John Smith with different SSN's but the last
four digits are '1234'? This is not probable, but it can happen.

Moreover, I suspect Table1 and Table2 come from different sources. This
increases the likelihood that data entry in the name fields is not
consistent. Perhaps one table has "Smith, John M" and the other "Smith
M, John" or worse, "Smith PhD, John M". Are these the same person? There
is no way to tell with such limited information. Again, Table2 does not
do a good job of uniquely identifying people.

If you want to try things out, you could
- add both tables to a query
- draw joins between first name and last name in each table
- in SQL view put this in the WHERE clause (insert appropriate table and
field names):

Right(Table1.SSN) = Table2.LastFourSSN

I question how successful any of this will be however.

 

[Smartin]

Dear Smartin:

Thank you very much for taking your time to reply to my post.
I certainly agree with you regarding the lack of unique identifiers in table
2 (aka RF)
I am very curious to find out what the result of this attempt will yield.

I have a problem. This is my SQL statement:

SELECT
FROM SIMS INNER JOIN RF ON (SIMS.First_Name = RF.First_Name) AND (SIMS.
Last_Name = RF.Last_Name);
WHERE Right(RF.SS#)= SIMS.LastFourSS#

When trying to run this...I get the following:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

I am sorry I am not very familiar with SQL but I can learn very quick

Thanks again for any thoughts on this step.

MRZ

Sorry, my expression of "LastFourSSN" was not meant to be taken
literally. Also, I see I erred in my suggestion regarding the Right()
function. SIMS contains the full SSN, right? So the WHERE clause might
should be

{view in monospace font}

WHERE RF.[SSN#] = Right(SIMS.[SSN#],4)
^^^^
insert correct field name for SIMS here

HTH

Hi All:

[quoted text clipped - 28 lines]

MRZ

Yes you can compare the last digits, last name and first name. But
Table2 does not identify people uniquely. What will you do when you have
two different people named John Smith with different SSN's but the last
four digits are '1234'? This is not probable, but it can happen.

Moreover, I suspect Table1 and Table2 come from different sources. This
increases the likelihood that data entry in the name fields is not
consistent. Perhaps one table has "Smith, John M" and the other "Smith
M, John" or worse, "Smith PhD, John M". Are these the same person? There
is no way to tell with such limited information. Again, Table2 does not
do a good job of uniquely identifying people.

If you want to try things out, you could
- add both tables to a query
- draw joins between first name and last name in each table
- in SQL view put this in the WHERE clause (insert appropriate table and
field names):

Right(Table1.SSN) = Table2.LastFourSSN

I question how successful any of this will be however.