Query to find missing data

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

This query returns 497 records:
SELECT staffs.practice, staffs.name
FROM [min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice;

But this query only returns 436 records:
SELECT staffs.practice, staffs.name, [x confirmed].[month name]
FROM ([min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice) LEFT JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].[month name])="this emp ytds 0910" Or ([x
confirmed].[month name]) Is Null));

I expected to see all 497 records in the 2nd query - but with a 'blank' in
the field [month name] for the 61 records where there is no record in table
[x confirmed] with a [month name] value of "this emp ytds 0910". What did I
do wrong.

Oh - and I do know about the dangers of using 'name' as a field name ...
suffice it to say that I inherited this, the mdb works, and that field is
deeply entrenched in it!

Hope someone can help.
Many thanks
Leslie Isaacs
 
Put square brackets [ ] around Name in the query and that should prevent any
problems.

What happens when you remove the Where clause from the second query?

The way I read it, if there is something besides "this emp ytds 0910" or
null in the table, then those records won't be returned.
 
You have negated the LEFT JOIN by applying criteria against the field in x
confirmed. I suspect that you are going to need to nest queries to get the
desired results. You could use a subquery except your table and field names
contain spaces.

First query (saved as qConfirmedForMonth)
SELECT *
FROM [X Confirmed]
WHERE [x confirmed].[month name]="this emp ytds 0910"

NOW you can use that in your existing query in place of the table
[x Confirmed]

SELECT staffs.practice, staffs.name, [qConfirmedForMonth].[month name]
FROM ([min mth nos] INNER JOIN staffs
ON [min mth nos].practice =staffs.practice)
LEFT JOIN [qConfirmedForMonth]
ON staffs.name = [qConfirmedForMonth].name

Your query would work IF there were at most one record (or no record) in the
table x confirmed for each record in the staffs table. That is apparently not
the case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Hello Jerry

Many thanks for your reply.
You are right - I needed to use a 'pre-query', which John has kindly
supplied.

Thanks again
Les


Jerry Whittle said:
Put square brackets [ ] around Name in the query and that should prevent any
problems.

What happens when you remove the Where clause from the second query?

The way I read it, if there is something besides "this emp ytds 0910" or
null in the table, then those records won't be returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


PayeDoc said:
Hello All

This query returns 497 records:
SELECT staffs.practice, staffs.name
FROM [min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice;

But this query only returns 436 records:
SELECT staffs.practice, staffs.name, [x confirmed].[month name]
FROM ([min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice) LEFT JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].[month name])="this emp ytds 0910" Or ([x
confirmed].[month name]) Is Null));

I expected to see all 497 records in the 2nd query - but with a 'blank' in
the field [month name] for the 61 records where there is no record in table
[x confirmed] with a [month name] value of "this emp ytds 0910". What did I
do wrong.

Oh - and I do know about the dangers of using 'name' as a field name ...
suffice it to say that I inherited this, the mdb works, and that field is
deeply entrenched in it!

Hope someone can help.
Many thanks
Leslie Isaacs


.
Click to expand...
Click to expand...
 
John

Many thanks for your reply.
I get it now (I think!), and your 2-query approach works perfectly!

Thanks again
Les


John Spencer said:
You have negated the LEFT JOIN by applying criteria against the field in x
confirmed. I suspect that you are going to need to nest queries to get the
desired results. You could use a subquery except your table and field names
contain spaces.

First query (saved as qConfirmedForMonth)
SELECT *
FROM [X Confirmed]
WHERE [x confirmed].[month name]="this emp ytds 0910"

NOW you can use that in your existing query in place of the table
[x Confirmed]

SELECT staffs.practice, staffs.name, [qConfirmedForMonth].[month name]
FROM ([min mth nos] INNER JOIN staffs
ON [min mth nos].practice =staffs.practice)
LEFT JOIN [qConfirmedForMonth]
ON staffs.name = [qConfirmedForMonth].name

Your query would work IF there were at most one record (or no record) in the
table x confirmed for each record in the staffs table. That is apparently not
the case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello All

This query returns 497 records:
SELECT staffs.practice, staffs.name
FROM [min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice;

But this query only returns 436 records:
SELECT staffs.practice, staffs.name, [x confirmed].[month name]
FROM ([min mth nos] INNER JOIN staffs ON [min mth nos].practice =
staffs.practice) LEFT JOIN [x confirmed] ON staffs.name = [x confirmed].name
WHERE ((([x confirmed].[month name])="this emp ytds 0910" Or ([x
confirmed].[month name]) Is Null));

I expected to see all 497 records in the 2nd query - but with a 'blank' in
the field [month name] for the 61 records where there is no record in table
[x confirmed] with a [month name] value of "this emp ytds 0910". What did I
do wrong.

Oh - and I do know about the dangers of using 'name' as a field name ...
suffice it to say that I inherited this, the mdb works, and that field is
deeply entrenched in it!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Back
Top