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.
.