Click any image to view a larger version ...
I created this form whilst working in a large UK secondary school to provide an effective way of identifying students who were in specified target groups so that appropriate action could be taken and the results analysed.
The student list can be sorted on any field by clicking on the field header. Click again to reverse the sort order.
The sort column is highlighted and the sort direction shown using up/down arrows
Reports can be created showing the target group either alphabetically or as displayed on the form
The report SQL and text is automatically updated to match the selections made
As each filter is applied, it is saved as a string. For example:
Private Sub cboGender_AfterUpdate()
If Nz(Me.cboGender, "") <> "" Then
strGender = " AND PupilData.Gender = '" & Me.cboGender & "'"
The GetRecordSource procedure combines the select string with all filters and the sort order to create the overall SQL string
Private Sub GetRecordSource()
strWhere = strWhere & strYear & strTG & strGender & strFSM & strCOP & strLowEn & strLowMa _
& strPupilPremium & strEAL & strFirstLang & strEthnicity & strLEACare
'combine with strSelect (set in Form_Load) and strOrderBy (depends on user choice)
strRecordSource = strSelect & " WHERE " & strWhere & " ORDER BY " & strOrderBy & ";"
Me.RecordSource = strRecordSource
CheckFilterFormat ‘used to add green shading to filtered fields
GetListTotal ‘shows the recordset count and a summary of the filters used
Click to download: Student Target Group Explorer v1.8
The underlying data can also be displayed. Filtered fields are displayed in BOLD RED text
Once again, the data can be sorted on any field by clicking the field header.
A report is available to print any of the bar charts from the form
The report SQL and text are automatically updated depending on the selections made
Click to download: Incident Analysis
UPDATED to version 1.8 - 09/01/2019
a) visual display of sort order using up/down arrows and highlighting of column headers (based on suggestions/code by Salvo Fricano)
b) fixed issues with sort order on report