isladogs
MVP / VIP
- Local time
- Today, 17:51
- Joined
- Jan 14, 2017
- Messages
- 18,769
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:
The GetRecordSource procedure combines the select string with all filters and the sort order to create the overall SQL string
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
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 SubThe 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 Sub2. 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
