Dave Thompson Consulting
Client Information System - Introduction
I am often asked: "What is an Access application?"; "What does a custom program look like?"
You don't always need an Access developer to assist in developing an application for your business. Since Access has been around since late 1992 (Access Version 1.0), there are a number of "How to .." Access books available. And there are plenty of half-day, or full-day, training courses for you or your staff.
Access is powerful, relational, multi-user database. Certainly you can use it to manage lists, and to develop, on your own, many straight forward custom applications. But when you have a business need for a complex business process, or information system, it may be time to use a professional developer to implement your business need as a custom application.
Client Information System Example
Clients as Individuals
Here is an example of a multi-user, client information application developed using MS Access. The program was originally written for a financial services firm.
In this application, the focal point is each client as an individual, primarily medical practicing professionals. The services firm develops and maintains a close relationship with each client. To facilitate this, the firm tracks an array of data elements (for each client) such as contact information, family member information, and outside advisors used by the client (attorneys, accountants, etc).
The firm's staff need to be able to retrieve this information quickly, as - for example - when one of their clients calls on the phone and wants to know something about their account.
Before implementing this client information system, incoming phone calls to the firm from clients often required the receptionist to write down the client's name and question, and promise a return phone call. With the new system, the receptionist can find the client in the database, and put them through to the appropriate financial planner on the first call.
When a financial planner gets the call, they can - in turn - look up the client's information and review/edit the various categories of information for the client.
Client Group Practices or Business Entities
Since many clients in the medical profession are in group practices, the database tracks group information and group members. The financial planner can look up other members of the caller's firm/practice who are also clients.
As developed, the application implemented Access workgroup security. The database administrator can assign data privileges to different user groups within the financial planning firm. For example, some groups can only view the data (and make no changes), while other user groups can edit the data. In most cases, when the data is edited, the program automatically records both the date/time of the change and the user making the change.
This background audit trail is automatic, and not user controlled.
NOTE: Microsoft has eliminated the Workgroup feature starting with Access 2007. If needed, security and logon ID's can be a part of the UI (user interface) and implemented through VBA programming. Also, the data file can encrptyed and protected with a password.
A split mdb model is used for the application with a front-end mdb (forms, reports, queries, and program code) and a back-end mdb (tables only - see Database Design below). When workgroup security is used, there is an additional workgroup security file (mdw).
Basic table structure. Click to enlarge (170KB)
Complete table structure. Click to enlarge (320KB)