Access – Creating Data Tables

If you read my earlier blog about database design http://wp.me/p2EAVc-bx , and working out how many tables you need in your database, then this is the next step.

Having thought about how many tables you need, and the data that each one will hold, you now need to think about building these tables in Access which is not as bad as it may first appear.

Going back to my training business database, I need to build a few basic tables to get me going;

• Topics
• Customers
• Contacts
• Training Jobs

The TOPICS table is the easiest so let’s start there.

All I need in this table is a topic ID number and the name of the topic. Again, if you are new to this sort of thing, plan it on paper first. Think up some short but meaningful names for your fields. You can add a more lengthy or descriptive caption if you want to afterwards.

So for this table is want;

• Topic_ID
• TopicName

I do a lot of VBA and have got into the habit of not having spaces in names and using camel hump text (capital at start of each word that makes up the field name), or if I do want one I use an underscore, but it is up to you how you name your fields.

Go to the CREATE tab and click on TABLE DESIGN.

Create a table using table design

You can go to TABLE but ultimately you will need to go into the deign area to set various options etc. so you may as well miss out the middle man and head straight for DESIGN view. Admittedly, this view does look a bit daunting when presented with a screen made up of several panels, each one seeming to contain more options than the last. But fear ye not!

Table options and properties…so many to choose from

The first thing to do is enter your field names. From time to time you may get a warning message telling you that the field name is a RESERVED word. You can ignore it, but this may cause issues later on Each FIELD must contain a single type of data. You need to think a little bit here about the DATA TYPE. Let’s look at a simple problem you may have to consider:

You might only need to register the year in one of your columns/fields. This is obviously a date, but if you use DATE as your DATA TYPE it won’t like it because dates are made up of days, months and years. If I want to do calculations based on my year then this may also cause issues, so in fact the best solution may be to store the year as a number. So do think about your data as you can only assign one DATA TYPE per column/field.

The data types you can assign are;

• Short text
• Long text
• Number
• Date/Time
• Currency
• Autonumber
• Yes/No
• OLE Object
• Hyperlink
• Attachment
• Calculated
• Look up wizard

Please note that these are all the available data types in Access 2013. If you are using an earlier version some of these will not be available.

For each data type you select, there’s a bunch of options or PROPERTIES that can be applied. I’m not going to go through each one but if you select a DATA TYPE, look at the bottom of the screen to the PROPERTIES window and look at what’s there.

Here are the properties for SHORT TEXT;

Short text properties

And another here for NUMBER;

Number data type properties

The PROPERTIES you set will be entirely dependent on your specific requirements, but here are a few fields that may be of some use generally;

• Caption – enter a sensible caption to display in the column heading or on forms etc. if you don’t want to display the field name you gave to your column.
• Default value – to save a bit of typing enter a value that is automatically displayed e.g. DATE() to show today’s date.
• Validation Rule – control what is entered in your database e.g. <=20000
• Validation Text – a message to the user in case they enter information that breaks your validation rule.
• Required – is the data here mandatory or not?

Format – pick a format from the list (this will change according to the data type chosen – see the options below for dates and currency)

Date format options

Currency format options

There are two options that do merit a little more detail, and they are FORMAT (when applied to numbers), and INPUT MASK.

Unfortunately, a number isn’t just a number…there are several types to choose from;

• Byte: whole numbers 0 to 255
• Integer: -32,767 to 32,768
• Long integer: -2,147,483,648 to 2,147,483,642
• Single: -3.402823e³⁸ to 3.402823e³⁸
• Double: -4.94e³⁰⁸ to 4.94e³⁰⁸

So if you are going to enter numbers think about whether it is a fraction (e.g. 2.545) and how big your numbers are going to get over time so you don’t have to go back every other week to change the size of the field.

An INPUT MASK is all about controlling how people enter information and then how it is saved and displayed. To see how these can help, let’s look at an example;

Wherever you are in the world you probably have a unique identifier against your name such as a national insurance number (UK) or social security number or similar. These numbers tend to come in a set format. Looking at the UK national insurance number, it is made up of capital letters and numbers;

AB 12 34 56 C

Asking users to enter their NI number is fraught with problems. You could get;

• ab123456c
• A B123 456 c
• A b 1 2 3 4 5 6 C

