Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Posts
    4

    Unanswered: Splittting a Sum in a report

    I am working on a work order database for my employees. we have "regular" and "emergency" work orders. They have to enter either one or the other in a field. When i run a report, I am using a sum to add all the hours for all the work orders, but they are combined. I know I can add more than one sum field in the report footer, but I want to separate them so that if the work order type is "emergency" it will be a sum in one field, and another for the "regular" field. I know someone is much smarter than me at this because I chase turds and fix things, not this complicated db stuff. Help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd need a couple more pieces of information to help you answer your question:
    1. What database are you using?
    2. What Reporting tool are you using?
    3. What is the table schema? The column names and data types are most important part for now.
    4. What do you envision your report looking like?
    If you can answer these questions, I can probably help you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    4

    Question Splitting a Sum in a report

    Pat,

    I am using Access 2007 with their reports. I don't plan on exporting it to any other report software.
    The text box label is "Work Order Type", which is where you have 'emergency' or 'regular'.
    The text box with the hours for one particular work order is called "Total Hours" This total does not know wether it was emergency or regular hours.
    The text box I want to have the sum of emergency hours would be called "Total Emergency Hours" and then the same layout for regular hours in a seperate box.
    I envision this with four boxes in play, 'work order type', 'total hours' (for each individal work order), 'total E hours', and 'total R hours', both of which sum the total number for all work orders in the report.
    Does that make any sense??
    Thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Access is pretty easy.
    1. Create a query based on your table
    2. Create a computed column something like Iif("Emergency"=type, hours, 0)
    3. Create another one for non-emergency
    4. Rock and roll using the query to build your report!
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2012
    Posts
    4
    I am going to try that tonight. I was hoping not to have to do that, but i figured it was coming. Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In Access, he could also create conditional fields on his report, one for each WorkOrderType, and then sum those fields in his footer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another way of removing the fur from this particular feline could be

    to create two hidden controls on the report
    called say StdHours and EmHours
    set the datasource for those controls to
    =iif([MyTypeColumn= 'Emergency'],[HoursWorked],0) 'for EMHours
    =iif([MyTypeColumn <> 'Emergency'],[HoursWorked],0) 'for StdHours
    then sum thoise controls int he report / group footers
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2012
    Posts
    4
    Do you know where I would put those "hidden" controls within the report. Would I be writing code in the field I want to sum the hours for each type of work, Emer and Reg??

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could put them anywhere in the detail section.
    I'm going to move this thread to the Access subforum, where you may get more advice.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •