Query of changes to parent or child

[Jeff]

I have a parent table with 3 children tables. Each of the child
records has an activity date on it to represent a change to the
record.

The client wants a query where any parent record where 1 or more of
the children have an activity date within the date parameters would be
included.

I was hoping for some pointers on the most efficient way to do this.

Thanks!

Jeff

 

[Allen Browne]

Use a subquery to meet the requirement that the Max() of the Date/Time field
is after the parameter date.

If subqueries are new, see:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

This kind of thing:

PARAMETERS SinceWhen DateTime;
SELECT *
FROM Table1
WHERE (SELECT Max(MyDate) AS MaxOfMyDate
FROM Table2
WHERE Table2.ForeignID = Table1.ID) > [SinceWhen]
OR (SELECT Max(MyDate) AS MaxOfMyDate
FROM Table3
WHERE Table3.ForeignID = Table1.ID) > [SinceWhen]
OR (SELECT Max(MyDate) AS MaxOfMyDate
FROM Table4
WHERE Table4.ForeignID = Table1.ID) > [SinceWhen];