Solved Using a control on a form to display the most frequently occurring value in a field

[csbrien]

I have a continuous form, frmPoints, based on a query, qryPoints. The purpose of the form is to view, add, edit, or delete "points" given to students. The points can be either "Merits" or "Infractions" (the points are stored in tblPoints and the types in tblPointTypes). The form can be filtered in a number of ways using buttons in the header that run DoCmd.RunCommand acCmdFilterMenu on the click event.

I have a text field called Reasons with values that come from a table tblReasons. For every record in tblPoints (when merits or infractions are given), there is a corresponding Reason (ReasonID in tblPoints). In the design of this database, I've included some aggregate functions in the footer of each form for quick reference. There are separate reports for more detailed analysis. I'd like two controls in the footer of frmPoints that displays the most frequent Reason (or a list of reasons sorted by frequency) for Merits and Infractions, respectively, for however the form is currently filtered.

I have been unable to figure out how to do this despite several hours of searching and experimenting. I originally tried a text box with various expressions involving Iif, Count, Dcount, Sum, etc., but to no avail. I did find a way to count the frequencies of all field values using a query with 'Group By' and 'Count' (I made a link to the webpage where I found that method, but it was flagged as spam, so I've removed it). This led me to trying a list box instead, but if I use the query as the row source of a list box, it can only show the frequencies for the entire unfiltered table/query. Below is the SQL for the query that totals merit reasons:

SELECT tblPointReasons.Reason, Count(tblPointReasons.Reason) AS CountOfReason, tblPointTypes.Type
FROM tblPointTypes INNER JOIN (tblPointReasons INNER JOIN qryPoints ON tblPointReasons.ID = qryPoints.ReasonID) ON tblPointTypes.ID = tblPointReasons.TypeID
GROUP BY tblPointReasons.Reason, tblPointTypes.Type
HAVING (((tblPointTypes.Type)="Merit"));

I need the control to update whenever the form is filtered, for example, by class or grade. I'm not sure if there's a way to reference the form's recordset or add the form's filter in the row source of the listbox. Is there any way to solve this using the control properties window? Or would I need to use VBA? Any help would be greatly appreciated. I'm happy to provide additional information if this is not enough.

Warm regards,
Chris

 

[June7]

Yes, would need VBA to alter the listbox RowSource SQL with the same filter criteria.

For info on using WHERE and HAVING in aggregate query, review http://allenbrowne.com/QueryPerfIssue.html#WhereVsHaving

 

[csbrien]

Yes, would need VBA to alter the listbox RowSource SQL with the same filter criteria.

For info on using WHERE and HAVING in aggregate query, review ....

Thank you for the reply. Using your suggestion, I was able to solve the problem by including VBA in the form's 'On Apply Filter' event that adds the form's filter to the listbox RowSource SQL:

    If Me.Filter <> "" Then
        strFilter = "AND " & Me.Filter
    Else
        strFilter = ""
    End If
       
    Me.lstMeritFrequencies.RowSource = "SELECT tblPointReasons.Reason, Count(tblPointReasons.Reason) AS CountOfReason, tblPointTypes.Type " & _
        "FROM tblPointTypes INNER JOIN (tblPointReasons INNER JOIN qryPoints ON tblPointReasons.ID = qryPoints.ReasonID) ON tblPointTypes.ID = tblPointReasons.TypeID " & _
        "WHERE tblPointTypes.Type = 'Merit' " & strFilter & " GROUP BY tblPointReasons.Reason, tblPointTypes.Type ORDER BY Count(tblPointReasons.Reason) DESC;"

Thanks also for pointing me toward a better way to structure the query using WHERE and HAVING. I've incorporated that into my SQL.

This now gives me a list box sorted such that the most frequent reason is on top, an acceptable solution. I'd be curious if there were a way to just have the top reason displayed, but it's not a major priority at this point. Thank again and best wishes.

 

[June7]

Explore SELECT TOP N

http://allenbrowne.com/subquery-01.html#TopN

 

[Pat Hartman]

Open the query in design view and add an order by "Desc" to the count field.

 

[June7]

Open the query in design view and add an order by "Desc" to the count field.

Looks like they already did that.

 

[csbrien]

Explore SELECT TOP N

...

Thanks for taking the time to help. I have marked this as solved. Select Top N does exactly what I needed, and even preserves ties, which works well in my situation. I found I had to account for users filtering the form on the reason type field (done by a selection in a combo box, cboType). In that case, DoCmd.RunCommand acCmdFilterMenu adds a lookup field to the form filter that doesn't apply to the list box row source. The Replace function came to the rescue by substituting the correct table name. That code, plus the query for the top merit reason, are below:

    If Me.Filter <> "" Then
        strFilter = Me.Filter
        strMatch = "[Lookup_cboType].[Type]"
        If InStr(Me.Filter, strMatch) > 0 Then
            strFilter = Replace(Me.Filter, strMatch, "[tblPointTypes].[Type]")
        End If
        strFilter = "AND " & strFilter
    Else
        strFilter = ""
    End If

    Me.lstMeritFrequencies.RowSource = "SELECT TOP 1 tblPointReasons.Reason, Count(tblPointReasons.Reason) AS CountOfReason, tblPointTypes.Type " & _
        "FROM tblPointTypes INNER JOIN (tblPointReasons INNER JOIN qryPoints ON tblPointReasons.ID = qryPoints.ReasonID) ON tblPointTypes.ID = tblPointReasons.TypeID " & _
        "WHERE tblPointTypes.Type = 'Merit' " & strFilter & " GROUP BY tblPointReasons.Reason, tblPointTypes.Type ORDER BY Count(tblPointReasons.Reason) DESC;"

 

[csbrien]

Open the query in design view and add an order by "Desc" to the count field.

Thanks, Pat. Using DESC worked in getting the most common reason to the top of the list so it's displayed by default, but the list box is only one row in height (I'm trying to preserve screen real estate for the table data.). If I keep the entire list in there, it can only be navigated with the tiny scroll bar, which doesn't feel like a good design to me. SELECT TOP 1 was a good compromise because it displays like a text box when there's only one top reason and only adds the scroll bar if there's a tie, allowing the user to see all the top reasons.

 

[Pat Hartman]

You could use a combo box rather than the listbox. That will work better since you only want to show the top reason anyway.

Also, since you have the counts in your query, you could also show the count. It will show automatically with the listbox as long as it is wide enough but with the combo, you would need to add a calculated column where you concatenate the text with a couple dashes and spaces and end with the count. If you make that the second column of the Rowsource and remove the plain description field, you won't have to change anything.

There are even ways to get the counts to right justify if you are obsessed with this type of neatness