I have a large-ish Access 2013 database I use to manage time on an engineering project. The principal constructs are a large table of raw timesheet records downloaded from our enterprise timekeeping system, and a staff list table containing names, salary rates, selling rates, etc. I want to create a report to print the profitability of the project by staff member. The fields in this report come mainly from the staff list, i.e. [StaffID] [SellRate] [SalaryRate] [OverheadMult] & [ProfitRate], but the [HoursWorked] to date comes from the raw timesheet table. I added [HoursWorked] to the report detail by simply using a DSum function over the timesheet table and then calculated the amount of [Profit] earned to date for each staff member.
The problem occurs when I try to calculate totals. I can create a [TotalHoursWorked] amount at the bottom of the report by using a similar DSum formulation, but cannot create a [TotalProfit] amount this way.
Here are the formulas I'm using:
Report line items:
[HoursWorked] = DSum("[Spent Time]","tblRawData","[ID] = [StaffID] and [Department] = 'Jacobs' and left([Code],1)<=4")
[Profit] = DSum("[Spent Time]*[ProfitRate]","tblRawData","[ID] = [StaffID] and [Department] = 'Jacobs' and left([Code],1)<=4")
Report totals:
[TotalHoursWorked] = DSum("[Spent Time]","tblRawData","[Department] = 'Jacobs' and left([Code],1)<=4")
[TotalProfit] = DSum("[Spent Time]*[ProfitRate]","tblRawData","[Department] = 'Jacobs' and left([Code],1)<=4")
NB---> This last formula doesn't work; it gives me the total hours x profit rate for the last row in the table.
Any suggestions? I don't really want to have an [Hours_to_Date] field in my staff table that has to be updated every week when the new timesheet data is downloaded.