DMax in grouping level in report Access 2000

[Guest]

I am trying to use DMax in text boxes in a grouping level header in a report
and the report always returns the maximum dates for the entire query, not for
the domain of the grouping levels. Does the domain of the DMax function
override the grouping? Each group interval value shows up on the report, but
with the same maximum values of the dates I'm trying to track.

About me:
I don't know SQL, so I do most calculations in calculated fields in queries
or in calculated controls in reports.

About the database:
I am tracking intervals between steps in a process--I have three tables: one
with client ProgramCodes, another with names of the Actions which serves as a
control source list for [ActionType] in a table with TrackingDates.
I was going to use DMax to retrieve the maximum date in each type of action
(or step in the process, although there is no specific order for many of the
in-between steps). I was then going to use calculated controls to get the
interval between these steps and feed those into averages in the report
footer.
But I can't get the grouping on [ProgramCode.ProgramID] to make the DMax
expression to pull maximum dates for each client (with a unique ID that links
to the actions held in the TrackingDates table.

I'm a newbie, so I hope I've explained enough. Thanks in advance.
Sunshine Hall

 

[David Lloyd]

Are you using the third parameter of the DMax function to specify the group
level value for which you want the DMax value calculated? Generally, you
would set the third parameter to the value of the group level value. For
example:

DMax("[MyDateField]", "[MyQueryName]", "[MyGroupField]=[MyQueryGroupValue]")

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to use DMax in text boxes in a grouping level header in a report
and the report always returns the maximum dates for the entire query, not
for
the domain of the grouping levels. Does the domain of the DMax function
override the grouping? Each group interval value shows up on the report,
but
with the same maximum values of the dates I'm trying to track.

About me:
I don't know SQL, so I do most calculations in calculated fields in queries
or in calculated controls in reports.

About the database:
I am tracking intervals between steps in a process--I have three tables: one
with client ProgramCodes, another with names of the Actions which serves as
a
control source list for [ActionType] in a table with TrackingDates.
I was going to use DMax to retrieve the maximum date in each type of action
(or step in the process, although there is no specific order for many of the
in-between steps). I was then going to use calculated controls to get the
interval between these steps and feed those into averages in the report
footer.
But I can't get the grouping on [ProgramCode.ProgramID] to make the DMax
expression to pull maximum dates for each client (with a unique ID that
links
to the actions held in the TrackingDates table.

I'm a newbie, so I hope I've explained enough. Thanks in advance.
Sunshine Hall

 

[Guest]

I have one text box for each type of action in the group header (the report
is grouped on [ProgramCode], such as:

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


I think you're suggesting that I add criteria in the third parameter that
mimics the grouping of the report. But can I make this criteria dynamic to
show maximum dates for the specified action AND for each of the 30 to 40
unique ProgramCodes?

If I add criteria for a specific client's ProgramCode, I would have to do
this for twelve steps for each client, the DateDiff function between each
step, and change the report each time a client is added or dropped. I don't
want a huge report like that. Unless it can dynamically change with the
data, I might as well do this report manually...

Sunshine Hall

 

[David Lloyd]

The idea is to make the DMax function dynamic and NOT hard code specific
Actions or Program Codes. I do not understand all of the specifics of your
report, however, you can make the third parameter dynamic so that it reads
the values of the various groupings from the report, rather than hard coding
the values. For example:

DMax(DMax("[Date]","Maximum","[Action]=[Action] AND
[ProgramCode]=[ProgramCode]")

Using the DMax function this way allows you to use the values in the table
or query behind the report to dynamically change the DMax calculation as it
goes through the various groups. The above example assumes the table or
query behind the report has an [Action] field as well as a [ProgramCode]
field. The DMax function will then read these values from the record source
and apply them to the right side of the criteria equalities.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have one text box for each type of action in the group header (the report
is grouped on [ProgramCode], such as:

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


I think you're suggesting that I add criteria in the third parameter that
mimics the grouping of the report. But can I make this criteria dynamic to
show maximum dates for the specified action AND for each of the 30 to 40
unique ProgramCodes?

If I add criteria for a specific client's ProgramCode, I would have to do
this for twelve steps for each client, the DateDiff function between each
step, and change the report each time a client is added or dropped. I don't
want a huge report like that. Unless it can dynamically change with the
data, I might as well do this report manually...

Sunshine Hall

Are you using the third parameter of the DMax function to specify the
group
level value for which you want the DMax value calculated? Generally, you
would set the third parameter to the value of the group level value. For
example:

DMax("[MyDateField]", "[MyQueryName]",
"[MyGroupField]=[MyQueryGroupValue]")

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


I am trying to use DMax in text boxes in a grouping level header in a
report
and the report always returns the maximum dates for the entire query, not
for
the domain of the grouping levels. Does the domain of the DMax function
override the grouping? Each group interval value shows up on the report,
but
with the same maximum values of the dates I'm trying to track.

About me:
I don't know SQL, so I do most calculations in calculated fields in
queries
or in calculated controls in reports.

About the database:
I am tracking intervals between steps in a process--I have three tables:
one
with client ProgramCodes, another with names of the Actions which serves
as
a
control source list for [ActionType] in a table with TrackingDates.
I was going to use DMax to retrieve the maximum date in each type of
action
(or step in the process, although there is no specific order for many of
the
in-between steps). I was then going to use calculated controls to get the
interval between these steps and feed those into averages in the report
footer.
But I can't get the grouping on [ProgramCode.ProgramID] to make the DMax
expression to pull maximum dates for each client (with a unique ID that
links
to the actions held in the TrackingDates table.

I'm a newbie, so I hope I've explained enough. Thanks in advance.
Sunshine Hall