Results 1 to 9 of 9
  1. #1
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151

    Report with subreport not outputting data in order expected

    I have a report with a subreport, but the results are not what I was wanting/expecting.
    One record from the detail section is output, followed by a record from the subreport. Then another record from the detail section, followed by the same record from the subreport.
    When it has done this for each record with the same group id, it goes on to the next group and does the same.

    What I want is: in the detail section, list all records with the same group id, then all linked records in the subreport, then go to the next group id and repeat.

    How it is

    A(1) (detail)
    1 (subform)
    A(2) (detail)
    2 (subform)
    A(3) (detail)
    3 (subform)

    B(1) detail
    1 subform
    B (2) detail
    2 subform etc

    What I want is

    A(1) detail
    A (2) detail
    A (3) detail


    1 subform
    2 subform
    3 subform

    B(1) detail
    B (2) detail
    B (3) detail
    1 subform
    2 subform
    3 subform

    Maybe I need 2 detail sections. If so, how do I create a 2nd detail section?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There can be only 1 detail section.

    Have you set up a group section for the groupID?
    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.

  3. #3
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    No. I don't know how to add a section.
    I have a report header/footer, a page header/footer, a "review number" header (which is empty), a BPItem Header (which has headings for the detail section), and the detail section.

    I am trying to give the user a printable version of the same data as on the input screen. The input screen has a main form with 4 nested subforms, shown below
    Click image for larger version. 

Name:	database.jpg 
Views:	14 
Size:	64.6 KB 
ID:	16521















    What I get on the report is
    Click image for larger version. 

Name:	report.jpg 
Views:	14 
Size:	24.3 KB 
ID:	16522Click image for larger version. 

Name:	report.jpg 
Views:	14 
Size:	24.3 KB 
ID:	16522

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Are you embedding forms or tables as subreport? Probably not a good idea.

    Report in design view, right click anywhere within the report frame > Sorting & Grouping. This opens a wizard at the bottom of the Access frame. Access Help has more guidance on report Sorting and Grouping functionality.
    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
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Thanks, but I decided I needed to create a temp table to report from. The two tables I was pulling from were such that I needed to report all the pertinent records from table 1, then all the pertinent records from table 2 - table 1 does not contain "headers" for table 2. They are equal in the level. Anyway, I got it working. Thanks for the suggestions.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Perhaps a UNION query would satisfy your requirements for merging the two tables. Why are there two separate tables - should they be one table to begin with?
    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.

  7. #7
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Don't know about the union query, when I tried going into the query design tool and selected union for the type of query, it gives me a blank screen. If this can give me the results I need, then I would prefer to use a query over a temp table.

    There are two tables because we are reviewing errors on two different levels with different selections dependent upon the level. On the report, I want to list all the records from level 1, then all the records from level 2 for a given case. The level 2 records are not related to the level 1, which is why the grouping doesn't work for this.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If the fields of the two tables are identical, should be one table with another field for the level identifier.

    UNION query must be typed in the SQL View, there is no wizard or builder for UNION. http://www.w3schools.com/sql/sql_union.asp
    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
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Got it! This is exactly what I needed. Thank you!!!!

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

Similar Threads

  1. Replies: 6
    Last Post: 12-13-2013, 12:38 PM
  2. Replies: 1
    Last Post: 12-02-2012, 02:10 AM
  3. Replies: 1
    Last Post: 09-21-2012, 03:31 PM
  4. Outputting proper grammar . . .
    By timmygrover in forum Queries
    Replies: 2
    Last Post: 03-28-2012, 03:09 PM
  5. How to report data in fical year order
    By gemini2 in forum Reports
    Replies: 1
    Last Post: 05-27-2006, 06:26 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