Query to find missing data

[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

 

[Jerry Whittle]

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.

 

[John Spencer]

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

 

[PayeDoc]

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

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.

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


.

 

[PayeDoc]

John

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

Thanks again
Les

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