Microsoft Access Database Design

Microsoft Access Database Design Concepts

A database is a collection of information organised and presented in a structured way.  There are many popular software applications which can be used to build databases from the humble spreadsheet to proper relational database management systems.

Data                           =Pieces of information.
Database                   =Information Source.

Reasons for using a database are:

·       Storing DataComputerised data takes up less space than its paper equivalent.
·       Finding DataLocating data by computer is very fast.
·       Sorting DataSorting and resorting data indifferent ways is achieved quickly and with little fuss.
·       Updating DataAs it is stored centrally, data need only be dealt with once.

Your Data will be organised, easy to maintain, easy to store and easy to retrieve

Types of Database Structures:

A Flat File Database – A simple table, consisting of categories (or fields) in columnar format and records entered in rows. Databases created in spreadsheet applications (e.g. Microsoft Excel) are flat file databases. An everyday example of a flat file or two-dimensional database is a telephone directory.

Relational – A relational database involves several tables. Data records can be extracted from several tables at the same time. Because these more advanced databases can involve multiple data tables they are sometimes referred to as three-dimensional databases.

Microsoft Access is a Relational Database Management System (RDBMS).

Flat file Database Vs Relational Database

Where a Flat File system deals with all the information on a single table a Relational Database System splits a broad range of information into several tables.  This means that data entry is quicker with less wasted effort.  Also ‘queries’ or methods of searching for specific data are much faster. The power of the relational database system lies in its ability to relate two or more tables together to retrieve and collate information.

Other differences are:

  • Because of data duplication for each record entered in a Flat File System.
  • There is much more wasted space.
  • There is more time spent on data entry.
  • Queries will run slightly quicker in a Flat File Database System.

Database Layout

In a Microsoft Access database, data is stored in the form of a table.  These consist of fields and records:

 First NameLast NameDate of Birth
RecordJohnDoe03 Nov 58
RecordPatrickDeer12 Jan 66
RecordAnthonyElk31 Jul 55
A Field is a piece of data of a specific type:e.g. First name or last name.
A Record is a collection of fields:e.g. information about a person.
A Database is a collection of records:e.g. your business card file.

The power of the relational database lies in its ability to relate two or more tables together to retrieve and collate information.

Microsoft Access Objects

A Microsoft Access Database uses the following objects which knit together to form the database:

Tables          Hold the information.

Queries        Manipulate the information and are used to extract data (find records)

Forms           For user input. These are easier on the eye than table grids.

Reports        To display the information in a presentable way and with totals etc.

Macros         To automate tasks.

Modules       For the programming of processes.

All of these can be designed by the user either from scratch or with the help of the sample templates.

Database Design

In a relational database, such as those created using Microsoft Access, information is stored about different subjects in separate tables.  To bring the information together in a meaningful way, Microsoft Access needs to be instructed about how the different subjects (tables) relate to each other.

Steps in Database Design

The Purpose of the Database and what the data is going to be used for.

  1. Decide what information is being stored in the database.
  2. Determine the tables.
  3. Divide the information into separate subjects, such as Customers or Products. Each subject will be a table in the database.
  4. Determine the fields.
  5. Decide what information you want to store in each table. Each field is displayed as a column in the table.  For example, one field in an Employees table could be First Name, another could be Start Date.
  6. Decide on the relationships between information.
  7. Look at each table and decide how the data in each table is related to the data in other tables in the database. Add the necessary fields to tables, or create new tables, if necessary.
  8. Determine the relationships between tables.
  9. Which field is the link.
  10. Refine the design. This comes after the database has been created, and the design errors have been analysed. This is done by creating the tables, entering a few records of sample data, and looking at the results, making any required adjustments, during the process.
  11. On the completion of steps1 to 5, the database can be created.

Creating the tables can be done in different ways:

  • Using the Table View.
  • Using the Table Design View
  • Using the SharePoint Lists

Microsoft Access Field Data Types

TextAny characters to a maximum of 255  
MemoLengthy combinations of characters.  Maximum size 64kbytes (about 2kbytes per page). (64000 characters) Cannot be indexed  
NumberNumeric values only  
Date/TimeDate and time values for years from 100 to 9999.  
CurrencyAccurate to 15 digits on the left side of the decimal point and 4 digits on the right.  
AutoNumberA number automatically incremented by Microsoft Access whenever a new record is added to a table.  
Yes/NoYes and No values (on off, true false)and fields that contain only one of two values.  Yes/No fields cannot be indexed.  
OLE ObjectAn object (such as an Excel spreadsheet, a Word document, graphics, sounds, or other binary data) linked to or embedded in an Microsoft Access table.  Can be up to 1 gigabyte.  
HyperlinkField that will store hyperlinks. A hyperlink can be a UNC path or a URL.  
Lookup WizardCreates a field that allows you to choose a value from another table or from a list of values using a combo box.  Choosing this option in the Data Type list starts the Lookup Wizard to define the data type.  

Our Microsoft Access training courses are scheduled regularly in Glasgow and Edinburgh, and we visit customers across Scotland.