When it comes to systems and applications less is more. If you find your business increasingly relying on a satellite system of spreadsheets and access databases then it really is time to review your strategy and your core systems. On a current project I've begun to feel like Copernicus gazing into the sky, except instead of stars I'm gazing out at a huge constellation of spreadsheets. In fact. there are so many, that the core system, Oracle,  is almost completely obscured from view.

Excel is great for data analysis, but it isn't the best for collecting data. Microsoft Access, on the other hand,  is a step up the ladder. You can design a better user interface and exert more control over the data entry process and consequently on the output in your reports. Although outside the scope of my remit I decided to help one group of users by replacing their shared spreadsheet with an access database - doing the work on my own time, and emphasising it was only a temporary solution.

Access, being a relational database, means your data entry screens may pull data from several tables and may only add the data to one. In building a screen that does this you might like some of your form fields to depend on the result selected in another field. This type of chained select process is very common on web forms using Javascript but it is as equally achievable in Access.

Lets have a look at a very simple database which we will call "customer". We will set up three tables.

Table 1 is called check and is used to list the types of check that will be performed on a customer.

Table 2 is related to table 1. It is a list of the possible outcomes of the check applied. Therefore it includes a foreign key (fkey) which is the index from table 1. In the example below we can see that the first three rows all relate to the credit check in table 1.

Table 3 will be the main table use to input data from a form. To keep things simple for our example this table includes the customer name as a free text field. In practice this would actually be a key to a separate customer table.

You can set these tables up in Access by using the Create table by entering data menu option. The three table are related to each other as follows.

We are now ready to create the user form, which we will name update, to input the data. Using the Create Form in Design view menu option create a form with four fields. The fourth field will contain the date time stamp which we will hide from view.

1. Customer name. Text box
2. CheckType. List box
3. CheckResult. List box
4. DateTime. Text box

To create the chained select between the two list boxes we need to do the following. Still in design view, view the properties of the CheckType list box. In the data tab we will input a SQL query in the row source as follows

Next in the event tab we need to add an event procedure to the After Update field. Click the first button at the end of the line and select [Event Procedure]. Then click the second button which will take you to a VB (visual basic) screen. Add the following code and save.

Basically here we are saying after you make a selection in the check type field, i.e. select whether the check type was a credit or a technical check, then rerun the SQL query in the check result field.

In the properties of the check result field add the following SQL query

In summary, the chained select is obtained by using the selection in the first field as a parameter in the SQL query in the second field, and by re-querying the SQL in the second field when a selection is made in the first.