DMax for dates in rpt group header returns DMax for entire records

[helios]

I created a small database that tracks about 40 clients and dates of certain
stages in a roughly annual review of their case. This DB consists of three
tables: a key table with the unique case or program number, which is linked
in a
one-to-many relationship with another table with fields for "action"
taken and the "date" of action. What management wants is a report on the
elapsed time between the
consecutive stages ("actions") of this annual review process, which has a
flexible start/end date.


These are my tables.fields

ProgramCode.ProgramNo, LastName, FirstName

which is linked one-to-many to:

TrackingDates.ProgramCode, Action, Date

The main four action types (from the value list) are "Review", "BMC
Approval", "Guardian Approval", and "HRC Approval"

the most recent review date is what I want to use for the start date of the
reviewing process, calculating elapsed time between each of the steps (1-4).

So I'm trying to use the DMax function in a textbox on a report grouped by
the "ProgramNo" field in a query that combines the two tables

=DMax("[Date]","TrackingDates","[Action] = 'Review'")

Since I don't need any dates before the last cycle, I could use DMax in all
these DateDiff calculations (for each of the [Action]s), but the DMax
statement above keeps returning the maximum date for the entire recordset.
It even behaves this way when I place it in the group header of the report
(grouped by ProgramNo). How do I get the most recent Review date value for
each ProgramNo?

Help!

 

[Rick Brandt]

I created a small database that tracks about 40 clients and dates of
certain stages in a roughly annual review of their case. This DB
consists of three tables: a key table with the unique case or program
number, which is linked in a
one-to-many relationship with another table with fields for "action"
taken and the "date" of action. What management wants is a report on
the elapsed time between the
consecutive stages ("actions") of this annual review process, which
has a flexible start/end date.


These are my tables.fields

ProgramCode.ProgramNo, LastName, FirstName

which is linked one-to-many to:

TrackingDates.ProgramCode, Action, Date

The main four action types (from the value list) are "Review", "BMC
Approval", "Guardian Approval", and "HRC Approval"

the most recent review date is what I want to use for the start date
of the reviewing process, calculating elapsed time between each of
the steps (1-4).

So I'm trying to use the DMax function in a textbox on a report
grouped by the "ProgramNo" field in a query that combines the two
tables

=DMax("[Date]","TrackingDates","[Action] = 'Review'")

Since I don't need any dates before the last cycle, I could use DMax
in all these DateDiff calculations (for each of the [Action]s), but
the DMax statement above keeps returning the maximum date for the
entire recordset. It even behaves this way when I place it in the
group header of the report (grouped by ProgramNo). How do I get the
most recent Review date value for each ProgramNo?

Help!

The regular aggregate functions (Sum, Min, Max, etc.), work differently in a
report depending on what report section you place them. The Domain
Aggregate functions work against the domain identified by their second
argument and the filter specified by their third argument regardless of
where you use them. You will need to pass more criteria into your third
argument (for a date range) if you want to limit the scope of the domain
sampled.