Query 1 without matching records in Query 2

[WDSnews]

I have two queries in which I expect to see a similar list of items yet one
result returns several more records than the seconds. I thought I could use
the Query Wizard's 'Find Unmatched Query Wizard' to make a list of
additional records. However the results of the wizard are always an empty
table. Thinking I did it wrong, I tried it again on two other queries in
which I knew there would be a difference and it also returned zero results.
So I have two questions.

Are queries not a legitimate source for an Unmatched (Is Null) operation?

How can I accomplish my goal?

 

[Allen Browne]

Switch the query to SQL View, and paste the SQL statement here.

You can use queries as a source for unmatched an query.

 

[WDSnews]

Allen, thank you for your help.

I pasted the SQL query below. It was built using the "unmatched" wizard.
The "All Students" query returns 381 results. The "Consolidated" query
returns 345.

Switch the query to SQL View, and paste the SQL statement here.

SELECT [Student All Query].ID, [Student All Query].[Last Name], [Student All
Query].[First Name]
FROM [Student All Query] LEFT JOIN [Consolidated Student File Format - ADM
Fall 2008] ON [Student All Query].[DistStdntID] = [Consolidated Student File
Format - ADM Fall 2008].[DistStdntID]
WHERE ((([Consolidated Student File Format - ADM Fall 2008].DistStdntID) Is
Null));

 

[Allen Browne]

That approach (a frustrated outer join) should work.

If it doesn't the problem may be deeper down, e.g. in one of the source
queries. Or, perhaps there are values that are not actually null (e.g. a
zero-length string.) Or, it could be one of the JET bugs (though nothing is
obvious in that simple a query.)

Not sure what else to suggest here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you for your help.

I pasted the SQL query below. It was built using the "unmatched" wizard.
The "All Students" query returns 381 results. The "Consolidated" query
returns 345.

Switch the query to SQL View, and paste the SQL statement here.

SELECT [Student All Query].ID, [Student All Query].[Last Name], [Student
All Query].[First Name]
FROM [Student All Query] LEFT JOIN [Consolidated Student File Format - ADM
Fall 2008] ON [Student All Query].[DistStdntID] = [Consolidated Student
File Format - ADM Fall 2008].[DistStdntID]
WHERE ((([Consolidated Student File Format - ADM Fall 2008].DistStdntID)
Is Null));

You can use queries as a source for unmatched an query.

 

[WDSnews]

Allen,

the queries are joined on DistStdntID, which is an 8 character left padded
text version of ID. ID is a long int.

Does that raise any issues?

That approach (a frustrated outer join) should work.

If it doesn't the problem may be deeper down, e.g. in one of the source
queries. Or, perhaps there are values that are not actually null (e.g. a
zero-length string.) Or, it could be one of the JET bugs (though nothing
is obvious in that simple a query.)

Not sure what else to suggest here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thank you for your help.

I pasted the SQL query below. It was built using the "unmatched" wizard.
The "All Students" query returns 381 results. The "Consolidated" query
returns 345.

Switch the query to SQL View, and paste the SQL statement here.

SELECT [Student All Query].ID, [Student All Query].[Last Name], [Student
All Query].[First Name]
FROM [Student All Query] LEFT JOIN [Consolidated Student File Format -
ADM Fall 2008] ON [Student All Query].[DistStdntID] = [Consolidated
Student File Format - ADM Fall 2008].[DistStdntID]
WHERE ((([Consolidated Student File Format - ADM Fall 2008].DistStdntID)
Is Null));

You can use queries as a source for unmatched an query.

I have two queries in which I expect to see a similar list of items yet
one result returns several more records than the seconds. I thought I
could use the Query Wizard's 'Find Unmatched Query Wizard' to make a
list of additional records. However the results of the wizard are
always an empty table. Thinking I did it wrong, I tried it again on two
other queries in which I knew there would be a difference and it also
returned zero results. So I have two questions.

Are queries not a legitimate source for an Unmatched (Is Null)
operation?

How can I accomplish my goal?

 

[Allen Browne]

Yes: It may well depend on how you are padding as to whether the result
really is null or not. Perhaps you can do some testing for Is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

the queries are joined on DistStdntID, which is an 8 character left padded
text version of ID. ID is a long int.

Does that raise any issues?

That approach (a frustrated outer join) should work.

If it doesn't the problem may be deeper down, e.g. in one of the source
queries. Or, perhaps there are values that are not actually null (e.g. a
zero-length string.) Or, it could be one of the JET bugs (though nothing
is obvious in that simple a query.)

Not sure what else to suggest here.

Allen, thank you for your help.

I pasted the SQL query below. It was built using the "unmatched"
wizard. The "All Students" query returns 381 results. The
"Consolidated" query returns 345.


Switch the query to SQL View, and paste the SQL statement here.

SELECT [Student All Query].ID, [Student All Query].[Last Name], [Student
All Query].[First Name]
FROM [Student All Query] LEFT JOIN [Consolidated Student File Format -
ADM Fall 2008] ON [Student All Query].[DistStdntID] = [Consolidated
Student File Format - ADM Fall 2008].[DistStdntID]
WHERE ((([Consolidated Student File Format - ADM Fall 2008].DistStdntID)
Is Null));



You can use queries as a source for unmatched an query.

I have two queries in which I expect to see a similar list of items yet
one result returns several more records than the seconds. I thought I
could use the Query Wizard's 'Find Unmatched Query Wizard' to make a
list of additional records. However the results of the wizard are
always an empty table. Thinking I did it wrong, I tried it again on
two other queries in which I knew there would be a difference and it
also returned zero results. So I have two questions.

Are queries not a legitimate source for an Unmatched (Is Null)
operation?

How can I accomplish my goal?