…or pretty much any combination of upper/lower case characters, spaces/no spaces etc. On the face of it, you’d think that was Ok but when you try filtering or running queries, searching for information becomes a real pain. To avoid all this, create an INPUT MASK that displays and saves the typed information in a pre-set format defined by you.

To create a mask you need to use specific characters to represent letters, numbers, spaces etc. and whether they are required or optional.

The table below shows some of the characters you can use to set up a mask;

Input Mask Character	Input Mask Function	Required/Optional
0	Digit 0-9 (+ or – not allowed)	Required
9	Digit 0-9 (+ or – not allowed)	Optional
L	Letter a-z	Required
?	Letter a-z	Optional
A	Letter or digit	Required
a	Letter or digit	Optional
&	Any character or space	Required
C	Any character or space	Optional
>	Force all characters to upper case from that point onwards
<	Force all characters to lower case from that point onwards
\	Any characters that follow are displayed as a literal e.g. LL\# would give ab#

Returning to our national insurance number, I would need to create the following mask to guarantee the data is displayed properly using upper case letters, spaces where required and mandatory numbers;

>LL\ 00\ 00\ 00\ L

However, there is still a problem. The table will display the data as per the INPUT MASK but in the background it will save it as it was typed in by the user. Knowing what users can do with your data, this is not a desirable situation to be in. To fix this, we need to add ;0 (semicolon followed by zero) to the end of the INPUT MASK. The mask would then look like this;

>LL\ 00\ 00\ 00\ L;0

Not only will the data be displayed as defined by the INPUT MASK, it will also save it that way too. Without this it will be very difficult if not impossible to find data as you would need to search for it the way it was entered.

As you can see, the building of tables can vary from very simple to quite complex depending on how much control you want to build into them.

The basic steps are then;

• Give each field a simple and meaningful name. Try to include at least one capital in the name. This will help when you move on to more complex stuff in Access, especially when working with VBA.
• Select a data type
• Select and edit whichever properties you need to help control what sort of data is entered and how

Remember to SAVE your table whenever you make design changes to it. You can name your tables any way you like, but there are a number of conventions which you can use. The most common one used in Access is probably the Leszynski naming convention, which is a variation of the Hungarian convention – if you are particularly interested in this then feel free to look it up, there’s loads out there. Basically, any object when named has a prefix to help identify the type of object. So a TABLE is usually prefixed by tbl, a QUERY by qry…etc. This is not absolutely necessary, but it does make life easier for you long term – create your own convention if you want to, but just make sure you are consistent! You may have a “Sales” table, a “Sales” query and a “Sales” report and even a field name, label etc. also called “Sales”, and differentiating between them can become tricky, especially if you progress on to VBA, so just be aware of the potential for issues.

