Quering on a portion of data in a field

[mrz]

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

 

[Michael J Ross]

What you could try is

1. Create a query on Table 1 and add a column which extracts the last 4 digits of the SS# using Columnname:Right([SSColumnname],4)

2. Create another query that joins the query just created with Table 2 using right joins on First and Last name and the column you created in query 1.

When you run query 2 it will extract all records from Table 2 and where there are matches from table 1 the fields will be populated, where there are no matches the fields from Table 1 will be blank.

 

[ColinEssex]

Your set-up is wrong.

You should have a table of 'people' with a field to identify if they are students or workers. This table holds all the demographic data of the people. These people should have an AutoNumber as an ID (PK).

The second table should list who works - using the ID number (FK) as an identifier. This table would list where and when they work, what hours, their start date of work and their end date when they leave etc etc

You can then pick up who is currently working using the ID as the link and if the end date is null then they must be working. Draw the demographic data from table 1 and the where / when etc from table 2. You can also identify how many workers are students or other workers.

Col