Calculating a Grand Total Based on Computed Field

[Guest]

I am trying to set up a database for our department to track its time. We
also need to have a Budget vs. Actual report sorted by project. One table
has all of the projects with their detail, including budgeted hours for each.
Another table is the details of each employee's daily time. (It allows them
to choose a project from a drop down list, which is based on the project
table, and then they put in the date and # of hours).

In my budget vs. actual report, we want to see the total hours for each
project, the budget hours, and the difference. Therefore, I had to make a
query based on both tables in order to have both budgeted and actual hours.
The only thing is, the total budget hours show up on each line of an
employee's actual hours.

I created a computed field to take the budgeted hours and divide by the
number of records under each project so that the correct budget hours are
shown. I need to see a grand total of budgeted hours at the end of the
report along with a budget vs. actual variance, but I can't find a formula to
work. Please help!

 

[Duane Hookom]

I would create a main report built on a query that had a single record for
each project with the amount budgeted and the Sum of Hours from employee's
daily time. Create a subreport based on the employee time details. Place the
subreport in the detail section of the main report. You should be able to
sum budgeted and [Sum of Hours].

 

[Guest]

I know this is a basic question, but how do you get it to show just the sum
of hours for each project? Currently, I had it set up to show the detailed
employee hours for each project and then I just deleted the details from the
report view. This is the main problem with my report since the budgeted
hours are showing up with each detailed line and throwing the budgeted grand
total off. If you could tell me how to just show the sum of hours, that
would really help me out. Thanks!

I would create a main report built on a query that had a single record for
each project with the amount budgeted and the Sum of Hours from employee's
daily time. Create a subreport based on the employee time details. Place the
subreport in the detail section of the main report. You should be able to
sum budgeted and [Sum of Hours].

--
Duane Hookom
MS Access MVP

I am trying to set up a database for our department to track its time. We
also need to have a Budget vs. Actual report sorted by project. One table
has all of the projects with their detail, including budgeted hours for
each.
Another table is the details of each employee's daily time. (It allows
them
to choose a project from a drop down list, which is based on the project
table, and then they put in the date and # of hours).

In my budget vs. actual report, we want to see the total hours for each
project, the budget hours, and the difference. Therefore, I had to make a
query based on both tables in order to have both budgeted and actual
hours.
The only thing is, the total budget hours show up on each line of an
employee's actual hours.

I created a computed field to take the budgeted hours and divide by the
number of records under each project so that the correct budget hours are
shown. I need to see a grand total of budgeted hours at the end of the
report along with a budget vs. actual variance, but I can't find a formula
to
work. Please help!

 

[Guest]

Actually, i figured out how to summarize the actual hours, but the problem is
the budgeted hours. Since I am combining two tables in my query, it is
matching up the budgeted hours with each line item. For example:

Name Project Actual Budget
John Smith Payroll Project 8 80
John Smith Payroll Project 16 80
Jane Smith Payroll Project 10 80
John Smith Accounts Payable 24 100
Jane Smith Accounts payable 8 100
etc.

So, the total of actual hours is OK, but since the budget is repeated for
each detailed line, I am getting total budget hours that are way over.

How can I get the report to show the summary of actual hours for each
project, but only the budgeted hours listed for each project, which is
already in the database as a total?

Sorry this is so confusing! Thanks so much!!

I would create a main report built on a query that had a single record for
each project with the amount budgeted and the Sum of Hours from employee's
daily time. Create a subreport based on the employee time details. Place the
subreport in the detail section of the main report. You should be able to
sum budgeted and [Sum of Hours].

--
Duane Hookom
MS Access MVP

I am trying to set up a database for our department to track its time. We
also need to have a Budget vs. Actual report sorted by project. One table
has all of the projects with their detail, including budgeted hours for
each.
Another table is the details of each employee's daily time. (It allows
them
to choose a project from a drop down list, which is based on the project
table, and then they put in the date and # of hours).

In my budget vs. actual report, we want to see the total hours for each
project, the budget hours, and the difference. Therefore, I had to make a
query based on both tables in order to have both budgeted and actual
hours.
The only thing is, the total budget hours show up on each line of an
employee's actual hours.

I created a computed field to take the budgeted hours and divide by the
number of records under each project so that the correct budget hours are
shown. I need to see a grand total of budgeted hours at the end of the
report along with a budget vs. actual variance, but I can't find a formula
to
work. Please help!

 

[Duane Hookom]

This is exactly why I thought you might create a Main Report of just Project
information with the Budget field. Then use a subreport based on the
employee/actual hours. You can then sum the Budget hours in the main report
since there are no duplicate Project records.

--
Duane Hookom
MS Access MVP

Actually, i figured out how to summarize the actual hours, but the problem
is
the budgeted hours. Since I am combining two tables in my query, it is
matching up the budgeted hours with each line item. For example:

Name Project Actual Budget
John Smith Payroll Project 8 80
John Smith Payroll Project 16 80
Jane Smith Payroll Project 10 80
John Smith Accounts Payable 24 100
Jane Smith Accounts payable 8 100
etc.

So, the total of actual hours is OK, but since the budget is repeated for
each detailed line, I am getting total budget hours that are way over.

How can I get the report to show the summary of actual hours for each
project, but only the budgeted hours listed for each project, which is
already in the database as a total?

Sorry this is so confusing! Thanks so much!!

I would create a main report built on a query that had a single record
for
each project with the amount budgeted and the Sum of Hours from
employee's
daily time. Create a subreport based on the employee time details. Place
the
subreport in the detail section of the main report. You should be able to
sum budgeted and [Sum of Hours].

--
Duane Hookom
MS Access MVP

I am trying to set up a database for our department to track its time.
We
also need to have a Budget vs. Actual report sorted by project. One
table
has all of the projects with their detail, including budgeted hours for
each.
Another table is the details of each employee's daily time. (It allows
them
to choose a project from a drop down list, which is based on the
project
table, and then they put in the date and # of hours).

In my budget vs. actual report, we want to see the total hours for each
project, the budget hours, and the difference. Therefore, I had to
make a
query based on both tables in order to have both budgeted and actual
hours.
The only thing is, the total budget hours show up on each line of an
employee's actual hours.

I created a computed field to take the budgeted hours and divide by the
number of records under each project so that the correct budget hours
are
shown. I need to see a grand total of budgeted hours at the end of the
report along with a budget vs. actual variance, but I can't find a
formula
to
work. Please help!