none
Grouping and summary report in MS Access RRS feed

  • Question

  • Hello,

    I have a report that has header grouping for "Programs", details (participant names), and grouping footer with counts the names in each grouping "Programs".  Works good... How do you create a (summary report) in report footer that has all program listed with participant counts from the details?  

    Putting the "Program" field in the report footer only shows the first record (not all of the "Program" groups), was hoping to show each program with a count.  I'm sure I can add a sub report but that might cause issues because the report has filter options when opening.

    Thanks in advance!

    Sunday, August 7, 2016 10:02 PM

Answers

  • You would "publish" that where-clause somewhere both reports (or their underlying queries (hint-hint)) can pick it up. Could be a global variable, or a hidden text field, ...

    -Tom. Microsoft Access MVP

    • Marked as answer by azcactus Monday, August 8, 2016 6:39 AM
    Sunday, August 7, 2016 11:05 PM

All replies

  • The subreport is the only way.

    -Tom. Microsoft Access MVP

    Sunday, August 7, 2016 10:41 PM
  • Thanks for your reply!  I was afraid of that...  Does anyone know how to apply the same report open WhereClause to a sub report when opening the main report?  For instance,  the report is opened by using "DoCmd.OpenReport strDocName, acPreview, , strWhere".  The strWhere would also need to be applied to the sub report.

    Thanks again.

    Sunday, August 7, 2016 11:04 PM
  • You would "publish" that where-clause somewhere both reports (or their underlying queries (hint-hint)) can pick it up. Could be a global variable, or a hidden text field, ...

    -Tom. Microsoft Access MVP

    • Marked as answer by azcactus Monday, August 8, 2016 6:39 AM
    Sunday, August 7, 2016 11:05 PM
  • Thanks for the idea! I was thinking of perhaps setting up a global variable in the parent report that is filled by the Open Arg, then changing the RecordSource to an sql statement (query + variable where clause)  on the On Open event of the main report. I'd do the same with sub report. Will sub report be able to read the global variable in the parent report? 
    Sunday, August 7, 2016 11:31 PM
  • Try it.

    Yes, global variables are global by definition.

    Careful about the sequence of events: use some Debug.Print statements to find out if it's the subreport or the report that goes through the Report_Open first (or other events you're contemplating on using)


    -Tom. Microsoft Access MVP

    Sunday, August 7, 2016 11:46 PM
  • Thanks for your advice Tom.  Worked great... Ended up creating a global variable and entered the following in the on open event of the sub report:

    Private Sub Report_Open(Cancel As Integer)
        Dim sql As String
        
        Static Initialized As Boolean
        If Not Initialized Then
            sql = "Select * from myQuery  " _
                & IIf(Len(WHERESQL) > 0, "WHERE " & WHERESQL, "")
                Me.RecordSource = sql

        End If
    End Sub

    Monday, August 8, 2016 6:38 AM