Solved

How count number of unique values at end of report

Posted on 2016-11-23
5
43 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 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 28

Expert Comment

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

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

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 35

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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