Unmatched Query makes column value disappear!

[GrandMasterTuck]

Hi folks, I have a weird problem... I have two queries that display employee records (one is based off my own employees table and one is built to display records that are still active from an older database table I imported into this database), and each has an EmployeeID value. In tblEmployees (mine), the EmployeeID is a text field (because some ID's have letters in them these days) and tblWorkers (the old table I imported) WorkerID is a number field. The query of tblWorkers is called qryOldEmployees and has a field with a value of Str([WorkerID]) which converts the old WorkerID number into a text string so I can compare it to my query.

qryOldEmployees has 1400 records. qryActiveEmployees has 1300 records, so there are 100 records missing in the new table. I want to write a query that shows me which records are missing.

What I want to do is show ONLY the records in qryOldEmployees that DO NOT MATCH any records in my query on tblEmployees (qryActiveEmployees). I just want to compare the EmployeeID field with the Str([WorkerID]) of the other table, and I want it to show me which one of those old WorkerID values don't exist as an EmployeeID value in the new table.

Here's the problem: When I use the wizard (or when I construct the query without it) to create an UNMATCHED query, the recordset never seems to show me my missing 100 records. I either get all 1400 records from OldEmployees, or I get ZERO records. And for some reason, the EmployeeID field (from qryActiveEmployees) shows null in the datasheet. No value at all. Even though when I vew qryActiveEmployees, every single record has a value in the EmployeeID field. As soon as I bring that query into the new UNMATCHED query window and try to hook it to qryOldEmployees, that column in the datasheet goes null for every record (this is not a permanent removal of the values therein, it just shows up blank in the query result datasheet for some maddening reason).

I just want my missing 100 records to show up. That's it. I don't even care if I only get the EmployeeID numbers of the missing records. If the EmployeeID number DOES exist as a WorkerID in qryOldEmployees, but there are NO records in qryActiveEmployees with that same value in the EmployeeID field, SHOW ME THE RECORD. If the value DOES exist, OMIT that record from the results.

I can't figure out what I'm doing wrong. Might this be an indication that one or more of the tables are corrupted in some way? I have followed directions to the letter for construction of the UNMATCHED query, and have even tried using the wizard and tried writing SQL. Nothing works for me.

EDIT: I found this question, posted by another user, and the answer just after it:

Hi all,

I am an absolute MS Access newbie. Excel has been able to provide me with all my needs so far but I was told that many of the things I do in Excel can be done easier and smarter in Access. So that is why my sudden interest.

Anyway about my question..

I have 2 tables, CODE1 and CODE2. Both tables have the same number of fields and same field names..Field1, Field2, Field3, Field4 and Field5.

I would like to run a query to show me all records in CODE2 that is not existing in CODE1. The basis of matching is all the fields i.e. Fields 1-5.

What I tried so far...

I tried to create a query using the FIND UNMATCHED QUERY WIZARD but I could not get it to work.

1. I clicked the INSERT menu, chose QUERY
2. Chose FIND UNMATCHED QUERY WIZARD
3. Under the "WHICH TABLE OR QUERY CONTAINS RECORDS YOU WANT...", I chose CODE2
4. Under the "WHICH TABLE OR QUERY CONTAINS THE RELATED RECORDS", I chose CODE1
5. Under which "WHAT PIECE OF INFORMATION IS IN BOTH TABLES, this is where I am confused because it seems I can only select 1 matching fields. So what I did I clicked each fields (1-5) in both cides and pressed the <=>

6. Under the "WHAT FIELDS DO YOU WANT TO SEE...", I chose all the fields.

When I viewed the results, it shows nothing although I know there are 2 records in CODE2 that are not in CODE1.

Pls help.

Thanks.

Answer:

from the database manager (I don't know what its really called) click Queries / New / Design View / OK
then a list of tables will come up
FIRST click on CODE2 and Add, then click on CODE1 and Add, then Close
now left click on field1 in CODE2 and drag it to field1 in CODE1
the fields will become connected by a black line
left click on the black line and make it bold (this may be difficult to click exactly on the line) once its bold, right click on it and select Properties
click the circle that says "Include ALL records from CODE2 and only those records from CODE1 where the joined fields are equal";
click OK; now the connecting line will have an arrow on the end of it pointing to CODE1

do that for all 5 fields

now drag the 5 fields from CODE2 down into the query builder grid
then drag the 5 fields from CODE1 down into the query builder grid (so it will be the 5 fields from CODE2 and then the 5 fields from CODE1)

now ONLY under the CODE1 fields put
is null
in the criteria row
so you're putting
is null
for ALL 5 fields of CODE1 and make sure all the is nulls are on the same criteria row

now press the red exclamation mark

And it doesn't work. When I do EXACTLY as this says to do, I get 1400 records, and every single record has all the fields from qryActiveEmployees showing null. And I know gosh-darned-well that NONE of those records are blank in qryActiveEmployees, because writing a query with ONLY qryActiveEmployees as the datasource and searching for nulls nets zero records. So qryActiveEmployees does NOT have null values in ANY records for ANY of the fields in my search, yet when I create my new UNMATCHED query with that left join on those fields, they ALL SHOW UP AS NULL... What the bloody hell??

 

[plog]

First, its possible none are missing. Perhaps you have duplicates in qryOldEmployees. It's even possible you have EmployeeID values in qryActiveEmployees that' aren't in qryOldEmployees. Here's what I would do:

1. Make a query to find all unique EmployeeID values in qryActiveEmployees, and count them. Call this query NewCount:

SELECT EmployeeID AS QAE_ID, COUNT(EmployeeID) AS QAE_Count FROM qryActiveEmployees GROUP BY EmployeeID;

2. Make a query to find all unique EmployeeID values in qryOldEmployees. You didn't provide the Str([WorkerID]) name, so let's call it X. Call this query OldCount:

SELECT X AS QOE_ID, COUNT(X) AS QOE_Count FROM qryOldEmployees GROUP BY X

3. Find duplicates. Run both queries, sort them by their count fields descending. Any records with a count greater than 1 has duplicates. Then find the last record of each query and see how many total unique values each produces. That's your accurate unique count, not the 1400/1300 you are going by now.

4. Find values in the OldCount not in NewCount:

SELECT QOE_ID FROM OldCount LEFT JOIN NewCount ON QOE_ID = QAE_ID WHERE QAE_ID IS NULL;

5. Find values in NewCount not in OldCount:

SELECT QAE_ID FROM NewCount LEFT JOIN OldCount ON QOE_ID = QAE_ID WHERE QOE_ID IS NULL;

Now you have covered your bases.

 

[GrandMasterTuck]

First, its possible none are missing. Perhaps you have duplicates in qryOldEmployees.

Oh my God, plog, you're a genius. As it turns out, that old table (which, by the way, has multiple problems other than this one) has just over 100 duplicate records. Your solution revealed this to me right away. And as soon as I read that first sentence of yours, I was like, "Now why didn't I think to check that?"

Derp. Anyway, you da mang. Thanks again!

One question though: why would those columns show up null in my query results? That still confuses me...

EDIT: Never mind. As it turns out, the Str() function, when converting from a number to a string, sticks a space at the beginning of the string for any positive number. That's why I always got no matches and those fields ended up null. I really need to learn the subtle ins and outs of these functions. All I had to do was modify the query to drop the stupid blank at the beginning of the field, and voila, matches galore. LOL