Access Database Basic Design

Microsoft Access is a database program which allows you to store data in a controlled environment

It will also allow you to edit and view the data in different ways. For day-to-day use, you normally store all your data into one single database, however there are some reasons why you may have more than one.

For example you want to have a “testing” database where you can try out something without potentially damaging the real data. Or sometimes you need to use other programs which use their own databases (for example accounting software, CRM programs, etc). In these cases, you can often link databases together so that you can access all the data from one database in another which makes it easier to work with all the data at the same time.

Tables

In Access, data which can be grouped together is normally stored in a “table”. For example you might have a table for customer orders, a different table for customer addresses and another table for employee information.

Example of how tables are listed in Access

When naming a table, it is a normal convention to start the name of the table with a few letters which indicate that this is a table (eg “tb” or “t” or “tbl” for example). This will make it easier later to recognise a table if it is used in any code we write.

Fields

In each Access table, the data you want to store is broken down into fields. So for a “customer details” table you might have a field for the customer ID number, another field for the customer forename, another for the customer surname, a customer title field and so on. Notice we split the customers name into two fields, one for the forename and one for the surname (we might also have one for the middle name if we wanted). This is because we might want to use the customers forename or surname independently from each other. For example if we sent a letter we might want to say “Dear Mr Smith”, using just their surname. Alternatively if we sent an email we might want to use “Hello John” instead. By having these two fields rather than one combined 

Alternatively if we sent an email we might want to use “Hello John” instead. By having these two fields rather than one combined field, it becomes a lot easier to do the above. If we stored the name as one field, we would need a method to split out the forename or surname, introducing complexity and slowing down any process which needed to do this.

Example of Fields in an Access table

When we create a field, we also need to specify the type of data the field will store (the “Data Type” column in the above example). Normally we would be storing either a number, some text or a date although for each of these we will actually need to be a bit more specific when saying what we are storing in a field.

For example for “numbers” we will need to say if the data we are storing will have decimals or not and it does not have any decimals, is it a big number or a small number.

Example of ‘number’ field sizes in Access

For example if we say we are storing a “Byte”, this means we are storing numbers which can only range from 0 to 255 (for example someone’s age in years can be stored as a byte). Negatives are not allowed.

If we use an “Integer” then the numbers we store must be between -32,768 and +32,767

For a “Long Integer” the numbers can go from -2,147,483,648 to +2,147,483,647

If we need to store numbers with decimals then the “Single” and “Doubles” types allow this.

Why have so many different types? Each type uses up a different amount space and calculations may take longer to perform with some of the larger types (although modern computers are very fast so this is not a major concern for smaller databases).

For example a number stored as a “double” takes up 8 times the amount of space compared to the same number as a byte (but remember we are limited to a number which ranges from 0 to 255 if we use a byte).

NB Other databases may use a different name for the same field type found in Access. For example the “Double” type in Access is called “Float” in a SQL server database.

Records

A record is simply one “row” of data. So in the case of the “customer details” table, a record would be the data for one customer which would include their name, customer ID, title, etc.

Example of a record in a table

Joining Tables

Imagine we have a table for “customer details” (eg name, customer id, etc) and another table for “customer orders” (eg order number, item code, quantity, etc). If we wanted to display information from both tables (for example we wanted a list of orders from the “customer order” table and we wanted to see the name of the customer who made those orders from the “customer details” table) then we would need a way to link the tables together. In order to do this, one field in each table must contain the same information and for one of the tables, the data in the field should be unique for each record.

Example of tables which can be linked in Access

In the above example we have a list of “households” which contains address information. This can be linked on the “MemberHousehold_ID” field to another field in another table (“MemberHousehold_IDLnk”) which holds details of all the people living at that address. Notice the field names in each table do not have to be spelt the same.

This link will allow us to do things such as running a report for each household and listing all members of that house against each household.

Sometimes when we join tables together, there may be data in one table, however when we link it to another table, there may not be a matching link. What gets reported back to use depends on the type of link we use.

Inner Join

When data is retrieved, only get information if the join exists in both tables. For example if we have the ID numbers 39 and 40 in one table, but in the second table we only had records with a matching ID of 39 and nothing for 40, then the data we get back will only display for record 39 and we will get nothing for record 40.

In the above example, if we joined the tables using an inner join on the “ID” field in the “tb_Names” table to the “SoldBy” field in the “tb_Sales” table, only Tom and Dick would be returned in any data we requested.

