Show records where ALL fields associated w/ one ID are not null

[Laura C.]

Hi,

I've been scanning posts trying to find a solution but can't seem to
translate the SQL statements I'm seeing for parallel situations into
something that will yield the results I want.

I have a table with the following fields:

tblNames_ID
tblTitles_ID
FirstElectedDate
PositionEndDate

A given name will generally be associated with several Titles, each of which
has a FirstElectedDate and PositionEndDate.

I'm trying to build a select query that will show me those records where ALL
the PositionEndDate fields associated with a given tblNames_ID have been
populated (i.e., PositionEndDate Is Not Null in every single instance for a
given tblNames_ID).

I don't want to see any records for tblNames_ID where some PositionEndDates
are null, others are not.

Can you help?

 

[KARL DEWEY]

Use two queries unless you know subqueries. First query selects all
tblNames_ID that have at least one PositionEndDate.
SELECT tblNames_ID
FROM YourTable
WHERE PositionEndDate Is Not Null;
Then left join YourTable in a query to the above query with criteria for
tblNames_ID in above query is null.

 

[Laura C.]

Thanks so much for your attention to my question Karl.

I've tried doing the left join between my table (tblMaster) and the query
but all rows are now coming up blank.

This is what the SQL looks like in the query with the LEFT JOIN:

SELECT qryPositionEndDateNotNull.tblNames_ID,
qryPositionEndDateNotNull.tblTitles_ID, qryPositionEndDateNotNull.[First
Elected Date], qryPositionEndDateNotNull.[Position End Date]
FROM tblMaster LEFT JOIN qryPositionEndDateNotNull ON tblMaster.tblNames_ID
= qryPositionEndDateNotNull.tblNames_ID
WHERE (((qryPositionEndDateNotNull.tblNames_ID) Is Null));

What am I doing wrong?

 

[John Spencer]

SELECT *
FROM tblNames
WHERE tblNames_ID NOT IN
(SELECT tblNames_ID
FROM tblNames
WHERE PositionEndDate is Null and tblNames_Id is not null)

That will be slow with LARGE datasets. It it is too slow, post back for
an alternate query that is more complex.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

You are selecting records from wrong object.
SELECT tblMaster.tblNames_ID, tblMaster.tblTitles_ID, tblMaster.[First
Elected Date], tblMaster.[Position End Date]
FROM tblMaster LEFT JOIN qryPositionEndDateNotNull ON tblMaster.tblNames_ID
= qryPositionEndDateNotNull.tblNames_ID
WHERE (((qryPositionEndDateNotNull.tblNames_ID) Is Null));

--
KARL DEWEY
Build a little - Test a little

Thanks so much for your attention to my question Karl.

I've tried doing the left join between my table (tblMaster) and the query
but all rows are now coming up blank.

This is what the SQL looks like in the query with the LEFT JOIN:

SELECT qryPositionEndDateNotNull.tblNames_ID,
qryPositionEndDateNotNull.tblTitles_ID, qryPositionEndDateNotNull.[First
Elected Date], qryPositionEndDateNotNull.[Position End Date]
FROM tblMaster LEFT JOIN qryPositionEndDateNotNull ON tblMaster.tblNames_ID
= qryPositionEndDateNotNull.tblNames_ID
WHERE (((qryPositionEndDateNotNull.tblNames_ID) Is Null));

What am I doing wrong?

Use two queries unless you know subqueries. First query selects all
tblNames_ID that have at least one PositionEndDate.
SELECT tblNames_ID
FROM YourTable
WHERE PositionEndDate Is Not Null;
Then left join YourTable in a query to the above query with criteria for
tblNames_ID in above query is null.

 

[Dale Fye]

How about:

SELECT tblNames_ID
FROM tblMaster
GROUP BY tblNames_ID
HAVING Sum(iif(isnull([PositionEndDate]), 1, 0)) = 0

This will list all of the people (tblNames_ID) in tblMaster where every
instance of [PositionEndDate] has a date entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Laura C.]

John, that totally worked. Thank you so much.

SELECT *
FROM tblNames
WHERE tblNames_ID NOT IN
(SELECT tblNames_ID
FROM tblNames
WHERE PositionEndDate is Null and tblNames_Id is not null)

That will be slow with LARGE datasets. It it is too slow, post back for
an alternate query that is more complex.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Laura C.]

Hi Dale,

Thanks for another suggestion. This works great -- spits out the names of
everyone for whom all positions have end dates.

John's solution was a little better suited to my issue, since it actually
gives me ALL of the records for each individual for whom all positions have
end dates. And since I am building a query with a view toward deleting those
records (did not want to delete without making sure I was culling the right
data!), more is better.

Thanks again!

How about:

SELECT tblNames_ID
FROM tblMaster
GROUP BY tblNames_ID
HAVING Sum(iif(isnull([PositionEndDate]), 1, 0)) = 0

This will list all of the people (tblNames_ID) in tblMaster where every
instance of [PositionEndDate] has a date entered.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi,

I've been scanning posts trying to find a solution but can't seem to
translate the SQL statements I'm seeing for parallel situations into
something that will yield the results I want.

I have a table with the following fields:

tblNames_ID
tblTitles_ID
FirstElectedDate
PositionEndDate

A given name will generally be associated with several Titles, each of which
has a FirstElectedDate and PositionEndDate.

I'm trying to build a select query that will show me those records where ALL
the PositionEndDate fields associated with a given tblNames_ID have been
populated (i.e., PositionEndDate Is Not Null in every single instance for a
given tblNames_ID).

I don't want to see any records for tblNames_ID where some PositionEndDates
are null, others are not.

Can you help?