How to create a running total of times on a report

[accessnovice1000]

Dear Everybody,

I created a database to keep track of my internship hours. I have a form with “date” “time in” “time out” and “details of internship” fields.

I set up a query to calculate the number of minutes I had spent at the internship site using this code:

[FONT=&quot]Minutes: DateDiff("n", [TIMEIN], [TIMEOUT])[/FONT]

Then, I used this code to convert the total minutes into hours and minutes:

=[TIME] \ 60 & Format([TIME] Mod 60, "\:00")
Now, on my report, I have a separate line for each time I checked into the internship. On the report I see:

Date / Time In / Time Out / Details / Total Minutes / Hours:minutes

The second line of code makes the total time show up as “1:30” to symbolize 1 hour and 30 minutes.

THIS IS WHAT I NEED

I need one line item at the bottom of the report to calculate all of the times… for example, if I worked

MONDAY 1:30
TUES :30
WED :30
THURS 1:00
FRI :30

… then I need a field at the bottom of the report to show that all these values equal a total of 4:00 (4 hours)

How to do this please? I have no idea if I explained this properly, but I did attach the database I created. Please don't laugh. It's very basic.

I'm very grateful for all your help, in advance.

Thank you.

 

[pbaldy]

In the report footer you should be able to use your formula on the sum of the minutes field:

Sum(Minutes)

 

[sneuberg]

It appears that you want to group this by week. I suggest looking at the DatePart function (see http://www.techonthenet.com/access/functions/date/datepart.php). If you put an expression like

WeeK: DatePart("ww",[DATE1])

in your query I think you'll see you will have something to group on in your report. Then you can add a sum to that group.

 

[accessnovice1000]

Hi Guys.... I appreciate your help, but here's my response:

To Paul: that code did not work. Once I have the textbox with the hh:mm (hours: minutes) calculation, I have no idea what that field is called in order to do a sum=(?????)

When I tried to add a textbox with Sum=(TIME), that did not work.


To the other respondent:
I do not need the info to be categorized by weeks. I like the report as-is, because it's very easy to follow. The only thing we need is something in the footer with the total time in hours and minutes (for all the individual line items together).

P.S. I attached the database in the original posting, in case you have a few minutes to experiment .

 

[pbaldy]

The field with minutes:

=Sum([TIME])\60 & Format(Sum([TIME]) Mod 60,"\:00")

By the way, Time is a bad field name. Sooner or later Access will confuse it with the Time() function. Look up reserved words, like:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187

 

[accessnovice1000]

This looks great.

What I ended up doing - while I was awaiting your response - was:

Created a textboox in the footer:

=Sum([TIME])

I noticed that it counted up all the minutes presented in the document..... then, I created another text box beside it and used this code:

=[Text18]\60 & Format([TIME]) Mod 60,"\:00")

And it works perfectly... on the pilot sheet... now I've gotta try it out on the actual document.... this is GGGRRrrrrreat !

 

[pbaldy]

I don't think that's going to work correctly. You're using the [TIME] field for the minutes, which will use the value from the last record, not the value from the sum.