Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 10-30-2018, 03:36 PM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Grouping & Filtering #3 – Multiple combos & option groups

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

'build the where clause
strWhere = strWhere & strYear & strTG & strGender & strFSM & strCOP & strLowEn & strLowMa _
    		& strPupilPremium & strEAL & strFirstLang & strEthnicity & strLEACare

'build the full SQL string
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
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/m...ter/4594454290

I hope this post is useful to others. Feedback welcomed
Any questions, please send me a private message
Attached Images
File Type: png IncidentAnalysis2.PNG (44.5 KB, 1037 views)
File Type: png StudentExplorer3.PNG (95.4 KB, 1303 views)
File Type: png StudentExplorer2.PNG (60.4 KB, 71 views)
Attached Files
File Type: zip StudentExplorer.zip (252.3 KB, 345 views)
File Type: zip IncidentAnalysis.zip (895.1 KB, 389 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
Tera (02-03-2019)
Old 02-01-2019, 07:32 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Grouping & Filtering #3 – Multiple combos & option groups

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!
Attached Images
File Type: png SortForename ASC.PNG (27.5 KB, 721 views)
File Type: png SortLastNameDESC.PNG (23.8 KB, 722 views)
Attached Files
File Type: zip StudentExplorer_v1.8.zip (222.3 KB, 200 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 02-01-2019 at 07:51 AM.
isladogs is offline  
The Following 2 Users Say Thank You to isladogs For This Useful Post:
MrHans (02-02-2019), Tera (02-03-2019)
Old 03-18-2019, 06:14 PM   #3
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 300
Thanks: 491
Thanked 22 Times in 21 Posts
Tera is on a distinguished road
Re: Grouping & Filtering #3 – Multiple combos & option groups

How the backcolor of these textboxes are changed to blue?




thank you.
Attached Images
File Type: jpg 2019-03-19_11-11-17.jpg (73.9 KB, 548 views)

Tera is offline  
Old 03-20-2019, 01:35 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,032
Thanks: 115
Thanked 3,017 Times in 2,744 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Grouping & Filtering #3 – Multiple combos & option groups

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
Tera (03-21-2019)
Closed Thread

Tags
combos , filter & group data , option groups

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Option Groups Required SoxPats83 Forms 9 07-02-2010 10:08 AM
Adding "All" option to Cascading Combos and Multiple combo box Filtering dkinnz Modules & VBA 1 02-27-2007 12:27 PM
multiple option groups.. keybearer Macros 1 07-12-2003 01:23 PM
Multiple option groups on form... Pangloss14 Forms 0 06-17-2003 11:47 PM
select multiple option groups with one button REDaughdril Forms 3 04-13-2003 08:17 AM




All times are GMT -8. The time now is 05:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World