complex elapsed time calculation.

[Guest]

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 number, which is linked in a
one-to-many relationship with two other tables--one with fields for "action"
taken and the "date" of action, and the other to provide an area for
commentary (a memo box and iD field). The memo table is not essential.
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.
So...how do I calculate elapsed time (days) between the most recent
consecutive step for each client, and the lapse in time for each consecutive
step before that until the query reaches the initializing action of the
process ("review" under the dropdown list in the [action] field.
I don't know VBA, but I hope I can be walked through simpler code (I've done
my calculations in calculated query fields or calculated report/form controls
up to this point.

I'll split my raise with whoever answers my question first!!
Any help or suggestion would be appreciated.
-Sunshine

 

[John Spencer (MVP)]

It would be a help if you had posted your table and field names (and perhaps a
description of the contents if it is unclear from the field name)

IF your field and table names do not contain spaces or other "special"
characters the following may work.

SELECT ClientID, ActionName,
DateDiff("d",
NZ((Select MAX(A.ActionDate)
FROM Actions as Tmp
WHERE Tmp.ClientID = A.ClientID
AND Tmp.ActionDate < A.ActionDate),A.ActionDate),
A.ActionDate) as ElapsedDays
FROM Actions as A INNER JOIN Clients
ON A.ClientID = Client.ClientID


You could also try, using the DMax Function instead of the subquery.

 

[Marshall Barton]

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 number, which is linked in a
one-to-many relationship with two other tables--one with fields for "action"
taken and the "date" of action, and the other to provide an area for
commentary (a memo box and iD field). The memo table is not essential.
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.
So...how do I calculate elapsed time (days) between the most recent
consecutive step for each client, and the lapse in time for each consecutive
step before that until the query reaches the initializing action of the
process ("review" under the dropdown list in the [action] field.
I don't know VBA, but I hope I can be walked through simpler code (I've done
my calculations in calculated query fields or calculated report/form controls
up to this point.

Use a text box in the report (in the same section as the
action date text box). Set its expression to something
like:

=DateDiff("d", DMax("actiondate", "actiontable",
"caseNum = " & txtCaseNum & " And actiondate < #"
& txtactiondate & "#"), txtactiondate)

 

[Guest]

you suggested:
Use a text box in the report (in the same section as the

action date text box). Set its expression to something
like:

=DateDiff("d", DMax("actiondate", "actiontable",
"caseNum = " & txtCaseNum & " And actiondate < #"
& txtactiondate & "#"), txtactiondate)

But I don't think I'm getting the expression right. I'm not getting any
error or parameter prompts when I run the report, but I have #error output in
the print view.

My first response to this question suggested that I list the names of 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).


I can't even get my report to show the DMax functions I want--it returns the
most recent date in the entire table. I would be able to use DMax textboxes
or query fields to do all of my calculations, but I can't figure out how to
set the criteria for each type of [Action]

I hope my question is clearer this time.
Thanks--
sunshine

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 number, which is linked in a
one-to-many relationship with two other tables--one with fields for "action"
taken and the "date" of action, and the other to provide an area for
commentary (a memo box and iD field). The memo table is not essential.
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.
So...how do I calculate elapsed time (days) between the most recent
consecutive step for each client, and the lapse in time for each consecutive
step before that until the query reaches the initializing action of the
process ("review" under the dropdown list in the [action] field.
I don't know VBA, but I hope I can be walked through simpler code (I've done
my calculations in calculated query fields or calculated report/form controls
up to this point.

Use a text box in the report (in the same section as the
action date text box). Set its expression to something
like:

=DateDiff("d", DMax("actiondate", "actiontable",
"caseNum = " & txtCaseNum & " And actiondate < #"
& txtactiondate & "#"), txtactiondate)

 

[Marshall Barton]

you suggested:
Use a text box in the report (in the same section as the

action date text box). Set its expression to something
like:

=DateDiff("d", DMax("actiondate", "actiontable",
"caseNum = " & txtCaseNum & " And actiondate < #"
& txtactiondate & "#"), txtactiondate)

But I don't think I'm getting the expression right. I'm not getting any
error or parameter prompts when I run the report, but I have #error output in
the print view.

My first response to this question suggested that I list the names of 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).


I can't even get my report to show the DMax functions I want--it returns the
most recent date in the entire table. I would be able to use DMax textboxes
or query fields to do all of my calculations, but I can't figure out how to
set the criteria for each type of [Action]

I hope my question is clearer this time.

Sorry, but I'm more confused now than I thought I was
before. What happened to the Case Number field that you
referred to in your original question?

While I still think I got most of the right idea in my first
try, If you can't translate from the table and field names I
used to the ones you're using, you might be better off
modifying the report's record source query along the lines
that John posted.