Outer Join

If instead we used an Outer Join in the above then we would get data from a table, even if there was no match. For Outer Joins we must additionally specify which table we want to retrieve data from when there is no data in the other table using the “Left” and “Right” command.

Left Outer Join

In the above example we have used a “Left Outer Join” and this tells Access to retrieve ALL records from the “tb_Names” table and the matching records from the “tb_Sales” table. If there are no invoice records to display (such as is the case for Harry), the NULL identifier is used.

Right Outer Join

With a “Right Outer Join”, ALL data is returned from the “tb_Sales” table and any matching data from the “tb_names” table. If there is no match then the “NULL” identifier is used. So in the above example all the Invoice numbers are displayed but only “Tom” and “Dick” are shown, as invoices 6 and 7 were sold by someone with a SoldBy id of “4”, but this ID number is not in the “tb_Names” table. 

NB in real life this would not be a good situation to be in, as we seem to be recording sales against someone with an ID number of 4, however we have no record of who this person is in the “tb_Names” table. This would need to be investigated to see if the invoice actually had the correct “SoldBy” number or if we needed to update the “tb_Names” table with someone with an ID of 4.

Normalisation

One very important process in any database design is normalisation. This process can help with database speed, reduce errors and make things easier when handling data. In principle it is quite simple to implement. Where we have any field which uses repeated data, we can replace the repeated data with the same ID number and store this ID number in a table which describes the value it represents.

As an example, lets look at the following table of data:

The above table of 7 employees has four columns and we can see two of them have data which is repeated – “Gender” and “Department”.

These columns can be potentially “normalised” and to do this we need to create a new table for each of them as follows:

All we have done in the above is take the unique values for the Gender and Department fields and stored them in their own tables and given them a unique ID.

The next step is to replace the values in the Employee table with these values, so we now get a table which looks like this:

Whilst this does mean it is harder to see what the data actually represents if we just look at this table (as we need to look in another table to find what the ID number is linked to), when creating reports we can simply link this table to the Gender and Department table to retrieve what the actual values are. Additionally we can add a number of fields to the Gender and Department tables to store and use other information.

For example if we were displaying reports which required the gender to be displayed, we may want to show the letter (ie M or F). However in some cases we may want to display the full word (eg Male or Female). If the table was NOT normalised in the gender column, we would either have to create another column with the full name or write some logic to detect the value in the gender column and display the full name accordingly. However as we HAVE normalised the gender column, we can simply add a new column to this table and reference this column when we link the two tables together.

Example of an expanded table with the full gender name

Lets now consider what would happen if we needed to change a normalised piece of data. In our example, if the Department column was NOT normalised and the company decided to rename the “Sales” department to “Sales and Service”, we would have to go through each record and update each one to the new name (or write a command to do this for us).

Result of updating the table manually

As you can see we have had to update each record which, if we had 100’s or 1000’s of records would be time consuming to do manually. We could write a command to do this for us but this adds a level of complexity we do not need to do.

However if the data is normalised then we do not need to touch the above data at all. All we simply do is change the single entry in the normalised table and when we link the tables, the new value will be used instead.

Result of updating the normalised table

This is much simpler and quicker to do, reducing the potential for mistakes.

NB for the Gender field we have replaced the letters M and F with the ID numbers 1 and 2. Arguably we don’t actually need to do that in this case and we could create the normalised table using ID’s or M and F as shown below and leave the letters M and F in the original Employee table.

In small databases and using modern computers where speed and storage space is not so much an issue, the above would work fine (although it some would consider using a numeric ID to be standard practise in database design).

Normalising a normalised table

It is possible to normalise a table which is itself used for normalisation. Consider this table of people and where they live:

We could normalise it like this:

However there is room for improvement whereby we can reduce the amount of data we store and make the database more efficient.

As each city will be in only one country (ie London will only ever be in the UK), we can change our tables to this:

Which as you can see has reduced the amount of data stored. We simply store the city value in the “tb_Employee” table and we can look up the country when we link this table to the “tb_City” table. But we can go further than this. If you notice in the “tb_City” table, we can normalise the “Country” column as this contains repeated data itself.

So when getting data from the above we would link the tb_Employee table to the tb_City table using the City ID and we would also link the tb_City table to the tb_Country table using the Country ID.

For further Access help or development please contact us.

The contact form requires that you configure reCAPTCHA in the site configuration.

The contact form requires that you select an email template.