Show only Latest date in reuslts of a query

[Dee]

Hi
I have a parent table holding review dates and a child table with audit
trail entries made with dates.
I want to extract all documents with a review date within a range (select
query doing this) and would also like to see only the latest audit trail
entry in the the child table.
At the moment my select query shows a document with all audit trail entries.

Any suggestions on how I can get only the latest audit trail entry would be
deeply appreciated.

Thanks Dee

 

[KARL DEWEY]

Select query left join to ---
Group by query with these fields --
Audit_Identifier Max(audit trail entry date) left join both
fields --
Child table fields

 

[Ken Sheridan]

Dee:

You can restrict the results by means of a correlated subquery which
identifies the latest (MAX) review date for the current value of the columns
which join the tables, e.g.

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM Parent INNER JOIN Child AS C1
ON Parent.MyID = C1.MyID
WHERE C1.ReviewDate =
(SELECT MAX(ReviewDate)
FROM Child AS C2
WHERE C2.MyID = C1.MyID
AND ReviewDate >= [Enter start date:]
AND ReviewDate < [Enter end date:] +1);

A couple of things to note:

1. The two instances of the table Child are given aliases C1 and C2 to
differentiate them.

2. If date/time parameters are used, as in the above example, it’s a good
idea to declare them as such. Otherwise they could be interpreted as
arithmetic expressions if entered in short date format and give the wrong
results.

3. The method for determining the date range on the basis of all rows on or
after the start date and before the day following the end date ensures that
any rows with dates on the final day of the range which might inadvertently
contain a non-zero time of day (which can easily happen if steps have not
been taken in the table definition to prevent this) are returned, whereas a
BETWEEN….AND operation would exclude such rows.

4. In the above example MyID is the name of the primary key of Parent and
the corresponding foreign key column in Child.

5. I've assumed that by " latest audit trail entry" you mean the latest
review date within the specified range, even if there should be a later
review date outside of the range.

Ken Sheridan
Stafford, England

 

[Dee]

Thanks Karl

Select query left join to ---
Group by query with these fields --
Audit_Identifier Max(audit trail entry date) left join both
fields --
Child table fields

 

[Dee]

This definately worked though I am not very good with SQL so took me very
long to understand but great stuff - thanks a lot Ken

Dee:

You can restrict the results by means of a correlated subquery which
identifies the latest (MAX) review date for the current value of the columns
which join the tables, e.g.

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM Parent INNER JOIN Child AS C1
ON Parent.MyID = C1.MyID
WHERE C1.ReviewDate =
(SELECT MAX(ReviewDate)
FROM Child AS C2
WHERE C2.MyID = C1.MyID
AND ReviewDate >= [Enter start date:]
AND ReviewDate < [Enter end date:] +1);

A couple of things to note:

1. The two instances of the table Child are given aliases C1 and C2 to
differentiate them.

2. If date/time parameters are used, as in the above example, it’s a good
idea to declare them as such. Otherwise they could be interpreted as
arithmetic expressions if entered in short date format and give the wrong
results.

3. The method for determining the date range on the basis of all rows on or
after the start date and before the day following the end date ensures that
any rows with dates on the final day of the range which might inadvertently
contain a non-zero time of day (which can easily happen if steps have not
been taken in the table definition to prevent this) are returned, whereas a
BETWEEN….AND operation would exclude such rows.

4. In the above example MyID is the name of the primary key of Parent and
the corresponding foreign key column in Child.

5. I've assumed that by " latest audit trail entry" you mean the latest
review date within the specified range, even if there should be a later
review date outside of the range.

Ken Sheridan
Stafford, England

Hi
I have a parent table holding review dates and a child table with audit
trail entries made with dates.
I want to extract all documents with a review date within a range (select
query doing this) and would also like to see only the latest audit trail
entry in the the child table.
At the moment my select query shows a document with all audit trail entries.

Any suggestions on how I can get only the latest audit trail entry would be
deeply appreciated.

Thanks Dee