Unmatched query not working

A

Ann Scharpf

I have two data tables from separate data dumps.

eBizHoursFrom9-19Dump (9,548 records)
eBizHoursRecorded (9,493 records)

I am trying to use an unmatched query to find the records from 9-19 that are
missing in the other data table. I thought I was following instructions
properly but my query results include ALL 9.548 records instead of just the
missing ones. I included both tables in the query, linked each field to its
counterpart, set the join for all records from 9-19 and only matching for
HoursRecorded. Added criteria for "is null" for the Employee field in the
Hours recorded.

Can anyone tell me what I have done wrong in this query? Here is my SQL.
Thanks very much for your help.

*******
SELECT [eBizHoursFrom9-19Dump].Employee, [eBizHoursFrom9-19Dump].HourType,
[eBizHoursFrom9-19Dump].Revision, [eBizHoursFrom9-19Dump].EmpNum,
[eBizHoursFrom9-19Dump].PPEdate, [eBizHoursFrom9-19Dump].AcctTemplate,
[eBizHoursFrom9-19Dump].Project, [eBizHoursFrom9-19Dump].Vendor,
[eBizHoursFrom9-19Dump].Function, [eBizHoursFrom9-19Dump].Task,
[eBizHoursFrom9-19Dump].NightHours, [eBizHoursFrom9-19Dump].TotalHours,
[eBizHoursFrom9-19Dump].OrgCode
FROM [eBizHoursFrom9-19Dump] LEFT JOIN eBizHoursRecorded ON
([eBizHoursFrom9-19Dump].OrgCode = eBizHoursRecorded.OrgCode) AND
([eBizHoursFrom9-19Dump].TotalHours = eBizHoursRecorded.TotalHours) AND
([eBizHoursFrom9-19Dump].NightHours = eBizHoursRecorded.NightHours) AND
([eBizHoursFrom9-19Dump].Task = eBizHoursRecorded.Task) AND
([eBizHoursFrom9-19Dump].Function = eBizHoursRecorded.Function) AND
([eBizHoursFrom9-19Dump].Vendor = eBizHoursRecorded.Vendor) AND
([eBizHoursFrom9-19Dump].Project = eBizHoursRecorded.Project) AND
([eBizHoursFrom9-19Dump].AcctTemplate = eBizHoursRecorded.AcctTemplate) AND
([eBizHoursFrom9-19Dump].PPEdate = eBizHoursRecorded.PPEdate) AND
([eBizHoursFrom9-19Dump].EmpNum = eBizHoursRecorded.EmpNum) AND
([eBizHoursFrom9-19Dump].Revision = eBizHoursRecorded.Revision) AND
([eBizHoursFrom9-19Dump].HourType = eBizHoursRecorded.HourType) AND
([eBizHoursFrom9-19Dump].Employee = eBizHoursRecorded.Employee)
WHERE (((eBizHoursRecorded.Employee) Is Null));

*******


Ann Scharpf
 
K

Ken Snell \(MVP\)

Your SQL statement looks ok -- but it assumes that every one of the fields
from the [eBizHoursFrom9-19Dump] table that you list in the ON clause has a
value that is non-Null, and that all fields in the [eBizHoursRecorded] table
that are "matched" to the first table's fields in the ON clause also have a
non-Null value. If any field has a Null value, the ON clause will not match,
and the first table's record will be returned for that "non-match".
 
A

Ann Scharpf

Those are true statements. NONE of the fields in either data table is ever
null.

I am looking for records that don't have a match for every one of the fields
in the record. Is there some other way I should do this?

Thanks.
--
Ann Scharpf


Ken Snell (MVP) said:
Your SQL statement looks ok -- but it assumes that every one of the fields
from the [eBizHoursFrom9-19Dump] table that you list in the ON clause has a
value that is non-Null, and that all fields in the [eBizHoursRecorded] table
that are "matched" to the first table's fields in the ON clause also have a
non-Null value. If any field has a Null value, the ON clause will not match,
and the first table's record will be returned for that "non-match".

--

Ken Snell
<MS ACCESS MVP>




Ann Scharpf said:
I have two data tables from separate data dumps.

