Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

This is the first in a series of examples showing different ways of filtering and grouping data in Access

 

This example focuses on the use of cascading combo boxes. It is an approach which is particularly useful where you have a very large dataset that would be impossibly slow to search using a single combo box or listbox.

 

For example, in my UK Postal Address Finder app (UKPAF), I have a table of 2.6 million postcodes.

 

For info, UK postcodes have 7 or 8 characters including a space

- Area e.g. HA

- District e.g. HA3

- Sector e.g. HA3 0

- Zone e.g. HA3 0S

- Postcode e.g. HA3 0SN

For anyone interested, additional info is provided in the attached PDF file

 

NOTE: All the above are official UK postcode terminology except zone (added here to assist with searching postcode sectors)

 

The normal postcode entry is a textbox. However, a postcode builder form is available when

you aren't sure which postcode to enter.

 

Searching a dataset of this size is obviously impossible using a single combo box. To make it manageable, the search is broken down using 5 cascading combos. The contents of each combo are dependant on the choices made in the previous combo which reduces the size of each list.

 

Each combo is based on a separate table filtered on the previous selection which makes it very fast. In addition, all fields used in the combo searches are indexed. This increases the database size BUT makes a dramatic difference to the search time needed

 

The attached example is a cut down version of the postcode builderused with the UKPAF application to make the file small enough to upload here. It only has data for two of the 125 postcode areas in the UK - Bristol (BS) & Kirkwall (KW). Inactive postcodes have been removed from the list. This reduces the total number of postcodes to around 28500 instead of the full data set of 2.6 million

 

It also contains an example postcode map.

Please keep this in the same folder as the database.

 

Click the OK button to start using the postcode builder.

A dropdown box appears with a list of postcode areas. Select an area from the list

 

Further dropdown lists appear in turn for district, sector and zone until you select a full postcode using the final combobox

 

In the full app, a Google static map  is automatically downloaded. This map is centred on the geo-coordinates for the selected postcode and can be zoomed in/out  

 

In the attached example, the mapping feature has been removed. Instead it just uses the same example map for each postcode

 

Click to download:        Postcode Builder Example

 

                                 UK Postcode Info    (PDF)

 

 

There is another example of the use of cascading combo boxes on the FMS website though I've had feedback that it has some flaws

     

Cascading Combo Boxes

Click any image to view a larger version ...

Screenshots

Return to Example Databases Page

Version 3.5      Updated 06/03/2019               Approx 4.4 MB   (zipped)

PostcodeBuilder1 PostcodeBuilder2 PostcodeBuilder3