Simple Report w/Grouping not Working

[gguerra3]

For some reason I cant get this simple report to work, am I missing something?

I have simplified the info for easy reading. I execute a query on a table to get the following datasheet.

Table (datasheet)

Field Data Type

JobNumber Number, Long Interger
Department Number, Long Interger
Time Date/Time, Short Time

All I want to do is subtotal Time by Job and by Dept.

Sample Data
JobNumber Department Time (Hrs)
2005 3 9:00
2005 3 4:15
2005 4 2:45
2005 4 12:00
2006 1 8:00
2006 1 8:00
2006 2 8:00
2006 2 0:15

Grouping
Job Number, Sort Order: Ascending
Department, Sort Order: Ascending

Report Sections
1. I have a JobNumber Header with 1 text box, (JobNumber)
2. In the Detail I have 2 text boxes Department, and Time, (Visible Property for detail section is No)
3. I have a Department Footer with Department and Time
4. I have a JobNumber Footer with JobNumber and Time

Question:
The subtotals and totals I get on both group levels are wrong, Does the time field in the footers need to be something other than just the field (how do I get it to sum), I do have the Running Sum set to "Over Group" on both group fields. Is there any special way to do calculations (sum) on date/time data types? thanks

 

[dhookom]

It looks like you are using a point in time "9:00" as a duration of time "9 Hrs"? I recommend you store values as either the number of minutes or number of hours. Then, in your footer just use a control source of:
=Sum([TimeField])

If you are experiencing issues, it could be due to a wrong control source, using a function as a field name, or expecting a point in time to be represented as a duration of time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gguerra3]

It is indeed a duration of time which I am storing. and also I do have what you mention as a control source e.g. =Sum([TimeField])in the group footers.

As I describe the data type of time is "date/time, Short Time"
There are other fields in the original table such as 'Time In" and "Time Out" and the field that I use is already a calculated (Total Time). I simplified the example. The actual name of the field I am using is "TotalTime". It seems the control source "=Sum([TimeField])" is only getting the previous value and not a sum of values and again I do have the Running Sum set to "Over Group" on both group fields.

I have done group reports before without any problems, and what I suspect is that Access stores time internally as a fraction (or decimal number less than 1), thereby throwing off the calculations. (Much like excel).

 

[gguerra3]

Sample Data
JobNumber Department Time (Hrs)
2005 3 9:00
2005 3 4:15
2005 4 2:45
2005 4 12:00
2006 1 8:00
2006 1 8:00
2006 2 8:00
2006 2 0:15


These are the actual results I get from the data described above

Job Number Department Total Time
2005
3 13:15
4 4:00
Total Time for Job 2005 4:00
2006
1 16:00
2 0:15
Total Time for Job 2006 4:15

As you can see the only accurate figures are the totals for Department 3 and Department 1, which happen to be the first group, first value. Everything after that is wrong

 

[dhookom]

The totals are correct. This line is technically not correct:
[blue]JobNumber Department Time (Hrs)[/blue]
since the values are not Hrs. The date/time values are stored as floating point numbers where 1=day, 12 hours = .5, and 6 hours = .25.

A short time format doesn't ever display any values >24 hrs. You will not be able to see a display in short time like 28:00. You can multiply your sum by 24 to display the number of hours.
=Sum([Time]) * 24
This would display 28 hours and 45 minutes as
28.75
You would not use Running Sum anywhere in your report. If you don't want to display the number hours as a decimal, you will need an expression that parses out the days, hours, and minutes. This has been posted many times on-line and might even be in the FAQs.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gguerra3]

Thanks for the reply, yes I figured out that you need to multiply the totaltime field by 24 to get a numerical value that you can calculate on. I got the clue from working with time in Excel where you also have to multiply by 24. Thanks again.