Help calculating duration of time on a report. (1 Viewer)

[bradwfresno]

Greetings Access Users:

My name is Brad Williams and I am familiar with the Access GUI and layout however I am venturing into functionality. I have before me a project which I need to track attendance of people over several classrooms. I have bar code scanning equipment scanning the ID of a person and also recording a date and a time stamp along with the unit ID as follows.

[Transactions]
PERSONID TDATE TTIME ID UID
1980 5/11/2012 8:05 AM 0000000000674302 1

I have several relations setup between a person table via the BADGE number = PERSONID

[Person]
UID BADGE F_NAME L_NAME COMPANY PHONE Email
1 1980 Brad Williams RBT Company 559 513 8612 bradATrbtcompany.com

And the Classes Table via the ID = ID from Transactions

[Classes]
UID ID CLASS NAME DISCRIPTION SDATE STIME EDATE ETIME
1 0000000000674302 Farming All about farming 5/11/2012 7:55 AM 5/11/2012 10:00 AM

I built a query that determins what class someone was in based on the date and time and unit ID that scanned their Badge.

[Class Transactions]
CLASS NAME F_NAME L_NAME COMPANY PHONE Email SDATE STIME EDATE ETIME TDATE TTIME BADGE
Farming Brad Williams RBT Company 559 513 8612 bradATrbtcompany.com 5/11/2012 7:55 AM 5/11/2012 10:00 AM 5/11/2012 9:55 AM 1980
Farming Brad Williams RBT Company 559 513 8612 bradATrbtcompany.com 5/11/2012 7:55 AM 5/11/2012 10:00 AM 5/11/2012 8:05 AM 1980

I built a report to display and group the data with a simple expression sum([TTIME])

F_NAME L_NAME COMPANY PHONE Email CLASS NAME SDATE STIME TDATE TTIME AccessTotalsTTIME
Brad Williams RBT Company 559 513 8612 bradATrbtcompany.com Farming 5/11/2012 7:55 AM 10:00 AM 5/11/2012 8:05 AM
Brad Williams RBT Company 559 513 8612 bradATrbtcompany.com Farming 5/11/2012 7:55 AM 10:00 AM 5/11/2012 9:55 AM
Hours 0.75

Here is the problem. the difference between 8:05am and 9:55am is not 0.75 Hours. Please help me rectify this.

Thank you,

Brad Williams

 

[CraigDolphin]

Ordinarily, if you have a start date/time and an end datetime in a single row of a table or query, you would use a simple datediff (http://www.techonthenet.com/access/functions/date/datediff.php) function with the two date fields as inputs.

However, if I understand you right, you need to calculate the difference between date/times on two different rows?

If that's right, then the question arises how is the database to know which row is the start, and which row is the end of the interval? Bear in mind that you might have 1, 2, or more scans (however uncommon) per person, per class.

What I would probably do is something like the following.

I would create a totals select query that lists the person, the class, the minimum date/time, and the maximum date/time from your information.

Then I'd create a second query that uses the datediff function in a calculated field to determine the amount of time elapsed between the minimum date/time, and the maximum date time from the first query., for each person and class.

Base your report on this second query.

Hope it helps.

 

[bradwfresno]

That sounds good to me however I would need some assistance in that process.

Do you think you could help me?

I can send you the database as it is very small at the moment.

 

[CraigDolphin]

Have a look at the following. I've added two new queries.

 

[bradwfresno]

Have a look at the following. I've added two new queries.

You just saved me at least an hour or two. Do you have a paypal account if so PM me your E-mail I want to send you a small thank you.

 

[CraigDolphin]

Glad it was helpful. And while I do appreciate the offer, there's no need to pay me anything. I learned much of what I know from many knowledgable posters on this forum and helping others periodically is my way of repaying their kindness.