total time in building

[jenn]

I have data that includes the name of the vistor 'name'
the date of the visit 'date'
the type of service (check in or check out, break out, break in) 'service'
and the time of the service 'time'
So I now need to find the total time the person was in the building... I
have played with some different pivot tables by exporting to excel, but I am
stuck still doing a lot of manual manipulation.
do I query by unique name and then do the calculation of each and put
everything in a report that groups by name/date...
any direction that digs me out of my hole is appreciated. thanks

 

[guido via AccessMonster.com]

You can create 5 queries in Access, using the fifth to display results. You
should not have to do other manipulation.
Create 1 query for each service type. Listing all fields and using -Where
service="check in"-(example)
The fifth query links these four. Use the 'check-in' query to link name and
date to the other queries. You can use outer joins if there will not always
be a break. Add the name and date fields to the query and then a formula that
is similar to this (I've assumed there is not always a break):
Tot_Time: IIf([break_out]![name] Is Null,([check_out]![time]-[check_in]![time]
)*24,(([check_out]![time]-[check_in]![time])-([break_in]![time]-[break_out]!
[time]))*24)