---

I received a call from one of my co-workers in the UK the other day, looking for some help with a quick, down-and-dirty database to help him track company assets held by employees. Just something to track the details on all of the computer and peripherals, phones, cars, things like that. He already had it started, and just needed a little help getting the report built and formatted. So, being the nice guy I am, I say 'Sure, zip it up and send it to me, then I'll give you a call back'.

A minute later I had it opened up and was looking at the tables. He actually did a pretty good job, and had the different 'objects' in their own tables. He had a user table, a phone table, a car table, and an IT table. He had, however, fallen into a typical trap that novice database developers often fall into. He had set up a 'name' field in the user table, then had used that name field for the linking fields in the other tables. So what's wrong with that, you ask?

This is the fourth (and final) article in this series, in the first article I showed the data that we would be working with and gave an overview of the normalization process. In the second article I went through the process of actually pulling this duplicate data into its own table. In the third article I looked at how to add the new foreign key values to the original table, and then delete the original column that held the offending duplicate data. In this article I will create a permanent parent-child relationship between the two tables and explain referential integrity.

This is the third article in the series, in the first article I showed the data that we would be working with and gave an overview of the normalization process. In the second article I went through the process of actually pulling this duplicate data into its own table. In this article I will look at how to add the new foreign key values to the original table, and then delete the original column that held the offending duplicate data. In the final article I will create a permanent parent-child relationship between the two tables and explain referential integrity.

This is the second article in the series, in the first article I showed the data that we would be working with and gave an overview of the normalization process. In this article I will go through the process of actually pulling this duplicate data into its own table. In the next article I will look at how to add the new foreign key values to the original table, and then delete the original column that held the offending duplicate data. In the final article I will create a permanent parent-child relationship between the two tables and explain referential integrity.

Normalizing data. The mere mention causes database developers to sit up in their seats with ears perked and fingers poised over the keyboard, and sends the layman looking for a rock to crawl under. It really isn't rocket science. In its simplest form, the act of normalizing data simply means that you're going to take any items that are duplicated in a database table and move those items to their own table, and then create a relationship between the original table and that new table.

Earlier we looked at a simple way to split a field into two values in Access. In that post I mentioned that it is considerably easier to put fields together (concatenate) than it is to take a field apart. Let's take a quick look now at how to do that.

On reviewing my log files I see that there are quite a few people coming in from various search engines looking for a way to split a name field into a first name and last name using Access. I know myself that this is a common issue, as many times this wasn't given much forethought when the table was originally created. Unfortunately, there is no really simple way to do this, at least that I've ever found. Fortunately, it can be done, albeit with usually less than perfect results. If you haven't ventured here before, consider this a welcome to the world of VBA (Visual Basic for Applications).

Every table you create in any database should have a field by which every record in that table can be uniquely identified against any other record in that same table. Say what!!??? OK, I just made that up, but when you read things about primary keys or indexes in databases that is the kind of tech-talk that makes people walk away without even trying. It's really not that complicated, trust me.

OK, so you've opened up Access a couple of times, clicked on a couple of things, and closed it right down again. It's really hard to know where to start, and reading the help files and manuals don't really seem to help much. Well, let's fix that. We'll start by creating a really simple table, which is where the 'rubber meets the road' when it comes to databases.

A typical question I run into is 'Why should I use Access when I can do everything I want in Excel?'. The answer is never black and white, but each of these programs have been created to address specific types of problems. I find many cases where people have used Excel for something where they really should have used Access. This usually happens because people are familiar with Excel, but not quite so comfortable with using Access. This can get you into trouble, and you really should ask yourself some questions before you get started. Here are some of the items that go through my mind when I'm trying to help someone decide which of these application should be used: