Results 1 to 9 of 9
  1. #1
    NMarius is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    4

    Question Multiple subreports/queries under a main report

    Hi,



    I am trying to build a database to collect Field Safety review data from several distinct Units (over 100) grouped by Divisions (over 20)
    The main report has to include all Units under a specific Division on one page and the rows are the questions(21) and on the columns each Unit compliance (Yes/No). An example below

    Unit 1 Unit2 Unit3 Unit4
    Question 1 Yes No Yes Yes
    Question 2 No Yes No No
    Question 3 No No Yes Yes
    Question 4 Yes Yes No Yes
    Question 5 Yes Yes No No
    Question 6 Yes No Yes No
    Question 7 No No Yes Yes
    Question 8 Yes Yes No Yes
    Question 9 No No Yes No


    So far I only know to make specific queries for each Unit and Sub-reports based on those queries under the main Report. Is there a better way to have this report without having to build 100 queries?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your table should be designed as:
    question, unit
    Q1, Unit 1
    Q1, Unit 2
    Q2, Unit 1
    etc...

    the main form/report can then filter on 1 UNIT, or you can still product the report you show above in your example via a Crosstab query.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A UNION query can rearrange the data to the structure recommended by ranman256. Then that query can be used as source for report and use the report Sorting & Grouping features.

    Where is the Division identifier?
    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.

  4. #4
    NMarius is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    4
    Thank you ranman256, the table is designed in the way you mentioned but the crosstab query is limited to 3 headers, it doesn't help me too much. I have 21 questions (rows) and up to 9 units (columns), depending on each Division. the columns are counted data, this part is covered but the final report still give me headaches

    @June7. Sorting and grouping is a compromise, I hoped that there was something better

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by crosstab is limited to three headers? Why is sorting & grouping in report a compromise? Perhaps you should provide example of raw data.
    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.

  6. #6
    NMarius is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    4

    more details on request

    Quote Originally Posted by June7 View Post
    What do you mean by crosstab is limited to three headers? Why is sorting & grouping in report a compromise? Perhaps you should provide example of raw data.
    A crosstab query will accept only three headers (I considered that the Units will fit there), maybe I didn't understood well the functionality but I tried. I use one table containing several fields, among them ID, department, division, unit, date, question1, question2 and so on up to 21 questions. Questions are Yes/No type (compliance with applicable rules)
    Report has to contain a Count on all No answers, for all Units belonging to a Division, the largest number being 9 Units arranged on columns
    The only solution I have so far is query and Count for each Unit and then columnar subreports containing the data fro each Unit
    This because my Supervisor does not accept a different type of report.
    My question, to be more clear is: A different approach will be easier? To obtain the result I want.

    Click image for larger version. 

Name:	crosstab query.PNG 
Views:	19 
Size:	28.6 KB 
ID:	33501
    Last edited by NMarius; 04-10-2018 at 11:32 AM. Reason: image attach

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It will only accept 3 row headers using the wizard but you can easily add more in the query designer.
    In fact there is no need to use the wizard at all once you understand the crosstab structure
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    NMarius is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    4
    Oh, that's something I haven't try. Thank you, I will look into this

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, wizards can be limiting. Use query desiger grid directly. In query design click CROSSTAB on ribbon. Multiple row headers possible. Only one each column and value each.
    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: 2
    Last Post: 01-11-2018, 12:10 PM
  2. Main Report and Subreports repeating data
    By PinkLady50 in forum Reports
    Replies: 17
    Last Post: 09-15-2017, 09:37 AM
  3. Replies: 4
    Last Post: 12-29-2014, 11:27 AM
  4. Empty Main Report, won't run subReports
    By rankhornjp in forum Reports
    Replies: 8
    Last Post: 03-15-2013, 11:07 AM
  5. Replies: 1
    Last Post: 11-24-2012, 04:40 PM

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