H
helios
I am working on a database in Access 2000 to track review dates for various
programs. I have set up a report that lists the various steps of review
under the grouping level of the [Submission Date] for each [ProgramID] and
the [Days] that have lapsed since the [Submission Date]. each program may
have several [Submission Date]s, which is what I am using to group the cycles
of review (programs are reviewed at least annually).
The report works fine to display the data, but I also want average [Days]
lapsed between the [Submission Date] and the [Date]s of [Action]s "step 1",
"step 2", "step 3" which are in the "Tracking Dates" table and the query for
the report [Date Range].
I have a parameter prompt for the start and end dates for the query based on
the [Submission Date].
I have tried domain aggregate functions in the query and in the report with
no luck. I have only gotten error messages. I need to get averages for each
type of action's [Days] value which is greater than or equal to 0, so my
control source expressions for calculated controls in the report footer are
along these lines:
=DAvg("[Days]","Date Range","[Action]='BMC Approval' and [days]>-1")
I'm thinking that perhaps the aggregate functions in the calculated text
boxes are executing their calculations before the parameters are set by the
user's criteria.
I am a novice in SQL and have thus far gotten away with calculations in
query fields and report controls.
I need the averages to be based on the same criteria for [Submission Date]s
I'd be happy to post more detailed information if needed.
Please help!
Sunshine
Query SQL:
SELECT [Type "Y" to display names; "N" to leave blank] AS [Name Whiteout],
ProgramCode.ProgramNo, ProgramCode.LastName, ProgramCode.FirstName, [Review
Date].[Program Submission], TrackingDates.Action, TrackingDates.Date,
DateDiff("d",[Review Date].[Program Submission],[TrackingDates].[Date]) AS
Days
FROM ProgramCode INNER JOIN ([Review Date] INNER JOIN TrackingDates ON
[Review Date].[review id] = TrackingDates.[Review ID]) ON
ProgramCode.ProgramNo = [Review Date].ProgramID
WHERE ((([Type "Y" to display names; "N" to leave blank])="Y" Or ([Type "Y"
to display names; "N" to leave blank])="N") AND (([Review Date].[Program
Submission]) Between [Run Report for Submission Dates Between: (type start
date)] And [And: (type end date)]));
programs. I have set up a report that lists the various steps of review
under the grouping level of the [Submission Date] for each [ProgramID] and
the [Days] that have lapsed since the [Submission Date]. each program may
have several [Submission Date]s, which is what I am using to group the cycles
of review (programs are reviewed at least annually).
The report works fine to display the data, but I also want average [Days]
lapsed between the [Submission Date] and the [Date]s of [Action]s "step 1",
"step 2", "step 3" which are in the "Tracking Dates" table and the query for
the report [Date Range].
I have a parameter prompt for the start and end dates for the query based on
the [Submission Date].
I have tried domain aggregate functions in the query and in the report with
no luck. I have only gotten error messages. I need to get averages for each
type of action's [Days] value which is greater than or equal to 0, so my
control source expressions for calculated controls in the report footer are
along these lines:
=DAvg("[Days]","Date Range","[Action]='BMC Approval' and [days]>-1")
I'm thinking that perhaps the aggregate functions in the calculated text
boxes are executing their calculations before the parameters are set by the
user's criteria.
I am a novice in SQL and have thus far gotten away with calculations in
query fields and report controls.
I need the averages to be based on the same criteria for [Submission Date]s
I'd be happy to post more detailed information if needed.
Please help!
Sunshine
Query SQL:
SELECT [Type "Y" to display names; "N" to leave blank] AS [Name Whiteout],
ProgramCode.ProgramNo, ProgramCode.LastName, ProgramCode.FirstName, [Review
Date].[Program Submission], TrackingDates.Action, TrackingDates.Date,
DateDiff("d",[Review Date].[Program Submission],[TrackingDates].[Date]) AS
Days
FROM ProgramCode INNER JOIN ([Review Date] INNER JOIN TrackingDates ON
[Review Date].[review id] = TrackingDates.[Review ID]) ON
ProgramCode.ProgramNo = [Review Date].ProgramID
WHERE ((([Type "Y" to display names; "N" to leave blank])="Y" Or ([Type "Y"
to display names; "N" to leave blank])="N") AND (([Review Date].[Program
Submission]) Between [Run Report for Submission Dates Between: (type start
date)] And [And: (type end date)]));