finding Students not listed in a StudentList

[jazzin]

I have a list of 3869 Students but this has misspellings and wrong addresses and I have a master address list with 11000 addresses in it. I was able to query out 3392 students based on writing a query comparing addresses and street names. I then was able to use the Find UnMatched query tool in Access to find 266 misspellings and/or addresses that didn't match but there is still 210 students missing from the list that it isn't finding. I am using address and Street Name as the fields that I am basing my query on as these fields or in both tables. Can anybody help me on how to write up a query to find these missing addresses so I can correct them. Thanks

[June7]

Your numbers are off by 1. The math shows 211 students not accounted for.

Not quite following what you have done. The FindUnmatched should have shown 477 Student records did not match the Master. How did you then determine corrections for 266 records and not the remaining 211?

[jazzin]

Your numbers are off by 1. The math shows 211 students not accounted for.Not quite following what you have done. The FindUnmatched should have shown 477 Student records did not match the Master. How did you then determine corrections for 266 records and not the remaining 211?

You are correct there are 477 records missing all together but when I run the Find Unmatched Query tool and use address as the join it only returns 266 records that did not match. Don't know why it isn't returning all 477 records?

[June7]

3392 records were an exact match on the address/streetname so 477 records should not match but only 266 are showing? Odd.

Show the attempted queries and maybe can determine issue but I expect I would have to work with data to fully analyze issue.

[jazzin]

3392 records were an exact match on the address/streetname so 477 records should not match but only 266 are showing? Odd.Show the attempted queries and maybe can determine issue but I expect I would have to work with data to fully analyze issue.

Here is the query that got me the 3392 records

SELECT Address.ADDPTID, Address.ADDRESS, Address.ST_LABEL, newstudentpop.SCHOOL_NAME, newstudentpop.LAST_NAME, newstudentpop.MIDDLE_NAME, newstudentpop.FIRST_NAME, newstudentpop.ADDRESS, newstudentpop.[P O Box], newstudentpop.ST_NAMEFROM Address, newstudentpop WHERE Address.ADDRESS=newstudentpop.ADDRESS And Address.ST_LABEL=newstudentpop.ST_NAME;

Here is the query I ran to get 266 unmatched records

SELECT newstudentpop.SCHOOL_NAME, newstudentpop.LAST_NAME, newstudentpop.MIDDLE_NAME, newstudentpop.FIRST_NAME, newstudentpop.ADDRESS, newstudentpop.[P O Box], newstudentpop.ST_NAMEFROM newstudentpop LEFT JOIN StudentList ON newstudentpop.ADDRESS=StudentList.newstudentpop.AD DRESS WHERE (((StudentList.newstudentpop.ADDRESS) Is Null));

Don't know why the rest is not showing up. Probably is something with the data that I haven't found yet. Thanks for your help

[June7]

The second query did not include ST_LABEL/ST_NAME in the JOIN.

[jazzin]

The second query did not include ST_LABEL/ST_NAME in the JOIN.

What is the syntax to include ST_LABEL/ST_NAME as part of the query? Think I tried to include it at one time but it didn't work The second query is just the SQL generated by the FIND UNMATCHED QUERY tool. It only lets you select one field to do a join on.

[June7]

Open the query object in Design view and modify.

Or in SQL view type the join same as in first query.

[jazzin]

Open the query object in Design view and modify.Or in SQL view type the join same as in first query.

I ran the same query using ST_NAME and it returns 271 records.

[June7]

I just noticed the first query didn't use a JOIN clause but used WHERE to relate the tables. Suggest you use JOIN.

I always get confused by LEFT and RIGHT. The second query needs to be a join that: "Includes all records from Students and only those from Addresses that match".

Then try filter criteria of: Address.ADDRESS Is Null

[jazzin]

I just did a 'LEFT JOIN on Address using Address.ADDRESS = newstudentpop.ADDRESS AND Address.ST_LABEL=newstudentpop.ST_NAME WHERE Address.ADDRESS IS NULL' and I got back 481 records which is close to 477. Don't know if what I did is right but it is something different from what I was getting and it seemed to put me closer to the truth. Thank you for the help!