• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Calculating time in an Access report

I have a table in my database that I wish to report on.  The data is organized as follows:

EventDate - DATE field
EventResult - TEXT field

There are 8 possible results, and the table looks something like this....

EventDate              Result
10/23/08 12:01        RED
10/23/08 12:02        BLU
10/23/08 12:02        RED
10/23/08 12:03        RED
10/23/08 12:04        GRN
10/23/08 12:05        RED
10/23/08 12:06        YEL
10/23/08 12:08        BLU
10/23/08 12:09        GRN
10/24/08 12:01        RED
10/24/08 12:02        RED
10/24/08 12:03        BLU
10/24/08 12:03        RED
10/24/08 12:07        YEL
10/24/08 12:08        RED

What I want to do is sub-total (count) the records and also determine the amount of time passed.  

EX:

10/23/08
RED-4
BLU-2
YEL-1
GRN-1
TOTAL-8
8 minutes
AVG 1 per minute

10/24/08
RED-4
BLU-1
YEL-1
GRN-0
TOTAL-6
7 minutes
AVG .85 per minute

I have worked out most of the problem with counting the 'subtotals' in the report by using a DCOUNT funtion in the group footer (thanks to help in another post!) but I have no idea how to attack the calculation of time passed from the first event to the last event for each day.  It's simple math ...  DATE2-DATE1=TIME ELAPSED, but how to I capture what the value for the first event of each day, the last event of each day, and calculate the difference between them by group?


0
callstate
Asked:
callstate
  • 2
1 Solution
 
armynt4Commented:
Try using the min and max aggregate function in your report's query.

so max(Time1)-min(Time2)=time elapsed

I'll try it as well to see what i get.
0
 
carazuulCommented:
Hi callstate,
DMAX - DMIN over the same criteria should work.
Regards,
carazuul
0
 
armynt4Commented:
So on the report you may need to play with the Result field sum calculation.  
But if you list all the time values in the detail of the report you can add a field in the date's group footer with a value like......


DateDiff('n',Min([Time]),Max([Time]))


[Time] being the name of the time value field in your table/query
Creating this Time field by .... Time: Format([EventDate],'hh:mm')

Should work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now