Total hours from Table into Report

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!

HeatherLou1974 wrote:
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 he
=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.




--
Joseph Meehan

26 + 6 = 1 It's Irish Math

Thanks for the info... I had already taken a peek at that and it didn't
really help .
Here's the problem: I don't have another field to add the information to.
All of the hours are in the same field, but in different records (if that
makes any sense). I just need them to add up.

"Joseph Meehan" wrote:

HeatherLou1974 wrote:
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 he
=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.




--
Joseph Meehan

26 + 6 = 1 It's Irish Math

HeatherLou1974 wrote:
Thanks for the info... I had already taken a peek at that and it
didn't really help .
Here's the problem: I don't have another field to add the
information to. All of the hours are in the same field, but in
different records (if that makes any sense). I just need them to add
up.

"Joseph Meehan" wrote:

You don't use the table to store the results of a calculation. The
Report or form calculates the subtotals as it runs and they are not saved.

In a database you seldom want to save the results of any calculation,
you keep the original data and let the program re-compute it any time it is
needed.

Does that help.


HeatherLou1974 wrote:
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
he =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.




--
Joseph Meehan

26 + 6 = 1 It's Irish Math

--
Joseph Meehan

26 + 6 = 1 It's Irish Math