Create a Select query that, for each parent recordID, gives you a count of
all children, a count of completion dates and the Max date of related
children.

Create a 2nd query that references the first query, selecting parent
recordID, maxDate WHERE ChildCount = CompletedCount.

Use the "TOP n" predicate to return only the number you want of dates
which are sorted DESCending and that are not Null.

HTH

Presuming you have the Parent table query already returning the correct
parent records - and what you next need to do is narrow that down to the most
recent date only;    Try using the 'Top X' feature in query design....the
drop down default is Top 10 or Top 5...but you can put your cursor in there
and change to Top 1....

This will give you the greatest, or most recent date record...

How you do it depends on whether you want to return columns from the
referencing (child) table other than the completion date.  If you only want
columns from the referenced (parent) table and the date from the referencing
table then a simple aggregating query is all that's needed, e.g.

SELECT [SomeField], [SomeOtherField],
MAX ([Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable]
ON [FirstTable].[Report Number] = [SecondTable].[Report Number]
WHERE [Completion Date] IS NOT NULL
GROUP BY [SomeField], [SomeOtherField];

If you want columns other than CompletionDate from the referenced table
(lets assume a column [PhaseNumber] from the second table) then you need to
use a subquery to identify the latest date:

SELECT [SomeField], [SomeOtherField],
[PhaseNumber], [Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable] AS ST1
ON [FirstTable].[Report Number] =ST1.[Report Number]
WHERE ST1.[Completion Date] =
   (SELECT MAX([Completion Date])
     FROM [SecondTable] AS ST2
     WHERE ST2.[Report Number] = ST1.[Report Number]);

Note how in the second query the different instances of the second table are
distinguished by aliases ST1 and ST2, allowing the subquery to be correlated
with the outer query.  Its not necessary to test for IS NOT NULL in the
subquery, as if all the completion date columns for a particular report
number are NULL the subquery will return NULL.  ST2.[Completion Date] =  NULL
will evaluate to NULL, never to TRUE as comparing anything with NULL, even
NULL, always results in NULL.  This is because NULL is an absence of a value,
an unknown, so comparing anything with an unknown must result in another
unknown.

Ken Sheridan
Stafford, England

How you do it depends on whether you want to return columns from the
referencing (child) table other than the completion date.  If you only want
columns from the refernced (parent) table and the date from the referencing
table then a simple aggregating query is all that's needed, e.g.

SELECT [SomeField], [SomeOtherField],
MAX ([Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable]
ON [FirstTable].[Report Number] = [SecondTable].[Report Number]
WHERE [Completion Date] IS NOT NULL
GROUP BY [SomeField], [SomeOtherField];

If you want columns other than CompletionDate from the referenced table
(lets assume a column [PhaseNumber] from the second table) then you need to
use a subquery to identify the latest date:

SELECT [SomeField], [SomeOtherField]
[PhaseNumber], [Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable] AS ST1
ON [FirstTable].[Report Number] =ST1.[Report Number]
WHERE ST1.[Completion Date] =
   (SELECT MAX([Completion Date])
     FROM [SecondTable] AS ST2
     WHERE ST2.[Report Number] = ST1.[Report Number]);

Note how in the second query the different instances of the second table are
distinguished by aliases ST1 and ST2, allowing the subquery to be correlated
with the outer query.  Its not necessary to test for IS NOT NULL in the
subquery, as if all the completion date columns for a particular report
number are NULL the subquery will return NULL.  ST2.[Completion Date] =  NULL
will evaluate to NULL, never to TRUE as comparing anything with NULL, even
NULL, always results in NULL.  This is because NULL is an absence of a value,
an unknown, so comparing anything with an unknown must result in another
unknown.

Ken Sheridan
Stafford, England

How you do it depends on whether you want to return columns from the
referencing (child) table other than the completion date.  If you only want
columns from the refernced (parent) table and the date from the referencing
table then a simple aggregating query is all that's needed, e.g.

SELECT [SomeField], [SomeOtherField],
MAX ([Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable]
ON [FirstTable].[Report Number] = [SecondTable].[Report Number]
WHERE [Completion Date] IS NOT NULL
GROUP BY [SomeField], [SomeOtherField];

If you want columns other than CompletionDate from the referenced table
(lets assume a column [PhaseNumber] from the second table) then you need to
use a subquery to identify the latest date:

SELECT [SomeField], [SomeOtherField]
[PhaseNumber], [Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable] AS ST1
ON [FirstTable].[Report Number] =ST1.[Report Number]
WHERE ST1.[Completion Date] =
   (SELECT MAX([Completion Date])
     FROM [SecondTable] AS ST2
     WHERE ST2.[Report Number] = ST1.[Report Number]);

Note how in the second query the different instances of the second table are
distinguished by aliases ST1 and ST2, allowing the subquery to be correlated
with the outer query.  Its not necessary to test for IS NOT NULL in the
subquery, as if all the completion date columns for a particular report
number are NULL the subquery will return NULL.  ST2.[Completion Date] =  NULL
will evaluate to NULL, never to TRUE as comparing anything with NULL, even
NULL, always results in NULL.  This is because NULL is an absence of a value,
an unknown, so comparing anything with an unknown must result in another
unknown.

Ken Sheridan
Stafford, England

How you do it depends on whether you want to return columns from the
referencing (child) table other than the completion date.  If you only want
columns from the refernced (parent) table and the date from the referencing
table then a simple aggregating query is all that's needed, e.g.

SELECT [SomeField], [SomeOtherField],
MAX ([Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable]
ON [FirstTable].[Report Number] = [SecondTable].[Report Number]
WHERE [Completion Date] IS NOT NULL
GROUP BY [SomeField], [SomeOtherField];

If you want columns other than CompletionDate from the referenced table
(lets assume a column [PhaseNumber] from the second table) then you need to
use a subquery to identify the latest date:

SELECT [SomeField], [SomeOtherField]
[PhaseNumber], [Completion Date] AS [LatestDate]
FROM [FirstTable] INNER JOIN [SecondTable] AS ST1
ON [FirstTable].[Report Number] =ST1.[Report Number]
WHERE ST1.[Completion Date] =
   (SELECT MAX([Completion Date])
     FROM [SecondTable] AS ST2
     WHERE ST2.[Report Number] = ST1.[Report Number]);

Note how in the second query the different instances of the second table are
distinguished by aliases ST1 and ST2, allowing the subquery to be correlated
with the outer query.  Its not necessary to test for IS NOT NULL in the
subquery, as if all the completion date columns for a particular report
number are NULL the subquery will return NULL.  ST2.[Completion Date] =  NULL
will evaluate to NULL, never to TRUE as comparing anything with NULL, even
NULL, always results in NULL.  This is because NULL is an absence of a value,
an unknown, so comparing anything with an unknown must result in another
unknown.

Ken Sheridan
Stafford, England

What do you want to do if all of the child recods don't have a completion date?

To get the latest date entered for a particular [Report Number], where the
completion date has a value then, try something like:

SELECT [Report Number], Max([Completion Date]) as LatestDate
FROM yourTable
WHERE [Report Number] = 1
AND [Completion Date] is not NULL

HTH
Dale

What do you want to do if all of the records for a particular parent do not
have a completion date?

You might try something like the following.  This would give you the latest
non-null Completion date for each [Report Number].

SELECT [Report Number], Max([Completion Date]) as LatestDate
FROM yourTable
WHERE [Completion Date] IS NOT NULL
GROUP BY [Report Number]

If you only want this value for a single [Report Number] then add a criteria
to the WHERE clause for that [Report Number].

HTH
Dale