Some years ago when I worked in the print department of a busy mailing operation, I had to print names and addresses from databases onto thousands of publications of various kinds.
This position came with considerable responsibility and I paid meticulous attention to each task.

Before I set the printer away I would run off about 15 copies to check that all was well; that the print was legible and the positioning of the address was within the white rectangle allocated for that purpose. I would only begin the print run proper when I was satisfied that everything was in order. A fellow, less painstaking, employee was responsible for twelve thousand magazines ending up in the skip.

This common sense approach should be made when you are designing a new database in Access. After all, you do not want to discover that there are faults within the database design when you have gone live with it. These are best found out before the database is operational.

So with careful planning you should be able to avoid some of the common pitfalls that beset many who attempt to create a database on the hoof. For example, a lot of unnecessary typing can be saved with a little forethought and making use of relationships. Data entry can be a monotonous task. It is senseless, therefore, to give yourself extra work by entering data more than once. The use of table relationships can do away with this.

To illustrate this, allow me to refer to another of my former jobs, this time as manager of a video rental store. When new titles came in they were logged on the database. We had three main suppliers, and the name of the supplier was logged with the other details. There was no point, however, in including the supplier's details in the titles table, as this would be making unnecessary entries. It made more sense to have a separate table for all of our suppliers, and this information would be entered just once.

To create a Supplier table, I go to Create on the ribbon, and in Design View, I create the fields for my new table. These include contact details of each supplier; all of the information that was deemed unnecessary in the Titles table. The first field of this table is the primary key, and it is simply called ID. These are numbered 1, 2 and 3.

Having created a separate database with details of the three suppliers, I set about creating a relationship between that table and the Titles one. I open my Titles table in Design View and add a new field called Supplier ID, selecting the data type Number so that this will match the Supplier ID in my other database.

With both of my databases saved, I click on the Database Tools ribbon and then on Relationships. This will bring up the Show Table dialog box, with both of my tables inside. Holding down the Ctrl key, I select both and click on Add. I can now close the dialog box and I am left with my two tables in the Relationships screen.

To create the relationship I simply click on the field that will be one part of the relationship, in this case the Supplier ID within the Titles database, and drag it across to the Supplier table, dropping it in the ID field. An Edit Relationships box will appear to confirm the proposed relationship. In this I check the Enforce Referential Integrity box and click on Create.

This will now show my two tables as connected, with the connecting line coming from ID in the Supplier table, and going into the Supplier ID in the Title table. Figures on the line show this to be a one-to-many relationship in that one supplier can supply many titles, but one title cannot have multiple suppliers.

I now open my Titles database again, and in the newly created Supplier ID field, I enter the ID of each of the suppliers from my Supplier database. This is a number, either 1, 2 or 3. And that is it; my relationship is complete.

But what about this referential Integrity - where does that come in?

Well, as sometimes happens, a young scallywag comes into the shop offering to sell a selection of his own videos. I decide to buy them but, as these videos have not come from a recognised supplier, I have a problem entering the details in the Title database. I could try to enter the new videos by giving them the brand new supplier ID of 4, but Access will not allow this. As the Supplier ID 4 does not exist in my Suppliers table, I would get an error message informing me that "You cannot add or change a record because a related record is required in table Supplier". This is referential integrity and it prevents invalid data entering the database. The only solution would be to create a new supplier in the Supplier table.

So by simple preparation your database can be free of unnecessary entries and secured against invalid data. It is true that the more care you take over planning your Access database, the more likely it is to be free of problems.