eBizHoursFrom9-19Dump (9,548 records)
eBizHoursRecorded (9,493 records)

I am trying to use an unmatched query to find the records from 9-19 that
are
missing in the other data table. I thought I was following instructions
properly but my query results include ALL 9.548 records instead of just
the
missing ones. I included both tables in the query, linked each field to
its
counterpart, set the join for all records from 9-19 and only matching for
HoursRecorded. Added criteria for "is null" for the Employee field in the
Hours recorded.

Can anyone tell me what I have done wrong in this query? Here is my SQL.
Thanks very much for your help.

*******
SELECT [eBizHoursFrom9-19Dump].Employee, [eBizHoursFrom9-19Dump].HourType,
[eBizHoursFrom9-19Dump].Revision, [eBizHoursFrom9-19Dump].EmpNum,
[eBizHoursFrom9-19Dump].PPEdate, [eBizHoursFrom9-19Dump].AcctTemplate,
[eBizHoursFrom9-19Dump].Project, [eBizHoursFrom9-19Dump].Vendor,
[eBizHoursFrom9-19Dump].Function, [eBizHoursFrom9-19Dump].Task,
[eBizHoursFrom9-19Dump].NightHours, [eBizHoursFrom9-19Dump].TotalHours,
[eBizHoursFrom9-19Dump].OrgCode
FROM [eBizHoursFrom9-19Dump] LEFT JOIN eBizHoursRecorded ON
([eBizHoursFrom9-19Dump].OrgCode = eBizHoursRecorded.OrgCode) AND
([eBizHoursFrom9-19Dump].TotalHours = eBizHoursRecorded.TotalHours) AND
([eBizHoursFrom9-19Dump].NightHours = eBizHoursRecorded.NightHours) AND
([eBizHoursFrom9-19Dump].Task = eBizHoursRecorded.Task) AND
([eBizHoursFrom9-19Dump].Function = eBizHoursRecorded.Function) AND
([eBizHoursFrom9-19Dump].Vendor = eBizHoursRecorded.Vendor) AND
([eBizHoursFrom9-19Dump].Project = eBizHoursRecorded.Project) AND
([eBizHoursFrom9-19Dump].AcctTemplate = eBizHoursRecorded.AcctTemplate)
AND
([eBizHoursFrom9-19Dump].PPEdate = eBizHoursRecorded.PPEdate) AND
([eBizHoursFrom9-19Dump].EmpNum = eBizHoursRecorded.EmpNum) AND
([eBizHoursFrom9-19Dump].Revision = eBizHoursRecorded.Revision) AND
([eBizHoursFrom9-19Dump].HourType = eBizHoursRecorded.HourType) AND
([eBizHoursFrom9-19Dump].Employee = eBizHoursRecorded.Employee)
WHERE (((eBizHoursRecorded.Employee) Is Null));

*******


Ann Scharpf
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

The method that you're using is the correct one for finding orphaned records
or unmatched records. But, because the query is returning all records from
the first table, that says that something in at least one of the fields is
not matching between the two tables.

Null values are the first things for which I look.

The next thing is to ensure that the data types of the fields that are being
matched are the same, exactly the same, in both tables (exception:
AutoNumber is the same as Long Integer number type). Be sure that one field
is not text while the other is number, even though the contents appear to be
the same numeric characters.

If that doesn't yield an explanation, then start with a new query that joins
on just one field; ensure that it's working. Then add one field, and test.
Continue until you find a field that suddenly causes all records from the
first table to be returned by the query.
--

Ken Snell
<MS ACCESS MVP>




Ann Scharpf said:
Those are true statements. NONE of the fields in either data table is
ever
null.

I am looking for records that don't have a match for every one of the
fields
in the record. Is there some other way I should do this?

Thanks.
--
Ann Scharpf


Ken Snell (MVP) said:
Your SQL statement looks ok -- but it assumes that every one of the
fields
from the [eBizHoursFrom9-19Dump] table that you list in the ON clause has
a
value that is non-Null, and that all fields in the [eBizHoursRecorded]
table
that are "matched" to the first table's fields in the ON clause also have
a
non-Null value. If any field has a Null value, the ON clause will not
match,
and the first table's record will be returned for that "non-match".

--

Ken Snell
<MS ACCESS MVP>




Ann Scharpf said:
I have two data tables from separate data dumps.

eBizHoursFrom9-19Dump (9,548 records)
eBizHoursRecorded (9,493 records)

I am trying to use an unmatched query to find the records from 9-19
that
are
missing in the other data table. I thought I was following
instructions
properly but my query results include ALL 9.548 records instead of just
the
missing ones. I included both tables in the query, linked each field
to
its
counterpart, set the join for all records from 9-19 and only matching
for
HoursRecorded. Added criteria for "is null" for the Employee field in
the
Hours recorded.

Can anyone tell me what I have done wrong in this query? Here is my
SQL.
Thanks very much for your help.

*******
SELECT [eBizHoursFrom9-19Dump].Employee,
[eBizHoursFrom9-19Dump].HourType,
[eBizHoursFrom9-19Dump].Revision, [eBizHoursFrom9-19Dump].EmpNum,
[eBizHoursFrom9-19Dump].PPEdate, [eBizHoursFrom9-19Dump].AcctTemplate,
[eBizHoursFrom9-19Dump].Project, [eBizHoursFrom9-19Dump].Vendor,
[eBizHoursFrom9-19Dump].Function, [eBizHoursFrom9-19Dump].Task,
[eBizHoursFrom9-19Dump].NightHours, [eBizHoursFrom9-19Dump].TotalHours,
[eBizHoursFrom9-19Dump].OrgCode
FROM [eBizHoursFrom9-19Dump] LEFT JOIN eBizHoursRecorded ON
([eBizHoursFrom9-19Dump].OrgCode = eBizHoursRecorded.OrgCode) AND
([eBizHoursFrom9-19Dump].TotalHours = eBizHoursRecorded.TotalHours) AND
([eBizHoursFrom9-19Dump].NightHours = eBizHoursRecorded.NightHours) AND
([eBizHoursFrom9-19Dump].Task = eBizHoursRecorded.Task) AND
([eBizHoursFrom9-19Dump].Function = eBizHoursRecorded.Function) AND
([eBizHoursFrom9-19Dump].Vendor = eBizHoursRecorded.Vendor) AND
([eBizHoursFrom9-19Dump].Project = eBizHoursRecorded.Project) AND
([eBizHoursFrom9-19Dump].AcctTemplate = eBizHoursRecorded.AcctTemplate)
AND
([eBizHoursFrom9-19Dump].PPEdate = eBizHoursRecorded.PPEdate) AND
([eBizHoursFrom9-19Dump].EmpNum = eBizHoursRecorded.EmpNum) AND
([eBizHoursFrom9-19Dump].Revision = eBizHoursRecorded.Revision) AND
([eBizHoursFrom9-19Dump].HourType = eBizHoursRecorded.HourType) AND
([eBizHoursFrom9-19Dump].Employee = eBizHoursRecorded.Employee)
WHERE (((eBizHoursRecorded.Employee) Is Null));

*******


Ann Scharpf
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Ann:

An alternative would be to use a subquery:

SELECT *
FROM [eBizHoursFrom9-19Dump]
WHERE NOT EXISTS
(SELECT *
FROM eBizHoursRecorded
WHERE
eBizHoursRecorded.OrgCode = [eBizHoursFrom9-19Dump].OrgCode AND
eBizHoursRecorded.TotalHours = [eBizHoursFrom9-19Dump].TotalHours AND
eBizHoursRecorded.NightHours = [eBizHoursFrom9-19Dump].NightHours AND
eBizHoursRecorded.Task = [eBizHoursFrom9-19Dump].Task AND
eBizHoursRecorded.Function = [eBizHoursFrom9-19Dump].Function AND
eBizHoursRecorded.Vendor = [eBizHoursFrom9-19Dump].Vendor AND
eBizHoursRecorded.Project = [eBizHoursFrom9-19Dump].Project AND
eBizHoursRecorded.AcctTemplate = [eBizHoursFrom9-19Dump].AcctTemplate AND
eBizHoursRecorded.PPEdate = [eBizHoursFrom9-19Dump].PPEdate AND
eBizHoursRecorded.EmpNum = [eBizHoursFrom9-19Dump].EmpNum AND
eBizHoursRecorded.Revision = [eBizHoursFrom9-19Dump].Revision AND
eBizHoursRecorded.HourType = [eBizHoursFrom9-19Dump].HourType AND
eBizHoursRecorded.Employee = [eBizHoursFrom9-19Dump].Employee);

Ken Sheridan
Stafford, England

Ann Scharpf said:
Those are true statements. NONE of the fields in either data table is ever
null.

I am looking for records that don't have a match for every one of the fields
in the record. Is there some other way I should do this?

Thanks.
--
Ann Scharpf


Ken Snell MVP said:
Your SQL statement looks ok -- but it assumes that every one of the fields
from the [eBizHoursFrom9-19Dump] table that you list in the ON clause has a
value that is non-Null, and that all fields in the [eBizHoursRecorded] table
that are "matched" to the first table's fields in the ON clause also have a
non-Null value. If any field has a Null value, the ON clause will not match,
and the first table's record will be returned for that "non-match".

--

Ken Snell
<MS ACCESS MVP>




Ann Scharpf said:
I have two data tables from separate data dumps.

eBizHoursFrom9-19Dump 9,548 records
eBizHoursRecorded 9,493 records

I am trying to use an unmatched query to find the records from 9-19 that
are
missing in the other data table. I thought I was following instructions
properly but my query results include ALL 9.548 records instead of just
the
missing ones. I included both tables in the query, linked each field to
its
counterpart, set the join for all records from 9-19 and only matching for
HoursRecorded. Added criteria for "is null" for the Employee field in the
Hours recorded.

Can anyone tell me what I have done wrong in this query? Here is my SQL.
Thanks very much for your help.

*******
SELECT [eBizHoursFrom9-19Dump].Employee, [eBizHoursFrom9-19Dump].HourType,
[eBizHoursFrom9-19Dump].Revision, [eBizHoursFrom9-19Dump].EmpNum,
[eBizHoursFrom9-19Dump].PPEdate, [eBizHoursFrom9-19Dump].AcctTemplate,
[eBizHoursFrom9-19Dump].Project, [eBizHoursFrom9-19Dump].Vendor,
[eBizHoursFrom9-19Dump].Function, [eBizHoursFrom9-19Dump].Task,
[eBizHoursFrom9-19Dump].NightHours, [eBizHoursFrom9-19Dump].TotalHours,
[eBizHoursFrom9-19Dump].OrgCode
FROM [eBizHoursFrom9-19Dump] LEFT JOIN eBizHoursRecorded ON
[eBizHoursFrom9-19Dump].OrgCode AND
[eBizHoursFrom9-19Dump].TotalHours = eBizHoursRecorded.TotalHours AND
[eBizHoursFrom9-19Dump].NightHours = eBizHoursRecorded.NightHours AND
[eBizHoursFrom9-19Dump].Task = eBizHoursRecorded.Task AND
[eBizHoursFrom9-19Dump].Function = eBizHoursRecorded.Function AND
[eBizHoursFrom9-19Dump].Vendor = eBizHoursRecorded.Vendor AND
[eBizHoursFrom9-19Dump].Project = eBizHoursRecorded.Project AND
[eBizHoursFrom9-19Dump].AcctTemplate = eBizHoursRecorded.AcctTemplate
AND
[eBizHoursFrom9-19Dump].PPEdate = eBizHoursRecorded.PPEdate AND
[eBizHoursFrom9-19Dump].EmpNum = eBizHoursRecorded.EmpNum AND
[eBizHoursFrom9-19Dump].Revision = eBizHoursRecorded.Revision AND
[eBizHoursFrom9-19Dump].HourType = eBizHoursRecorded.HourType AND
[eBizHoursFrom9-19Dump].Employee = eBizHoursRecorded.Employee
WHERE eBizHoursRecorded.Employee Is Null;

*******


Ann Scharpf
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query changes field name to Expr1000 2
unmatched query not working 2

Top