URGENT - Getting hours totaled for the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with our subcontractors and the hours that they worked
daily. What I need to do is get a report to total the hours that the
subcontractor worked in a month - with each subcontractor on its own page.
The page needs to show the subcontractor's name and the dates that they
worked (not a problem), the hours that they worked each day (also not a
problem), with the total at the bottom of the page.
For some reason, I can't figure out how to get the total to appear at the
bottom of the page - probably because I can't figure out how to write the
function statement.
Can anyone help?
 
=Sum(Hours) in the contractor group band should do it.

By the way, check out the Reports newsgroup for future report questions.
 
Correction, add it to the group band under the subcontractor band, which
groups the dates by month. THAT should do it..

Add page breaks, or set the force new page properties as needed, too.
 
Thanks for your reply. I have encountered the following problem:
1) Access rounds ##s up and I have 1/2 hours in that field. The only way to
fix it was to make it a text field. Access will not sum text fields.
2) "The expression you entered contains invalid syntax. You may have
entered an operand without an operator."
I entered it exactly as you had in your reply.
 
Oh, I just thought to mention that all of the hours are stored in the same
field, but different records (obviously). Is that going to change the syntax
any?
 
Thanks for your reply. I have encountered the following problem:
1) Access rounds ##s up and I have 1/2 hours in that field. The only way to
fix it was to make it a text field. Access will not sum text fields.
Click to expand...

I'd suggest storing duration in a Long Integer field, storing
*minutes* - i.e. 2 hours 30 minutes would be stored as 150.

You can display this in either hours and fractions of an hour by just
dividing by 60 for display purposes (and setting the decimal places
appropriately) or as hours and minutes with an expression like

=Sum([fieldname]) \ 60 & Format(Sum([fieldname]) MOD 60, ":00")

John W. Vinson[MVP]
 
I have a database with our subcontractors and the hours that they worked
daily. What I need to do is get a report to total the hours that the
subcontractor worked in a month - with each subcontractor on its own page.
The page needs to show the subcontractor's name and the dates that they
worked (not a problem), the hours that they worked each day (also not a
problem), with the total at the bottom of the page.
For some reason, I can't figure out how to get the total to appear at the
bottom of the page - probably because I can't figure out how to write the
function statement.
Can anyone help?
Click to expand...

I take it you're using the Sorting and Grouping property of the Report
to group by subcontractor? If so, be sure that you have the Group
Footer visible (it's not by default); put a textbox in this Footer
with a control source =Sum([Hours]) where Hours is the number field
containing the number of hours worked. It's necessary to use the Group
Footer, not the Page Footer... this may be the source of your
difficulties.

If you're storing the hours worked in a Date/Time field, you should
reconsider: date/time fields are good for recording a point in time,
but less good for storing durations.

John W. Vinson[MVP]
 
John-
Thanks for the help. I am getting closer and closer with this stupid thing
:).
I have had to use a text box for the hours worked field because Access
rounds up and I have 1/2 hours in that field. I need it to be exact. Any
ideas?
Thanks!
Heather

John Vinson said:
I have a database with our subcontractors and the hours that they worked
daily. What I need to do is get a report to total the hours that the
subcontractor worked in a month - with each subcontractor on its own page.
The page needs to show the subcontractor's name and the dates that they
worked (not a problem), the hours that they worked each day (also not a
problem), with the total at the bottom of the page.
For some reason, I can't figure out how to get the total to appear at the
bottom of the page - probably because I can't figure out how to write the
function statement.
Can anyone help?
Click to expand...

I take it you're using the Sorting and Grouping property of the Report
to group by subcontractor? If so, be sure that you have the Group
Footer visible (it's not by default); put a textbox in this Footer
with a control source =Sum([Hours]) where Hours is the number field
containing the number of hours worked. It's necessary to use the Group
Footer, not the Page Footer... this may be the source of your
difficulties.

If you're storing the hours worked in a Date/Time field, you should
reconsider: date/time fields are good for recording a point in time,
but less good for storing durations.

John W. Vinson[MVP]
Click to expand...
 
No need to change the field to text. Just make sure that where the field is
defined, that it is defined with 2 decimal places. As long as you have that,
there should not be a problem. (i have a database summing up hours for
employeesthis way) In fact, my field is defined as "single" with 2 decimal
places. Then when you sum up the fields in the report using =sum([Hours])
there should not be a problem.
 
Heather,

You don't need to change the field to a textbox though in order to keep all
fractions. If you make sure that the field is defined as having 2 decimal
places, you should be good to go with summing (define as number and "single"!)

MN

HeatherLou1974 said:
John-
Thanks for the help. I am getting closer and closer with this stupid thing
:).
I have had to use a text box for the hours worked field because Access
rounds up and I have 1/2 hours in that field. I need it to be exact. Any
ideas?
Thanks!
Heather

John Vinson said:
I have a database with our subcontractors and the hours that they worked
daily. What I need to do is get a report to total the hours that the
subcontractor worked in a month - with each subcontractor on its own page.
The page needs to show the subcontractor's name and the dates that they
worked (not a problem), the hours that they worked each day (also not a
problem), with the total at the bottom of the page.
For some reason, I can't figure out how to get the total to appear at the
bottom of the page - probably because I can't figure out how to write the
function statement.
Can anyone help?
Click to expand...

I take it you're using the Sorting and Grouping property of the Report
to group by subcontractor? If so, be sure that you have the Group
Footer visible (it's not by default); put a textbox in this Footer
with a control source =Sum([Hours]) where Hours is the number field
containing the number of hours worked. It's necessary to use the Group
Footer, not the Page Footer... this may be the source of your
difficulties.

If you're storing the hours worked in a Date/Time field, you should
reconsider: date/time fields are good for recording a point in time,
but less good for storing durations.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
John-
Thanks for the help. I am getting closer and closer with this stupid thing
:).
I have had to use a text box for the hours worked field because Access
rounds up and I have 1/2 hours in that field. I need it to be exact. Any
ideas?
Click to expand...

Yes, posted elsewhere in this thread.

There are several types of Number. The default Number datatype is Long
Integer which is (as the name implies) a whole number only. Single and
Double numbers allow decimal places, and may be adequate for your
needs (they have some problems with roundoff error - if you're
searching for the vaguely remembered record with 5.1 hours you might
not find it because Access stored it as 5.099999999999999983).

Or, you can store *minutes* worked rather than hours and use
arithmatic to calculate hours and fractions, or hours and minutes.

John W. Vinson[MVP]
 
Back
Top