Dave Thompson Consulting
A List Navigation and Drill Down User Interface for MS Access
Many Access applications are employed in a multi-user environment on a local area network (LAN).
Performance is an issue. While a test application during development may run fine with a dozen or so records, projects should be designed considering the typical data load once the application is running in a full production environment where the client list - for example - may not be five test records, but several hundred or several thousand records.
An important determinate of performance is network traffic: how much data has to be moved between a client workstation (PC) and the server.
You will rarely find performance acceptable in the Access file server model where a user has to drag - for example - several thousand complete client records across the wire (from server to local PC) although the user needs to deal with just ONE record.
In many cases it is useful to retrieve a list containing the most used search fields, and then pull complete records only for the specific client, contact, or customer.
A database application - as used by end users - focuses on finding information quickly in order to:
These activities can be generally described as data lookup and maintenance. Of course there are other processes against the data such as reporting, archiving, exporting data for other uses, and others.
But on a daily basis, the functionality of lookup and add/change/delete are the most used.
Another characteristic of many - if not most - applications is the stability of the data design (you are not adding new tables, data fields, very often) and the need to deal with lists that change little on a day-to-day basis. For example, while you may add new contacts daily, you basic contact list doesn't vary significantly.
Similarly, your business customer list may not vary significantly. You may be entering new orders every day, but most of these are for existing customers.
Naturally, business applications that are heavily transaction-oriented (such a reservations system), or market to consumers (online sales, telephone sales, or other direct marketing activities), will be heavy on adding records and light on the lookup side.
Considering the case of frequent lookup and data editing, how you get the user to the correct record (client, contact) can be described as a navigation issue: allow the user to navigate to the correct record quickly using the most common data elements that the user may have such as customer number, a person's last name, or company name.
One method that is useful is to present the user with a list containing these most frequently available data elements, provide intuitive search tools, and once the correct entry is located, permit the user to drill down to a detailed form for that one specific client, contact, or customer
This article describes one way to implement a list with drill down. The example used is drawn from the client database example, described in more detail on this site (See Client Information System - Introduction).