combine totals for a group of records

[Guest]

I have a table of employees, that might work under different cost codes and
they have a asgweekdate. I need to pull only the hours from a certain cost
code and from the beginning of their asgweekdate. So if they work under
costcode 6789 starting 10/1/2005. I need the total hours added since 2005
just for the costcode 6789. My query is setup as follow

Cost Code EmpName Asgweekdate Payrate Totalhours.
Groupby Groupby Groupby Groupby Sum

The costcode is setup for a popup box so you can enter the number of the
costcode. The totalhours is set to sum. My problem is my totalhours are
adding in all costcodes.

Thanks in advance for any help I can get.

 

[Guest]

I'm not sure I fully understand your requirement here, but it looks to me
like you simply have to remove the Asgweekdate and Payrate columns from the
query so that it sums over CCostCode and EmpName only. That will give you
the sum of the TotalHours values for each employee for the cost code toy
enter at the parameter prompt. The query would look something like this:

SELECT [Cost Code], EmpName,
SUM(TotalHours) As HoursWorked
FROM YourTable
WHERE [Cost Code] = [Enter cost code:]
GROUP BY [Cost Code], EmpName;

At present you'll be getting a summation of the TotalHours value for each
distinct value of Asgweekdate and PayRate rather than the sum of all
TotalHours values for the cost code/employee.

Ken Sheridan
Stafford, England