URGENT - Cannot get Report to total

  • 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?
 
You can't easily put totals in Page Headers or Footers. You can easily put
totals in all other sections. The controlsource should be something like:

=Sum([ExpressionToSum])
 
You have mentioned few details, so I will will assume that there is a
Subcontractor table, that the table in which you record the hours each
subcontractor works is a separate table (I will call it tblSubJob) that is
related to the Subcontractors table through the primary key field
(SubcontractorID), and thateach day the subcontractor worked is a separate
record. I may suggest things you have already done, since I am not sure what
you have tried.
I With the report open in design view, click View > Sorting and Grouping.
Select the subcontractor name in the top line of the Sorting and Grouping
dialog. Sort ascending. Select SubcontractorID as the second line, change
Group Header and Group Footer to Yes, Group On Each Value, and Keep Together
With First Detail (I think). Take a look in Report view to be sure it is
what you need so far. Go back to design view, and add a textbox
(txtSubHrsTotal) to the report. Set its control source to SubHrs (or
whatever name you use for subcontractor hours in each record). Set its
visible property to No, and its Running Sum property (on the Data tab) to
Over Group. Now add an umbound text box to the report's group footer, and
set it s control source to =[txtSubHrsTotal]. Use whatever names you prefer,
of course.
This is all pretty general, since you have not provided many details. In
general you should bear in mind when posting a question that there are often
several ways of doing things, and that there is no way to know what choices
you have made unless you explain them. If this sketch doesn't get you where
you need to go, provide some details of your database structure, including
relationships, the report's record source, and any queries you are using.
 
Actually, here is how I have it set up - at the request of the coworker that
I am doing this for:
There is only 1 table. In that table I have a date field, Main contractor,
tier contractor, man hours and # of laborers.
I need the hours in the man hours field to sum for each contractor for each
month. But I can't seem to get the hours to total like that.


BruceM said:
You have mentioned few details, so I will will assume that there is a
Subcontractor table, that the table in which you record the hours each
subcontractor works is a separate table (I will call it tblSubJob) that is
related to the Subcontractors table through the primary key field
(SubcontractorID), and thateach day the subcontractor worked is a separate
record. I may suggest things you have already done, since I am not sure what
you have tried.
I With the report open in design view, click View > Sorting and Grouping.
Select the subcontractor name in the top line of the Sorting and Grouping
dialog. Sort ascending. Select SubcontractorID as the second line, change
Group Header and Group Footer to Yes, Group On Each Value, and Keep Together
With First Detail (I think). Take a look in Report view to be sure it is
what you need so far. Go back to design view, and add a textbox
(txtSubHrsTotal) to the report. Set its control source to SubHrs (or
whatever name you use for subcontractor hours in each record). Set its
visible property to No, and its Running Sum property (on the Data tab) to
Over Group. Now add an umbound text box to the report's group footer, and
set it s control source to =[txtSubHrsTotal]. Use whatever names you prefer,
of course.
This is all pretty general, since you have not provided many details. In
general you should bear in mind when posting a question that there are often
several ways of doing things, and that there is no way to know what choices
you have made unless you explain them. If this sketch doesn't get you where
you need to go, provide some details of your database structure, including
relationships, the report's record source, and any queries you are using.

HeatherLou1974 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...
Click to expand...
 
I wonder why your coworker didn't just have you use a spreadsheet, since that
is essentially what you have. However, what I have said will still apply.
Group by contractor, set up the hidden text box, and place the text box in
the group footer. The problem will come if you ever misspell a contractor
name or add an extra space or something, in which case that record will not
be included with the rest of the records for that contractor. You should at
least consider using a combo box to select the contractor name, to assure
that typos don't throw off your results. Consider investigating relational
database design. It will save you untold hours and hassles.

HeatherLou1974 said:
Actually, here is how I have it set up - at the request of the coworker that
I am doing this for:
There is only 1 table. In that table I have a date field, Main contractor,
tier contractor, man hours and # of laborers.
I need the hours in the man hours field to sum for each contractor for each
month. But I can't seem to get the hours to total like that.


BruceM said:
You have mentioned few details, so I will will assume that there is a
Subcontractor table, that the table in which you record the hours each
subcontractor works is a separate table (I will call it tblSubJob) that is
related to the Subcontractors table through the primary key field
(SubcontractorID), and thateach day the subcontractor worked is a separate
record. I may suggest things you have already done, since I am not sure what
you have tried.
I With the report open in design view, click View > Sorting and Grouping.
Select the subcontractor name in the top line of the Sorting and Grouping
dialog. Sort ascending. Select SubcontractorID as the second line, change
Group Header and Group Footer to Yes, Group On Each Value, and Keep Together
With First Detail (I think). Take a look in Report view to be sure it is
what you need so far. Go back to design view, and add a textbox
(txtSubHrsTotal) to the report. Set its control source to SubHrs (or
whatever name you use for subcontractor hours in each record). Set its
visible property to No, and its Running Sum property (on the Data tab) to
Over Group. Now add an umbound text box to the report's group footer, and
set it s control source to =[txtSubHrsTotal]. Use whatever names you prefer,
of course.
This is all pretty general, since you have not provided many details. In
general you should bear in mind when posting a question that there are often
several ways of doing things, and that there is no way to know what choices
you have made unless you explain them. If this sketch doesn't get you where
you need to go, provide some details of your database structure, including
relationships, the report's record source, and any queries you are using.

HeatherLou1974 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...
Click to expand...
Click to expand...
 
Back
Top