Counting records if all associated records have a date

[Guest]

Good morning

I have to build a query but am having some trouble and was hoping that someone might be able to guide me a bit
I have a main table ("Modification Tbl") that contains modification general information and a secondary table ("Modification Impacted Dwgs Tbl") that has a 1-many relationship, it contains all the drawings that are impacted by each mod listed in the main table. For each drawing in the secondary table there is a field to enter the completed date

I need to create a query that will count the number of modifications that are closed, thus, that all the impacted drawings associated to the modification have a completed date

Could someone explain how I would go about creating such a query

Thanking you in advance for your help

Daniel P

 

[Gerald Stanley]

Try this untested aircode

SELECT COUNT(Q1.foreignKeyId)
FROM (SELECT foreignKeyId , Count(1) As TotalCount FROM
[Modification Impacted Dwgs Tbl] GROUP BY foreignKeyId) Q1,
(SELECT foreignKeyId, Count(1) As CompletedCount FROM
[Modification Impacted Dwgs Tbl] WHERE NOT [completedDate]
IS NULL GROUP BY foreignKeyId) Q2
WHERE Q1.foreignKeyId = Q2.foreignKeyId AND Q1.TotalCount =
Q2.CompletedCount

The foreignKeyId is the column with the key to the
Modification Tbl.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Good morning,

I have to build a query but am having some trouble and was

hoping that someone might be able to guide me a bit!

I have a main table ("Modification Tbl") that contains

modification general information and a secondary table
("Modification Impacted Dwgs Tbl") that has a 1-many
relationship, it contains all the drawings that are
impacted by each mod listed in the main table. For each
drawing in the secondary table there is a field to enter
the completed date.

I need to create a query that will count the number of

modifications that are closed, thus, that all the impacted
drawings associated to the modification have a completed date.