So far then, we have discussed how to start designing a database ( http://wp.me/p2EAVc-bx ), and now we have looked at creating tables to store all our data. The next step in the process it to link all our tables together into a proper relational database.

Access – Planning & Designing a Database

The popularity of Access seems to go up and down on a regular basis. People tend to use Excel (albeit incorrectly) as a database tool as it is a relatively easy program to use, and most people are familiar with it so it is easy to create lists of information. Unfortunately, that is all it is…a list, not a fully functioning relational database. Excel uses formulas to create “links” which normally means VLOOKUPs galore. This is particularly inefficient as your spreadsheet will refresh all formulas each time you update the worksheet and as your data gets bigger this can take a long time to process.

Access on the other hand is designed for the very purpose of building databases. The downside is that it is not particularly intuitive – you can’t just open it and go unless you know what you are doing. In this series of blogs about Access I will cover;

• Database design & data normalisation
• Creating tables
• Linking tables & relationship types
• Creating queries
• Creating forms
• Creating reports

That’s not a definitive list but it covers the majority of what you will need to get creating and running a database.

First things first…designing your database.

Before you even open your PC or laptop, get a big sheet of paper and design your database there. This may seem like a backwards step but until you can visualise and create a database layout with all the necessary tables and links in your head…draw it on paper first!

But how do you go about designing a database?

First, think about how many tables you will need. Chances are if you are thinking in Excel terms you will create one massive table that contains everything. The biggest problem with this is duplication of data. You should be looking to create tables, where each one contains a unique set of related data, and each line in your table represents a unique record.

Let’s look at an example; I want to build a database to track my customers asking for training. At any customer site I deal with there will be one or more contacts that I deal with, perhaps in different departments, managers and staff etc. Problem is, that if I use a single table, each time I add a new contact I also have to add in all the details about the customer name, address etc. which results in a lot of data duplication. I could just enter the customer name and leave everything else blank, adding only the new contact information, but then how do I apply filters or run any look ups? It’s simply not going to work.

Identifying duplicated data

Wherever you see data duplication this is hint to create a separate table. This process is called DATA NORMALISATION. Continue doing this process until you remove all forms of duplication from your lists.

Now you’re thinking…I’ve created a bunch of separate tables, each containing unique data with nothing connecting them. At this point this is true, but this is where you need to create common fields between tables that are related to each other.

Continuing our example, we know that the contacts are related to the customers so we need to create a common field between the two tables. At this point think about which comes first – the customer or the contact? Can you have a contact without having a customer or vice versa? It is possible to know random people, but where did you meet them? At the pub, at work, at the gym? You both need something in common however brief to create the connection.

Bearing this in mind, the customer comes first in our example and contacts can share a customer name in common. We therefore need to add a new field in our contacts table that contains the customer ID. Each customer will have a unique ID reference or PRIMARY KEY field and this is what we will use.

This seems to go against having duplication in a table, but all you are duplicating (assuming there are multiple contacts at a customer site) is the ID number. No address information, or any other details relating to the customer are present in the contacts table. Unlike Excel there are no VLOOKUPs to refresh, there is simply a link between the two tables, which we create using the RELATIONSHIPS screen in our DATABASE TOOLS which I will describe in a later blog.

Once the relationship is created it will produce what is called a one to many relationship where the customer can only appear once in the customer table (the one side of the relationship), and each customer can have multiple contacts (the many side of the relationship).

So our database, in this case using only two tables, should look like this once in Access;

Tables within a database

As you continue to add tables, think about how each one is related to other tables in the database. Don’t become over-zealous though with your linking, trying to link every table to every other table. As long as you can access all tables following a “route”, no matter how convoluted, around your database, you can extract pretty much any data from any part of it.

Rough plan of database layout

Looking at a possible database design above, you can see that the TOPICS table is not directly linked to INVOICES, but there is one via TRAINING JOBS so I could check the number and value of invoices per topic covered. There is no direct link between EXPENSE TYPE and the CUSTOMER tables but because there is a link via EXPENSES > TRAINING JOBS > CUSTOMERS I can extract a report that shows me what type of expenses I tend to have with each customer and you can see how this works in my blog about building queries.

Now imagine the number of VLOOKUPs or INDEX and MATCH or INDIRECT functions I would need to create to try and connect all this data if it was in Excel!

One more thing you need to consider when thinking about the number of tables you require…sticking with our training database, how can I connect the TOPICS table with the CUSTOMER table? Should I even bother? After all, that’s what the customer wants – me to teach a particular topic or range of topics.

Let’s think about it…it’s possible that any customer could want training in a variety of topics. Bearing in mind that each customer appears only once in the CUSTOMER table giving us a one to many relationship (one customer – many possible topics). How about the topics I teach? Each topic could be requested by several customers. Once again, if we look at the tables, our topics appear only once in the TOPICS table which would give us a one to many relationship with the CUSTOMER table (one topic requested by many customers). But we know this is not possible as both the topics and customer names appear only once in their respective tables. The type of relationship we are trying to create here is a many to many type and this is not possible when linking two tables, each containing a primary key field or list of unique values.

If you come across this type of relationship you will need an extra or intermediate table that sits between the two tables. In reality this is an orders table, or something we refer to as a TRANSACTIONAL table.

If you work with an ERP system, just think about the screens you go into. You will have supplier lists, item masters etc. but where you enter an order on a supplier, it is on a completely different screen or form and therefore a separate table in the database. So this is a very common set up in databases…however big or small.

 

In the table above we can see that each customer appears several times, and the topics also appear several times, and in one case, Excel appears twice for the same customer. What is unique though, is the order number and that will provide me with a PRIMARY KEY in this table.

So now you have the basic principles of database design, and in particular thinking about the number of tables you might need and how to split the data to create tables containing unique record sets, removing all possible duplication through the process of data normalisation. At first this process is not obvious, but with practice you soon see patterns emerging in your data and from there can quickly identify the tables you need to create in your database.

In my next blog I will show you how to create your tables in Access.