Results 1 to 10 of 10
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question Hide some Groups in a Report

    Hello All!



    I have a report that gives the total number of people registered for camp.

    Each registrant has to have a "Type", a "Group", "Program Level", and a "Grade"

    This is fine for the girls (it's a Girl Scout Camp) ... so a 3rd grade Brownie is a "G" (type = girl), a "Girl" (group), "BR", and "3"
    While a 7th Grade Cadette is a "G", "Teen", "CD", "7"

    We separate our middle school and up campers as "Teens".

    However, an Adult is "A", "Adult", "Adult", "A".

    Many reports order by grade or by group so Adults have to have a value in each field (actually, there are lookup tables, but once the query is run, the query pulls from those tables and populates).

    For some things, I have to report total adults and total campers (Girls and Teens). Other reports require a count of each grade level or program level.

    I have a report that sort of combines everything and there is some redundancy I'd like to get rid of, if possible.

    First, it groups by Type, then by Group, then Program Level, then by Grade.

    This is perfect for the campers:

    G (All the campers)
    Girls or Teens
    Program Level
    Grade

    Each Grouping level has a total, I can look and see how many 5th graders we have or how many Teens or just how many campers.

    But, for the Adults, it's very redundant.

    A
    Adults
    Adult
    A (grade and type are the same in this case)

    And each Grouping has a total, so, right now its:

    A 64
    Adults 64
    Adult 64
    A 64

    Is there any way to suppress the Group, Program Level, or Grade Headers if they are a certain value? (like if the Program Level is "Adults," don't show it, but if it's "Brownies" do show it)

    Then I could have a report that has one line that totals the adults and then the campers are further grouped and subtotaled.


    Thank you!

    Susie
    Kansas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you dont want the group, filter it out of the query or the report.

    Rpt filter:
    docmd.OpenReport "rpt",acViewPreview ,,"[field]='" & me.textbox & "'"

    but for a complex filter, make a query to get only what you want, and use the same report (but a different name) bound to this query.

  3. #3
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Well, I want ALL the adults and ALL the campers. I just don't want the Program Level group to show for the adults, but I do for the campers.

    So, I'm not sure that filtering out a group would work.

    Susie

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Maybe, try code to set visibility in the Format event of the Group header section. Format event only triggers for PrintPreview or direct to printer.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    June,

    I tried:

    If ProgramLevel = "Adults" then
    GroupHeader1.visible = false
    End if

    But, it hid ALL the GroupHeader1 . I really only want it to hide SOME of the GroupHeader1 .

    I'll have to do some more research. It's entirely possible that what I want isn't really doable.

    Thanks!

    Susie

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Are you aware that "Type" and "Group" are reserved words in Access and shouldn't be used as object names??


    Maybe look into a Union query.
    Create a query ("qryType_G") like the one you have now, but filter it: WHERE [Type] <> "A"
    Then copy that query, rename it ("qryType_A") and change the filter to: WHERE [Type] = "A"
    Now create a Union query with the the two queries "qryType_G" and "qryType_A".

    You can edit the query "qryType_A" to return 1 record with whatever fields you want.....

  7. #7
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    Yeah, I don't actually use "Type" and "Group," I just figured it was easier to use them to explain what I was trying to do.

    I appreciate all your help!

    Susie
    Last edited by catluvr; 04-04-2019 at 08:41 AM.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Need the setting for True as well as False so each instance of the header will get dynamically set.

    Try this one-liner:

    GroupHeader1.Visible = Me!ProgramLevel <> "Adults"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87
    June,

    Just what I needed! Well, not quite, but it got me on the road to success!

    I ended up with this code placed in the "On Format" event of GroupHeader1:

    If me!ProgramLevel = "Adults" then
    GroupHeader1.visible = false
    Else
    GroupHeader1.visible = True
    End if

    I thank all of you for your help!


    Susie
    Kansas

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Glad it works.

    The one-liner should accomplish the same thing. Works for me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 16
    Last Post: 02-06-2015, 11:40 AM
  2. option groups as criteria for a report
    By cfljanet in forum Access
    Replies: 10
    Last Post: 10-30-2013, 02:49 PM
  3. Report with Age groups data under each group
    By jyellis in forum Reports
    Replies: 1
    Last Post: 03-10-2013, 05:12 PM
  4. Report Groups and Sort
    By cbrsix in forum Reports
    Replies: 4
    Last Post: 11-18-2011, 02:06 PM
  5. Replies: 1
    Last Post: 09-27-2010, 06:58 AM

Tags for this Thread

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