Thinking Access

Monitor page
for changes

Advice on Designing Databases

A lot of complexity in forms, reports, queries, VBA programming, can be dispensed with if you just spend more time thinking of the design of the database before you start adding all those things. If you don't think out your design properly, you will suffer an amazing amount of work (and it still may not work right).

Remember:

Draw out an E-R (Entity-Relation) diagram on paper first, before you commit to making the tables in Access.

Tall and Narrow tables make for peformance efficiency and clean design, with ability to retrieve more stats using just queries and less VBA coding.

Each table should be 3rd Normal Form (3NF) with no interdependencies between columns. Also see DataBase Design Issues

Understand and use the features of Primary Key, Referential Integrity rules in the Access Relationships Window. Use the RI rules to work for you, not against you.

(Not) Partitioning your tables

Problem

There are so many incidences where people have gone and partitioned their tables and then found that they run into problems where reports and modifications have to be re-done over and over again for each clone.

Let's take a scenario.

You're compiling data on each branch. So you have branches called Melbourne, Sydney, Canberra and so on. Light bulb glows in your head - start off with one table and then clone that table for each branch. Ok, after months of work, you have now filled three tables. So you need to design a report or two, what could be simpler? So you design a report for Melbourne, finish that. What about Sydney and Canberra?

Well, the Melbourne report essentially works for the other states, except for a change of title. But you've painted yourself in a corner - how do you use the Melbourne report for Sydney? Ok, another light bulb, let's make a copy of the Melbourne report, call it rptSydney and we'll adjust the title and "Bob's your uncle". Well, not actually, because while we're at it, you just found this nice thing you could do on the Sydney report and then ok, we'll change the Melbourne report too.... What about the Canberra report?

See what I mean, you start doing extra work for no good reason.

The smart thing to do would have been to keep one report and just edit the Melbourne Report's Record Source to refer to the Sydney table or the Canberra table (remember to do the title as well).

Tip

The even smarter thing to do is NOT TO SPLIT your tables in the first place! Just simply add two extra columns to the one table. The first column is the StateID (which will identify the state for each record) and the second column is the StateTitle (which will hold the words to make up the report title for each state). Ensure that the StateID is the first column in your enlarged, Primary Key.

All you really need is one table, one report.

Expert User's Workaround

If you have gone and just done, it, created a table each for Melb, Sydney and Canberra, don't despair. Roll up your sleeves and use a UNION query to make it feel like you have one table for the purposes of review or reporting.