Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How count number of unique values at end of report

Posted on 2016-11-23
5
Medium Priority
?
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

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 30

Expert Comment

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

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

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 500 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 39

Accepted Solution

by:
PatHartman earned 1000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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