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;
- 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;
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.
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!
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
- OLE Object
- 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;
And another here for NUMBER;
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)
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.402823e38 to 3.402823e38
- Double: -4.94e308 to 4.94e308
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;
- 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|
|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.