Designing an Access Application / Database

Essentially, a Database is a collection of data while an Application is a complete system of screens (to add, edit, retrieve, review, manipulate data), reports (to print out) and a navigation system (menus, buttons, switchboards).

Most Applications do need you to spend some time planning out the job before doing it. Typical steps are:

Do your research

1. Get enough Access knowledge. Enough is a hard thing to judge, but judge it you must. If you do not get enough, then you will probably be charging on ahead, breaking all the native behaviours and "free" facilities of Access and spending time and money re-inventing the wheel. And Access does not take kindly to someone overcoming it's natural behaviour in a brutal way so it tends to give you more undesired consequences.
   
2. Get enough business knowledge and rules. If your objective is fairly simple, then the business requirements will be intuitive. However, if the list of features that you want your application to perform is long and rich, then obviously, you have to detail and document each point.
   

Write the Specification down

1. Formally, put some sentences together describing the Objective. I find that people wriggle out of this one, saying that it's just trivial but it isn't. What happens is, if you make a long list of functions/features that the database is supposed to do, build your database and then get embroiled in heated arguments (sometimes in your own head) on what the database is supposed to serve anyway because one or more features conflict. An example is, whether you should store every receipt detail or just the total sales for the day. If you already have an accounting system that stores every receipt detail, then you might be just making a sales analysis Access system and not be interested in each single sale.
   
2. Write down the Functional Specification. Ensure that you get some real or sample data. Specify
   1. what will go into the database. A list of fields.
   2. what will be retrieved out of it. A list of fields. Additionally, you often find that the list of output fields may be different from the list of input fields. There may be some intermediate calculations, there may even be some aggregation calculations (roll ups) and there will often be some referencing of other lists. In which case, a list of formulae or processing sequences required to manipulate the data.
   3. a list of screens (some for data entry, some for review) - how the data is derived and a visual picture of each as well as any screen behaviour / interaction
   4. a list of reports, a visual layout of each report and how the data is derived.
   5. who will do what - supply the data, key it in, chase for it, print it out, view it on the screen, support other users. Who will back it up, maintain it for enhancements and bugs. Who will write the user documentation, the programmer documentation. Who will do the training, how much training.
   6. when does the job need to start
   7. how long will it continue for
   8. how much money and resources would you throw at it

Write down the Proposal

3. Design the whole model on paper. You would at least need an Entity-Relation diagram (E-R) and possibly a Data Flow Diagram (DFD).
   
4. Translate the E-R to reality - balance out what Access can do and what will cause Access problems
   
5. Specify the names, properties of tables and fields, reports and forms.
   

Review and discuss your Proposal with the Client

6. If this means you talk to yourself, so be it. But get approval for how the thing is supposed to hang together.
7. Decide when it is time to shut the door on any further changes - that can go into Version 2.

Start actual switch-on-the-PC work

8. Switch on the PC and design the Access objects, starting with the tables, relationships.
   
9. Test out the design and use it with sample data for a few days.

Review and discuss the prototype with the Client

10. Have a walk around, show it to people (tell them it's a draft). You may need to ensure that machines are set up properly and/or prepare the setup program.
    
11. Customise the design and keep changing it until you are satisfied. Get sign-offs from the requestors
    
12. Decide when to shut the door - that can go into Version 2.
    
13. Someone start writing the user documentation and rope in the people who will run and support the database.
    
14. Work on the production version for launch.
    
15. If you have not documented it by now, write the progammer's documentation.

Launch it

16. Prepare the users and the machines - training, machine, setup, documentation
    
17. Start using it.

Review and Bug Fix / Enhance

18. After a week or a month of use, review what needs to be fixed or improved and act on it.
    
19. If it needs a version 2, start the whole process again.