Last Date

D

dsc2bjn

I need to be able to find the latest date of multiple record which belong to
a parent record, but only if all the child records have an actual completion
date.

I have all my children records in a table linked to the parent record using
a [Report Number].


Any suggestions would be appreciated.
 
G

George Nicholson

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.
 
L

Larry Daugherty

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

HTH
 
C

Chris Anderson [MVP-VB]

George said:
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.
Click to expand...
Expanding on that, something like this:

SELECT *
FROM table1 T1
INNER JOIN (SELECT T.ID, MAX(T.EffDate) AS MaxDate FROM Table1 T GROUP
BY T.ID) T ON T1.ID = T.ID AND T1.EffDate = T.MaxDate

-ca
 
N

NetworkTrade

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...
 
K

Ken Sheridan

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
 
K

Ken Sheridan

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

dsc2bjn said:
I need to be able to find the latest date of multiple record which belong to
a parent record, but only if all the child records have an actual completion
date.

I have all my children records in a table linked to the parent record using
a [Report Number].


Any suggestions would be appreciated.
Click to expand...


dsc2bjn said:
I need to be able to find the latest date of multiple record which belong to
a parent record, but only if all the child records have an actual completion
date.

I have all my children records in a table linked to the parent record using
a [Report Number].


Any suggestions would be appreciated.
Click to expand...
 
K

Ken Sheridan

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

dsc2bjn said:
I need to be able to find the latest date of multiple record which belong to
a parent record, but only if all the child records have an actual completion
date.

I have all my children records in a table linked to the parent record using
a [Report Number].


Any suggestions would be appreciated.
Click to expand...


dsc2bjn said:
I need to be able to find the latest date of multiple record which belong to
a parent record, but only if all the child records have an actual completion
date.

I have all my children records in a table linked to the parent record using
a [Report Number].


Any suggestions would be appreciated.
Click to expand...
 
K

Ken Sheridan

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
 
D

Dale Fye

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
 
D

Dale Fye

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linked Form Filteration 4
Two Forms 2
No Field Edit capability 3
Seeking suggestion for handling parent/child situation on continous forms 6
Data entry problems with form based on Query 3
primary key - concatenated or autonumber? 6
Birthday query for many children in one record 1
Record Review Process 2

Top