Example Apps for Businesses, Schools & Developers

Click any image to view a larger version

Section Links:             Student Target Grade Explorer             Incident Analysis


This is the third in a series showing different ways of filtering and grouping data in Access.
It includes two different examples showing the use of multiple combo boxes and toggle buttons to filter and group data.

I have used the methods described here in numerous databases for different clients



Student Target Grade Explorer                                                                         Return To Top

Version 1.81     Updated 16 Feb 2022    Approx 1.5 MB (zipped)

This example database demonstrates how multiple filters can be applied to the data in a form & report.

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.

NOTE:
The dataset in the attached example is for fictitious students in an imaginary secondary school.

Up to 12 filters can be applied to gradually filter down the student list.
These include Year group / tutor group / gender / free school meals (FSM), SEN group, Key Stage 2 scores etc
The fields being filtered are shown in GREEN

Unfiltered Form

StudentExplorer1
Filtered Form

StudentExplorer2

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

Form Sort 1

StudentExplorer3
Form Sort 2

StudentExplorer4

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

Report

StudentExplorer5

As each filter is applied, it is saved as a string. For example:

CODE:

Private Sub cboGender_AfterUpdate()

    If Nz(Me.cboGender, "") <> "" Then
        strGender = " AND PupilData.Gender = '" & Me.cboGender & "'"
        GetRecordSource
    End If

End Sub



The GetRecordSource procedure combines the select string with all filters and the sort order to create the overall SQL string

CODE:

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
Me.Requery
GetListTotal     'shows the recordset count and a summary of the filters used

End Sub



UPDATED to version 1.81      16 Feb 2022     (ONLY for Access 2010 or later)

Changes include:
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
c)   modified API declarations in modResizeForm

Click to download:            Student Target Group Explorer v1.81


If you have Access 2007, please download this earlier version instead:

Click to download:            Student Target Group Explorer v1.7



Incident Analysis                                                                         Return To Top

Version 1.81          Updated 16 Feb 2022                    Approx 2.2 MB (zipped)

This is a modified version of a form I created for a client working for Transport for London.(TfL)
The form is used to analyse incidents of vandalism and graffiti on the London tube network.
All data has been changed for this example

The main purpose is to show a count of the filtered/unfiltered data for each group as a horizontal bar chart.
This is done using coloured rectangles directly on the form i.e. without using the Access chart object

First click one of the toggle buttons at the top of the form to group the data by one of the following:
      Time slot / Location / Day of Week / Month / Year

Next select the date range:
      Today / This Week / This Month / This Year / All Dates (the default)
Alternatively enter your own preferred range of dates

Using combo boxes, the data can also be filtered by one or more of the following:
      Time Slot / Location / Incident Type

The GetRecordSource procedure uses similar code to that in the Student Explorer example app to filter and group the data.

It then stores the aggregated ‘temporary’ data in two tables tblEventsTEMP/tblEventsTOTAL.

These tables are overwritten each time that new grouping or filtering is applied.

The DisplayChart procedure then creates horizontal bar charts based on the ‘temporary’ data.

Bar Chart 1

IncidentAnalysis1
Bar Chart 2

IncidentAnalysis2

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.

Filter Data

IncidentAnalysis3

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

Report - Example 1

IncidentAnalysis4
Report - Example 2

IncidentAnalysis5


Click to download:                   Incident Analysis v1.81



Colin Riddington           Mendip Data Systems                 Last Updated 16 Feb 2022

Return to Example Databases Page Return to Top