Grouping & Filtering #3 – Multiple combos & option groups (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 18:00
Joined
Jan 14, 2017
Messages
18,186
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 & option groups with toggle buttons to filter and group data.

I have used the methods described here in numerous databases for different clients and the approach is easily ported to new situations

1. Student Target Group Explorer
As used in several UK secondary schools to identify students in specified target groups so that appropriate action could be taken and results analysed.
The dataset is for all students in a fictitious 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



NOTE: Apologies for the large size of the images in this post - they are all full screen

The student list can be sorted on any field by clicking on the field header.
Click again to reverse the sort order.

Reports are available showing the target group either alphabetically or as displayed on the form.

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()

[COLOR="darkgreen"]'build the where clause[/COLOR]
strWhere = strWhere & strYear & strTG & strGender & strFSM & strCOP & strLowEn & strLowMa _
    		& strPupilPremium & strEAL & strFirstLang & strEthnicity & strLEACare

[COLOR="darkgreen"]'build the full SQL string[/COLOR]
strRecordSource = strSelect & " WHERE " & strWhere & " ORDER BY " & strOrderBy & ";"

Me.RecordSource = strRecordSource
CheckFilterFormat[COLOR="darkgreen"] ‘used to add green shading to filtered fields[/COLOR]
Me.Requery
GetListTotal[COLOR="darkgreen"] ‘shows the recordset count and a summary of the filters used[/COLOR]

End Sub

2. Incident Analysis
This is a modified version of a form I created for a client working for 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 primary purpose is to show the data 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

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 Target Group Explorer to filter and group the data and 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

I can only upload 5 attachments with this post
For additional information & many more screenshots, see this link http://www.mendipdatasystems.co.uk/multiple-group-filter/4594454290

I hope this post is useful to others. Feedback welcomed
Any questions, please send me a private message
 

Attachments

  • IncidentAnalysis2.PNG
    IncidentAnalysis2.PNG
    44.5 KB · Views: 2,456
  • StudentExplorer3.PNG
    StudentExplorer3.PNG
    95.4 KB · Views: 3,020
  • StudentExplorer.zip
    252.3 KB · Views: 865
  • IncidentAnalysis.zip
    895.1 KB · Views: 887
  • StudentExplorer2.PNG
    StudentExplorer2.PNG
    60.4 KB · Views: 424

isladogs

MVP / VIP
Local time
Today, 18:00
Joined
Jan 14, 2017
Messages
18,186
Attached is an updated version (1.8) of the Student Target Grade Explorer showing the sort order on the column heading in a visual way. For example:





Many thanks to Salvatore Fricano for suggesting this enhancement and providing the code for me to include.

NOTE:
Unfortunately the use of the back color property means this version is for Access 2010 or later. Sorry Gasman!
 

Attachments

  • StudentExplorer_v1.8.zip
    222.3 KB · Views: 759
  • SortForename ASC.PNG
    SortForename ASC.PNG
    27.5 KB · Views: 1,875
  • SortLastNameDESC.PNG
    SortLastNameDESC.PNG
    23.8 KB · Views: 1,836
Last edited:

deletedT

Guest
Local time
Today, 18:00
Joined
Feb 2, 2019
Messages
1,218
How the backcolor of these textboxes are changed to blue?




thank you.
 

Attachments

  • 2019-03-19_11-11-17.jpg
    2019-03-19_11-11-17.jpg
    73.9 KB · Views: 1,708

isladogs

MVP / VIP
Local time
Today, 18:00
Joined
Jan 14, 2017
Messages
18,186
Conditional formatting.

Just a reminder to report any posts in sample databases / code repository as these are moderated areas. See sticky thread above for an explanation
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom