Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

Multiple Group & Filter in Forms and Reports

Click any image to view a larger version ...

Return to Example Databases Page

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 & "'"

       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

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

StudentExplorer5

Click to download:             Student Target Group Explorer v1.8

Incident Analysis

IncidentAnalysis1 IncidentAnalysis2 IncidentAnalysis3 IncidentAnalysis4 IncidentAnalysis5

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

 

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

StudentExplorer1 StudentExplorer2 StudentExplorer3 StudentExplorer4