HeatherLou1974 said:
I have a database that tracks companies and the hours that they
worked for us each day. I now need a report that will total the
hours for each company each month that they have worked for us.
Each company should have its own page in the report, showing which
days they worked, how many hours and then a total monthly hours at
the bottom of the page.
Can someone help me out here? I have exhausted my knowledge and
that of those around me. Thanks!
Make a report based on the table. you will need to group the
report by company and moth. You will need to use a hidden controls
to come up with totals. The hidden controls are the fun part to get
right, they need to be in the correct parts of the report to work.
I don't have a handy reference handy so I just copied some of the
instructions from the Access help file.
I believe that Northwind has an example. There are other pages
of information in the help file.
Calculate a total or other aggregate values
In Design view
Calculate a total or average on a form or report
Open a form or report in Design view.
Click the Text Box tool on the toolbox.
Do one of the following:
To calculate a total or average for a group of records, add the text
box to the group header or footer.
To calculate a grand total or average for all records in a form or
report, add the text box to the header or footer of the form or
report.
Select the text box, and then click Properties on the toolbar.
In the ControlSource property box, type an expression that uses the
Sum function to calculate a total or the Avg function to calculate
an average.
Tip
In a Microsoft Access database (.mdb), for a form or report, you can
click the Build button to use the Expression Builder to create the
expression.
Example
This control calculates the total sales for each order.
This control calculates the total sales for all orders in the
report.
Notes
The Employee Sales by Country report in the Northwind sample
database has an example of group totals and a grand total. To view
this report, point to Sample Databases on the Help menu, click
either Northwind Sample Database or Northwind Sample Access Project,
and then open the Employee Sales by Country report in Design view.
When computing a total in a form or report in Design view with an
aggregate function such as Sum or a domain aggregate function such
as DSum, you can't use the name of a calculated control in the Sum
function. You must repeat the expression in the calculated control.
For example:
=Sum([Quantity] * [UnitPrice])
However, if you have a calculated field in an underlying query, for
example: =ExtendedPrice: [Quantity] * [UnitPrice]
you can use the name of that field in the Sum function, as shown
here: =Sum([ExtendedPrice])
Calculate a percentage value on a report
Open a report in Design view.
Click the Text Box tool on the toolbox.
Do one or both of the following:
If the report does not calculate group totals, click the group
footer to add a text box to display the group totals.
If the report does not calculate a grand total, add a text box to
the report footer to display the grand total.
Do one or both of the following:
To calculate what percentage of a group total or the grand total
each item is, add a text box to the detail section.
To calculate what percentage of the grand total a group of items is,
add a text box to the group header or footer.
Note If your report includes multiple group levels, place the text
box in the header or footer of the level for which you want
Microsoft Access to calculate a percentage.
Select the text box, and then click Properties on the toolbar.
In the ControlSource property box, type an expression that divides
the smaller total by the larger total of which the smaller total is
a part.
Tip
In a Microsoft Access database (.mdb), you can click the Build
button to use the Expression Builder to create the expression.
Example
The percentage of the grand total is calculated by ...
... dividing the value of the DailyTotal control in the
ShippedDate footer ....
... by the value of the GrandTotal control in the report footer.
Set the Format property of the text box to Percent.
Note The Employee Sales by Country report in the Northwind sample
database has an example of percentages. To view this report, point
to Sample Databases on the Help menu, click either Northwind Sample
Database or Northwind Sample Access Project, and then open the
Employee Sales by Country report in Design view.
Calculate a total or other aggregate values on a data access page
Calculate an aggregate value
When the control you want to use to calculate the aggregate value is
already on the page
Open the data access page in Design view.
Select the text box or bound span control that contains the values
you want to aggregate.
On the toolbar, click the arrow next to the AutoSum button, and
then select the aggregate function that you want to use.
The results you see depend on the type of page and the group level
to which the selected control belongs. If the bound control belongs
toThen
An ungrouped page, or the outermost group level on a grouped
pageMicrosoft Access creates a new group level, and adds a bound
span control to the footer section of the new group level.
An inner group level on a grouped pageAccess adds a bound span
control to the footer section of the parent group level. If the
footer section is not shown, the aggregate control is placed in the
header section of the parent group level.
Note The list of aggregate functions that you see in the AutoSum
list depends on the type of data you want to summarize. For numeric
values, you can choose from Sum, Average, Min, Max, Count, StDev,
and Any. For text, date, and time values, you can choose from Min,
Max, Count, and Any.
When the control you want to use to calculate the aggregate value is
not on the page
This procedure does not apply to ungrouped pages. The page will not
show the detail values of the field that is being used to calculate
the aggregate values. If you want to show both individual and
aggregate values, add the bound control to the page, and follow the
previous procedure.
Open the data access page in Design view.
If the field list is not displayed, click Field List on the View
menu.
In the field list, click the field that has the values you want to
aggregate.
Drag the field to the header or footer section where you want to
show the aggregate values.
For example, if the page has two group levels, Orders and Order
Details, you can drag a field from the Order Details table to the
header or footer section of the Orders group level.
The Layout Wizard dialog box appears.
In the wizard dialog box, select the layout you want, and click OK.
Microsoft Access creates a bound span control in the target section.
The control will use the Sum function for numeric values and the
Count function for all other data types.
Calculate subtotals and grand totals
Calculate a total based on an aggregate value
Select the aggregate control, and then click AutoSum on the toolbar.
Microsoft Access creates a bound span control in the footer section
of the next higher group level. If the aggregate control belonged to
the highest group level, a new group level will be created. The new
control will use the same aggregate function as the aggregate
control on which it is based.
Notes
You can set the properties of the aggregate control, and move,
resize, or format the control to make it look the way you want.
You can change the control's aggregate function by setting the
control's TotalType property to the aggregate function you want.
When you change the aggregate function, the default alias and
caption of the control change to reflect the underlying aggregate
function. To prevent the control name from changing, type a new
alias for the control in its ControlSource property. User-specified
aliases don't change when the aggregate function is changed.
Changing the aggregate function of a control does not affect other
aggregate controls, such as those that calculate subtotals or grand
totals.
.