Collating, calculating, and displaying the result

[neillovell]

Hi,
I have several reports, one for each employee, that details which projects they have been working on and for how many hours.
I also have the employees' wages per hour in a table.

What I'd like to do is gather up all this data and display it in a report, so the reader can see how many hours in total are spent on what project, and how much it has cost the company.

So EmpA works on Proj#1 for 10 hours. His wage is £5 per hour, sothe report displays

PROJECT HOURS COST
Proj#1 10 £50


and is EmpB works on Proj#1 also, for the same amount of time and for the same money, you get:

PROJECT HOURS COST
Proj#1 20 £100


Now the question is how to achieve this - should I have a query for each employee that checks their personal cost per project and THEN add them in a report, or forgo that and go stright to adding them all together? What if new employees join the company, I can hardly go and change all the VBA code to accomodate them, can it be done automatically?

 

[randysmid]

Hi,
How about the idea of having two separate reports? So, you would keep your original one that is designed to be printed for each employee.

The second report would be based on a query that links the tables together, and is sorted by project id first, then by employee number. In the design of the second report, you would set "Sorting and Grouping" (found under the View menu item). The first grouping would be based on project id. The second grouping (for sorting purposes) would be based on employee id. That way, your consolidated report might look like this:

Project Cost Report

Project id: 1234 Desc: To create a consolidated ...
Emp# 1234 Hrs: 20 Cost: $100.00
Emp# 2345 Hrs: 100 Cost: $500.00

You could then even add a Group Footer to give you the totals for each project. You can set this in the "Sorting and Grouping" dialog box.

HTH,
Randy Smith
California Teachers Association