Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Totals for Group

    I am hoping someone can give me an EASY way to do what I need to do.

    I have a report that has 3 group levels(copy attatched, not pretty but you'll get the jist.
    1. Department
    2. Month
    3. Job Code

    I have the Totals for the Month no problem, simple Sum function in footer.

    The issue I am having is I need a Grand Total for each department but for Each Job Code. Example Attached.

    I attempted to use an IIF statement but that did not work as expected, doesn't work the same way as in Excel.

    I also tried a Sub Report, but don't think i did something right there to get that to work, I got stuck in a loop of my prompts.

    Is there some way to do this that does not involve major VBA or SQL programing as I don't know what I am doing with those.
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Try this:

    Right click in report in design mode, select to show Report header / footer

    Place in the footer 3 fields

    Field 1: =Sum([Department])
    Field 2: =Sum([Month])
    Field 3: =Sum([Job Code])

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by chris07tibgs View Post
    Try this:

    Right click in report in design mode, select to show Report header / footer

    Place in the footer 3 fields

    Field 1: =Sum([Department])
    Field 2: =Sum([Month])
    Field 3: =Sum([Job Code])
    Will this work in the Department Footer as well? I need totals by Job Code at the Department Level as well as the Report Footer. Where do I place the fields I am actually summing? I have 4 of those.

    Hours; Dollars; Average Rate and FTE.
    Sorry I should have included that in the original post.
    Last edited by CHI Brian; 12-08-10 at 09:05.

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Yes in the department footer it will show the totals of the grouping you set. Follow the same format as shown above and it should work. The report footer will show the totals for the entire report.

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Quote Originally Posted by chris07tibgs View Post
    Yes in the department footer it will show the totals of the grouping you set. Follow the same format as shown above and it should work. The report footer will show the totals for the entire report.
    Sorry to sound dense but the 3 fields you gave me above I still use and then do I include another one for the specific field I want to sum or how do I get the different totlas for the 4 different fields?

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    The 3 fields above are only examples, you only use them if you want a total for those fields. You can replace anything in the brackets [ ] with the field name you want to total.

    So basically, whatever you want to total, whether in the report footer or the grouping footer, will look like this:
    =Sum([Your Field Name Here])

  7. #7
    Join Date
    Mar 2009
    Posts
    120
    Right, I have one of those now, but it is a combined total of all Job Codes for each department. I need a Total for Each Job Code at the Department level.

    Department 1
    July
    RN 1.00
    LPN 2.00
    CNA 3.00
    August
    RN 1.00
    LPN 2.00
    CNA 3.00 etc

    Department Total's
    RN 2.00
    LPN 4.00
    CNA 6.00

    Right Now I just have 4 individual totals for Hours, Dollars, Average Rate and FTE,

    I need totals for those 4 things but broken out by Job Code, So it will Totall All Months together for each job code within each department like the example above.

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    You could try to do a grouping for Job Code within your Departmental grouping, but it might make the report too cluttered.

    What I would suggest is something like this:
    Criteria 1:
    =Sum(Iif([Job Code] = "Criteria1",[Job Code],"0"))

    Just replace "Criteria1" with whatever your job code is.

  9. #9
    Join Date
    Mar 2009
    Posts
    120
    chirs07tibgs, Thank you SO Much. That worked. I changed "Criteria1" as you stated and substituted the 2nd [Job Code] with the field I need to sum and it worked.

    Have 1 more question though. I have a total of 7 different job codes related to this. Not all the Departments use all 7 some use the 3, RN; LPN; CNA, others use only 1 or occasionaly 2.

    Is there a way to Hide the totals as well as the Label if there is a zero value or if they are Null in the Summed field? I don't want these fields to be constants on the Department footers if there is no value.

  10. #10
    Join Date
    Oct 2009
    Posts
    204
    I would put code on the group footer's on current, similar to this:

    =Iif me.fieldname = "0" then
    me.fieldname.visible = false
    else
    me.fieldname.visible = true
    end if

    Along with that, you could also code each field's left and top properties to place them so that there are no gaps if something is missing.

  11. #11
    Join Date
    Mar 2009
    Posts
    120
    On Current? What is this? I don't see anything in the Properties of the footer called Current.

    Also, the "me" do i actually type 'me' or do I substitute with somethine else?
    Last edited by CHI Brian; 12-08-10 at 10:32.

  12. #12
    Join Date
    Oct 2009
    Posts
    204
    Click on the footer, click properties, click the Event tab, then click the elipsis next to On Current, and select Event Procedure. Then put the code in the VBA screen that pops up.

    If you need help, you can e-mail me the database if you want (just send me a pm)

  13. #13
    Join Date
    Mar 2009
    Posts
    120
    My Event Tab does not have an On Current. It only has
    On Format
    On Print
    On Retreat

    My DB are created in Acess 2000, we only recently upgraded to 2003. A bit behind the times here.

  14. #14
    Join Date
    Oct 2009
    Posts
    204
    Sorry, I was thinking backwards. It should be On Format.

Posting Permissions

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