Results 1 to 4 of 4
  1. #1
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12

    Summing calculated fields with DSum in a report

    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.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can do all of this work inside the report - but it is preferable to get this done before the report is run. Get all the data correct in the query which is then used as the record source for the report. Do the calcs in the query too. Then when you get to the report all you need to do is display the calculated fields and the totals at the bottom. You shouldn't be using DSum, the fields should be available to you in the report already, without having to go outside of the record source.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you want to do it in the report, create hidden fields in the detail section and for control source point to the fields in the detail:
    sumHoursWorked and for control source = [HoursWorked]
    sumProfit = [Profit]

    In the Properties Data tab for each of those 2 new fields, Select the option for Running Sum Over Group(can do Running Sum over All also but I think you need Group one). You can leave those fields visible so you can see how they add up but when read to go live make them invisible.

    Then in the footer, do 2 more fields and point to those new sum fields.
    TotalHoursWorked and for control source = [sumHoursWorked]
    TotalProfit = [sumProfit]

    But also, looks like your DSUM line is right for TotalProfit. So the one for Hours works but not the profit one?

  4. #4
    Carbontrader is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    12
    Thanks, guys. I solved this along the lines suggested by adding the Raw Timesheet data table to the query, linking it to the Staff table via the StaffID fields and then using running sums to calculate the total hours for each staff, and then for the whole project. I put the report line records into StaffID Header, with the report totals in the Report Footer. The Detail section of the report is empty! Anyway, it works, so all good.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Summing calculated fields
    By George in forum Modules
    Replies: 3
    Last Post: 06-03-2016, 08:21 AM
  2. Replies: 1
    Last Post: 02-27-2014, 07:59 PM
  3. Summing specific fields on a report
    By spacekowboy in forum Access
    Replies: 4
    Last Post: 02-18-2014, 08:44 AM
  4. Dsum in a calculated field
    By JonathanT in forum Reports
    Replies: 3
    Last Post: 01-22-2011, 08:45 PM
  5. Summing Calculated Fields
    By Zoran in forum Queries
    Replies: 1
    Last Post: 03-31-2010, 01:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums