Solved

How count number of unique values at end of report

Posted on 2016-11-23
5
28 Views
Last Modified: 2016-11-25
I have a report that has a column named for example, "Sport".  In the report footer I want to show the number of times "Football" shows up in the report, or "Baseball" shows up in the report, or "Soccer" shows up in the report, etc.  There are a total of 41 possibilities so I realize the footer will be somewhat lengthy.  But since the report uses date range criteria, not all possibilities may show up depending on the date range.

How can I do this?

Example:

SPORT
Football = 34
Baseball = 12
Soccer = 56
etc.
0
Comment
Question by:SteveL13
5 Comments
 
LVL 3

Expert Comment

by:bfuchs
ID: 41899903
See following, I think you can apply solution here as well

https://www.experts-exchange.com/questions/28948461/How-do-I-get-a-count-of-inner-group-category-in-an-access-report.html

Also you can use a count in detail format event and then display this at footer format.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41900000
Try.. DCOUNT. example..

=DCount("SportsCatergory", "YourTableName", "[SportsCatergory] = 'Soccer'")
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41900033
create a query to use as the RecordSource for a subreport at the end. Before you open the main report, modify the SQL of the query to use the same criteria -- something like this:
'strive4peace
   on error goto proc_err

   dim sSQL as string
   dim sQryName as string
   dim vWhere as variant
   dim db as dao.database
   set db = currentdb
   vWhere = null
   sQryName = "My Query Name" ' change this to be the query that gets the data you want
   'add statements to change vWhere to be the criteria
   sSQL = db.QueryDefs(sQryName).SQL 'sQryName is the name of the query you want to change
   sSQL = GetSQL_WHERE(sSQL, Nz(vWhere, ""))
   With db
      .QueryDefs(sQryName).SQL = sSQL
      .QueryDefs.Refresh
   End With

Proc_Exit:
   On Error Resume Next
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   YOUR-PROCEDURENAME" 'substitute your procedure name

   Resume Proc_Exit
   Resume

Open in new window

then here is the code for GetSQL_WHERE:
Public Function GetSQL_WHERE( _
      ByVal pSql As String _
      , ByVal psWhere As String _
      , Optional pbooAdd As Boolean = False _
      , Optional booSubqueryHasWhere As Boolean = False _
      ) As String
'strive4peace
'add or replace criteria to/in the WHERE clause of an SQL string, if specified
'future: remove Where if not specified

'strive4peace ... 131204, 140120
'add criteria to the WHERE clause of an SQL string. Create if Where is not there.
'will FAIL if fieldname ends with 'where', 'group by', ' having', 'order by'
   On Error GoTo Proc_Err
   Dim iPos As Integer _
       , iPos2 As Integer

'   If Not Len(psWhere) > 0 Then
'      'no change
'      GetSQL_WHERE = pSQL   'same as what was sent
'      Exit Function
'   End If

   pSql = Trim(pSql)

   'look for WHERE
   iPos = InStr(pSql, "WHERE ")
   If booSubqueryHasWhere Then
      iPos = InStr(iPos + 1, pSql, "WHERE ")
   End If
   
   If iPos > 0 Then
      If pbooAdd Then
         'add to beginning of WHERE clause
         If Len(psWhere) > 0 Then
            pSql = Replace(pSql, "WHERE " _
                              , " WHERE (" & psWhere & ")" & " AND ")
         End If
      Else
         'replace WHERE clause
         iPos2 = InStr(iPos + 1, pSql, "GROUP BY ")
         If Not iPos2 > 0 Then
            iPos2 = InStr(iPos + 1, pSql, "HAVING ")
            If Not iPos2 > 0 Then
               iPos2 = InStr(iPos + 1, pSql, "ORDER BY ")
            End If
         End If
         If Not iPos2 > 0 Then
            iPos2 = Len(pSql)
            If Right(pSql, 1) = ";" Then
               iPos2 = iPos2 - 1
            End If
         End If
         If Len(psWhere) > 0 Then
            pSql = Left(pSql, iPos + 5) _
                        & psWhere & " " & Mid(pSql, iPos2)
         Else
            'remove WHERE clause
            pSql = Left(pSql, iPos - 1) _
                        & Mid(pSql, iPos2)
         End If
      End If
   Else
      If Len(psWhere) > 0 Then
         'create WHERE clause
         'look for GROUP BY
         If (InStr(pSql, "GROUP BY ")) > 0 Then
            'put before 'GROUP BY'
            pSql = Replace(pSql, "GROUP BY " _
                                 , " WHERE " & psWhere & " GROUP BY ")
            'look for HAVING
         ElseIf (InStr(pSql, "HAVING")) > 0 Then
            'put before 'Having'
            pSql = Replace(pSql, "HAVING " _
                                 , " WHERE " & psWhere & " HAVING ")
         Else
            'look for ORDER BY
            If (InStr(pSql, "ORDER BY ")) > 0 Then
               pSql = Replace(pSql, "ORDER BY " _
                                    , " WHERE " & psWhere & " ORDER BY ")
            Else
               'add to end
               iPos = Len(pSql)
               If InStr(pSql, ";") > 0 Then
                  pSql = Replace(pSql, ";", " WHERE " & psWhere & ";")
               End If
Debug.Print pSql
            End If
         End If
      End If
   End If
   GetSQL_WHERE = pSql
Proc_Exit:
      On Error Resume Next
      Exit Function

Proc_Err:
      '   MsgBox Err.Description, , _
          "ERROR " & Err.Number _
          & "   GetSQL_WHERE"
      GetSQL_WHERE = pSql
      Resume Proc_Exit
      Resume
End Function

Open in new window

basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html
this is the first video in a 3-lesson series
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 125 total points
ID: 41900823
Normally, you could count or sum the values in the detail section of  form or report by setting an unbound controls ControlSource to something like:

=Count([MyField])
or
=Sum([MyField])

Although I prefer Crystal's subreport method, another option would be to modify the above method and put the data in unbound textboxes, with the appropriate labels, then use a Sum( ) function call as the controlsource, something like:

=Sum(iif([SomeField] like "*Football*", 1, 0))
=Sum(iif([SomeField] like "*Soccer*", 1, 0))
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41901177
I would also use a subreport (the reference to my answer was for a different situation where there was only one count requested) but there  isn't any code required.  Just create a query of the query used as the recordSource for the report and select only the SportName and add a count.

Select SportName, Count(*) As SportCount
Group by SportName;

Then make a report based on this query and put it in the Report's ReportFooter section so it will appear only once on the last page of the report.  There are no master/child links since this is a recap report and isn't synchronized with a group in the main report.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now