One of the major pit falls that new Access developers fall into is trying to create the database user interface first, then attempting to design the tables based on the interface they have created.

By this I mean a developer jumps straight into creating the forms that they require users to input data into with little or no consideration to the underlying tables.

In reality the reverse should be true, the developer should create the tables and then, from there, forms can be created for user input and reports for viewing the data.

The most important lesson to learn when developing Databases in Access is ALWAYS start by designing the tables. Tables are fundamental to your whole database system and a well-designed table structure will not only make your database more efficient it will also make the process of developing the user interface quicker and easier; In fact it is advisable that when starting a new database you work out the main table structure on paper before even turning your machine on.

Once you have your table structure it is time to start developing the user interface. The main aim for the Access developer is to create a system where the user cannot (and should not need to) enter data directly into a table or view data in a table; from the users point-of-view input should be via a form and viewing of data done via a report.

The diagram below is a simplified structure of an Access Database system; arrows denote data flow and the areas highlighted in blue should be restricted from the users.