Only get minutes added in a sum on a time field........ 3

[curtterp]

and I am pulling my hair out (I am bald). I am making a time card database for my girlfriend for her work. I have a morning in/out and an afternoon in/out, with morning/afternoon totals with a total time. The morning and afternoon totals come out right, and the total time is right, but when I do a report and try to sum up the totals for the week off the total time for the day, all I get is the total for minutes, and nothing for hours. The morning/afternoon and total time are short totals. I am using an unbound box with =sum([total time]) in it.

Any help would be appriciated. Thanks in advance.

 

[cwillsh]

Try this:
Total: DateDiff("n",[TimeIn],[TimeOut])/60

 

[MichaelRed]

From your description, you realy need to go back a few steps. Time In/Out/Am/Pm appears to relate to FOUR (4) fields, where at MOST 2 are useful, and provide the possability of any number of in/out excursions.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[curtterp]

Let me expand what my database looks like for now. It has a morning start field and a morning stop field. Same for the afternoon (like punching out for lunch then back in again. Then the morning has a total field for the morning, same with the afternoon. then the total time field that adds both morning and afternoon totals together. This all works well and I have no problems. My problem is when I want to do a report based on a date range that is inputted ( for week/ bi weekly reports)I tried to calculate the total time for the date range inputted. Thats where the problem lies, just the minutes are getting added, not the hours. When I went to a longer date range ( about a month )then it added just the carry over from the minutes into the hours section, or so it looks like to me. That is what has got me stumped. Does the sum function not work correctly with short time format? If so, what should I be doing different here?

Wish I knew this better.

Thanks again in advance.

 

[MichaelRed]

All date/time fields are really just Double (floating point) values. Only the dispaly is different. The "Integer" portion of the value represents the number of "Days" since the starting point (Dec 30, 1899?). the "decimal" Fractional portion represents the part of the day simce midnight. So a date/time of:
0.5 = 12 hours;
1 hour = 0.083333 days;
1/2 half hour (30 Minutes = 0.04166667 days);
1 Minute = 1.38888888888889E-03 days
1 Second = 2.31481481481482E-05 days

Thus, properly calculated time intervals work perfectly well with the sum function in a query.

For example:

A table:
MyIndex RaceTime
1 1:00
2 1:22
3 3:26
4 2:12
5 1:06
6 1:23
[/b]

A Query:
SELECT Sum(RaceTime_2.RaceTime) AS SumOfRaceTime
FROM RaceTime_2;


A Query Results:
SumOfRaceTime
0.439016203703704


The same query Results with the Format changed to Short time
SumOfRaceTime
10:32

Note that the Table "times" are properly formatted as short time. If your times are as text, some odditites/errors may occur. When the query is 'built', if you do not force the field format to 'short time', you get the fraction of a day - not the time.

I do not understand what you are doing in any detail, so cna only offer this brief example / explination. Summing intervals in MS Access is VALID and commonly done. My only comment is / was toward the tabel design, which causes extra work to chieve a realtively simplistic goal, as you need two interval calculations per record - and then need to sum them within the record. A design which simply included TimeIn and TimeOut could produce the same report with only one 'record' calculation (TimeOut - TimeIn), with the summation in the query alone. Since you need to group by employee anyway, the results are the same. 'Modern' relational database design favors "deep" (many records with less info) over "wide" fewer records but more fields (info) per record. Your design limits the excursions of an employee to two per day, while the more general approach allows as many as necessary. While this may appear to be a trivial issue, it is a design thought/approach/technique which will cause problems. If not in the here and now - certainly in the future.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

I have a time card database I wrote once that I could send to you if you'd like it. It's rather simple and may give you some help/ideas/etc... If you provide me your email address I'll forward it to you.
Joe Miller
joe.miller@flotech.net

 

[curtterp]

First of all, thanks for responding.

Mike, your ideas about my design flaws are noted and appriciated. I originally set this up on Handbase on a palm pilot. Since there was a need for printing, I converted and imported to Access. I am rethinking what I am doing, and might redesign the whole system and import out to Handbase that way. Yes it was a backwards way of doing things, but sometimes I am like that

Joe - I emailed you to let you know my email. Thanks for your offer.

Cwillsh - Thanks for your help. Your idea was something I hadnt thought about either.