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 Data | Computerised data takes up less space than its paper equivalent. |
| · Finding Data | Locating data by computer is very fast. |
| · Sorting Data | Sorting and resorting data indifferent ways is achieved quickly and with little fuss. |
| · Updating Data | As 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.
- 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:
| Field | Field | Field | |
| First Name | Last Name | Date of Birth | |
| Record | John | Doe | 03 Nov 58 |
| Record | Patrick | Deer | 12 Jan 66 |
| Record | Anthony | Elk | 31 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.
- Decide what information is being stored in the database.
- Determine the tables.
- Divide the information into separate subjects, such as Customers or Products. Each subject will be a table in the database.
- Determine the fields.
- 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.
- Decide on the relationships between information.
- 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.
- Determine the relationships between tables.
- Which field is the link.
- 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.
- 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
| Text | Any characters to a maximum of 255 |
| Memo | Lengthy combinations of characters. Maximum size 64kbytes (about 2kbytes per page). (64000 characters) Cannot be indexed |
| Number | Numeric values only |
| Date/Time | Date and time values for years from 100 to 9999. |
| Currency | Accurate to 15 digits on the left side of the decimal point and 4 digits on the right. |
| AutoNumber | A number automatically incremented by Microsoft Access whenever a new record is added to a table. |
| Yes/No | Yes and No values (on off, true false)and fields that contain only one of two values. Yes/No fields cannot be indexed. |
| OLE Object | An 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. |
| Hyperlink | Field that will store hyperlinks. A hyperlink can be a UNC path or a URL. |
| Lookup Wizard | Creates 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.