This is the place to get the cure.
I’m going to provide a Microsoft Access tutorial on how to create and use tables.
I’ll be following that up with posts on queries, forms, reports and automation.
First off, you already know how to use a spreadsheet.
probably Excel, but it really doesn’t matter.
Secondly, you’re free to relax a little even if you have a different version of Access.
You might need to hunt around for a few things, but mostly it’s all the same.
Just to get the ball rolling, take a look at this spreadsheet.
That’s a pretty typical list of items in Excel.
Hopefully you’ll have some data of your own that you’re able to experiment with.
A practical example is always easier.
That’s one of the things about databases.
It’s much harder to deal with inconsistencies than it is in a spreadsheet.
There are a couple of examples of things in here that you would need to fix.
For starters, you really ought to read some of these books, but that won’t help.
Every column in Access needs to be consistent.
Same problem with the " x' in theRatingcolumn.
Note that in most cases having the cells blank is just fine.
Okay, let’s start Access, and start making decisions.
Two specific things before you’ve got the option to get started with tables.
First, choose to create a blank database rather than using any of the pre-built templates.
Come back and check those out later for more examples.
Secondly, it’s crucial that you choose a name for your Access project at the beginning.
So click onBlank Database, give the file a name, and click onCreate.
In our case though, we don’t want to do that.
We want to design the table properly.
Here’s the thing to remember.
It’s cumbersome to change your mind afterwards.
If you’re using my example, fill things out so they look like this.
Access is a relational database.
One of the aims of relational databases is to minimise both repetition and wasted space.
I’m not going to go into the theory, but this is callednormalisation.
There are many other places to research the topic.
I’m just going to be practical and show you an example.
Notice that each of the classic sci-fi authors is mentioned a number of times in the spreadsheet.
That way each author only exists once, and the opportunity for errors is greatly reduced.
For the moment, accept my request that you create the author field as aNumber.
The quickest way is to tap on theSavebutton in the toolbar.
That might not be the easiest thing to find.
You’ll be asked for a name for the table.Bookmight be a simple option.
The table names are not file names.
They are just components in the database calledClassic Sci-fi.
Think of multiple sheets in an Excel workbook.
Just take my word for this one.
The easiest way to do this is to add another field just for the job.
That’s normally referred to as theIDfield.
Don’t worry about it.
Before we can start using the tables, we need torelatethem to each other.
select theDatabase Toolstab, and then click theRelationshipsbutton.
Add both of the tables using theShow Tablesdialog.
terminate the dialog when you’ve finished, and take a break for a moment.
Tables in Access generally relate in special ways.
The most common of these is what is called amany to onerelationship.
Access asks you for some details for the relationship.
The only change you’re gonna wanna make is to tick theEnforce Referential Integritybox.
end the relationships window, and save the changes.
Sensibly enough, this is called alookup.
Fill out the fields as noted.
shut the table, save it, and reopen it in theDatasheetview again.
And that’s it.
More on that in the next few posts.
