Averaging Dates

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

i have a table (repairs) that has multiple date fields -
the date parameters (beginning and ending date)are coming
from the "DateCompleted" field. i am having difficulty in
writing the following:

Request date range from user (based on date completed)
Calculate the following information based on the items
completed in the date range selected

Avg days from Date received to Date Evaluated
Avg days from Date Evaluated to date quoted
Avg days from Date quoted to date approved
Avg days from date approved to date completed
Total number of items finished in date range
Avg days from date received to date finished for all jobs
completed in date range

Thanks in advance
Frank
 
Frank said:
i have a table (repairs) that has multiple date fields -
the date parameters (beginning and ending date)are coming
from the "DateCompleted" field. i am having difficulty in
writing the following:

Request date range from user (based on date completed)
Calculate the following information based on the items
completed in the date range selected

Avg days from Date received to Date Evaluated
Avg days from Date Evaluated to date quoted
Avg days from Date quoted to date quoted
Avg days from date approved to date completed
Total number of items finished in date range
Avg days from date received to date finished for all jobs
completed in date range
Click to expand...


The secret creating any report is to first get it's record
source query to generate the needed data. I think what you
need here is something like:

SELECT JobID,
AVG(DateDiff("d", received, Evaluated) As AvgA,
AVG(DateDiff("d", Evaluated , quoted) As AvgB,
AVG(DateDiff("d", quoted , approved ) As AvgC,
AVG(DateDiff("d", approved , completed) As AvgD,
AVG(DateDiff("d", received, completed) As AvgE
FROM repairs
WHERE DateCompleted Between [Start Date] And [End Date]
GROUP BY JobID

The report footer section can calculate the total number of
jobs completed by using a text box with an expression like:

=DCount("*", "repairs", "completed Between "
Format([Start Date], "\#\/m\/d\/yyyy\#") & " And " &
Format([End Date], "\#\/m\/d\/yyyy\#"))
 
Thanks Marshall - I'm a bit confused. Is this to be pasted
in the recordsource? What is the JobID? This report is
driving me nuts?
Frank
 
Thanks Marshall - I'm a bit confused. Is this to be pasted
in the recordsource? What is the JobID? This report is
driving me nuts?
Click to expand...

You could put that SQL statement in the record source, but I
think it would be easier to manage if you put it into the
query design's SQL view and save it with a name (then use
the query name in the record source). Once you get the
names straightened out you could switch to query design view
and see how that could have been entered as a Totals type
query.

JobID is the name that I used for for your jobs records'
primary key. Replace that and the other names with the
field names that are in your table.
--
Marsh
MVP [MS Access]


The secret creating any report is to first get it's record
source query to generate the needed data. I think what you
need here is something like:

SELECT JobID,
AVG(DateDiff("d", received, Evaluated) As AvgA,
AVG(DateDiff("d", Evaluated , quoted) As AvgB,
AVG(DateDiff("d", quoted , approved ) As AvgC,
AVG(DateDiff("d", approved , completed) As AvgD,
AVG(DateDiff("d", received, completed) As AvgE
FROM repairs
WHERE DateCompleted Between [Start Date] And [End Date]
GROUP BY JobID

The report footer section can calculate the total number of
jobs completed by using a text box with an expression like:

=DCount("*", "repairs", "completed Between "
Format([Start Date], "\#\/m\/d\/yyyy\#") & " And " &
Format([End Date], "\#\/m\/d\/yyyy\#"))
Click to expand...
Click to expand...
 
Back
Top