Data Types, Properties, and Relationships in Access

A fundamental aspect of Access databases is the setup of relationships between tables. It’s usually the nature of business databases to have them, so the actual creation of the relationship isn’t hard to do. But there are a couple of not-so-obvious things the user needs to know.

Relationships Window

Once the database is open, and the tables have been created, the first thing is to open the Relationships window from the Database Tools tab–>Relationships group. If there are no relationships already in place, the Show Table dialog box will open automatically. The user can then double-click the tables needed, or select them and click Add, which places them in the main window.

Table Hookup

Then, dragging from a field in one table to its counterpart in the other will establish the relationship. (We usually check Enforce Referential Integrity, as we normally want something in one table to have a counterpart in the other.) Click Create, and the basic hookup is done.

The background behind this is a little more subtle, but not that hard to understand. Some newer users think the field names have to match for the relationship to work, but this is not true. The program doesn’t require it, but it’s highly recommended that field names be consistent across tables, so anyone who needs to look at or use the database won’t get confused.

The main thing, or rather things, that need to match up, are two of the properties of the fields—specifically, the Data Type and Field Length. These can be easily checked, or modified, by going into Design View once a table is open and selecting the field in question.

Data Type

The reason is, the program can’t assume what will be stored in a given type of field—say, some numbers—will work with something in another field type. If the types match, they should. And if the lengths are the same, fifty characters for example, then what’s in one field can fit in the other.

Field Length

One analogy which I’ve heard, which seems to help explain this, is the idea of two railroad cars. In different countries, one sometimes finds the rails are different distances apart—in the US, it’s four feet, eight and one-half inches. If you want to couple two cars together, they have to ride on the same gauge (distance apart) of rails, and they have to have the same couplers. If these two things are correct, all is well. One might also think about a car—if it’s supposed to run on diesel, you shouldn’t put gasoline in the tank, and vice versa.

So, field names should match, but data type and length have to. Simple enough, once you know it.

How to Normalize Data in Microsoft Access

The term “normalization” gets thrown about quite a bit in database circles, especially to try to explain to newer users a couple of principles of data organization, but seems rather vague to someone not acquainted with database-ese.

The idea of making data “normal” is not too far from the meaning used by database designers, when one understands what “normal” is. When we create, say, a batch of file cards with names, addresses, etc., we tend to lay out the information similarly—consistently—so as to make it easier to follow on each. If each piece of information is in its own place, a “field” or space designated for each, we’re already close to the idea of normalization. One wouldn’t want to put the first and last name in the same field, since we might want to sort by last name first. We couldn’t do it easily if the two were in the same place. Nor would we want to put street address, city, state, and zip code in the same field, for the same reason.

Bad Addresses

So one rule we try to follow regardless of what kind of database software we use, MS Access for example, is that we should put one piece of information in each field. A rough analogy would be, if one wanted to fill a glass jar’s volume completely, using something like golf balls or hard candies would not work too well, as they’re too big to really follow the shape of the jar. But using gravel, or sand, or better still, flour, would be better. The individual pieces are smaller, and can fit the space much better. The smaller the pieces, the more “flexible” they are in filling the space.

Jars

Secondly, it’s a good idea to divide the tables (or collections of data) into categories—an employee database might contain a table of personal data, a table of office data, one of health plan data, one of travel/transport data, and so on. Turns out it’s easier for most databases—and users—to work with a larger number of smaller tables than the other way round.

Finally, there is at least one exception. Some kinds of data, such as addresses, work better with a little de-normalization. (Access makes this fairly easy to see.) We don’t want to put a zip code in the same field with city and state, but putting city, state, and zip fields in an address table—even though slightly redundant—make the addresses much easier to understand and use.

Address Table

Align Fields in Access Forms

Working with Microsoft Access can be kind of intimidating when you start, especially because there seem to be so many details to absorb and keep track of. And even fairly experienced database people have to watch out for the little things.
One which really drives some new Access users up the wall has to do with forms. Doing form layout is an art unto itself, and getting a good layout can take time. Even after you’ve got it mostly cleaned up, the program has one particular nitpick some people don’t even notice. At first.

Form Design View
When you work on a form in Design view, the most time-consuming thing is sizing and moving fields (officially known as text boxes, where data show up) and labels (to tell you what the fields contain). And making sure the Tab key gets you from field to field in a logical order is important. Luckily, we can use the Tab Order dialog box to do this.
Tab Order Box

At this point, the tab order doesn’t match the order of the fields because we rearranged them. We want to tab left to right, top to bottom. So we arrange our fields, bring up the box, and click Auto Order.

Auto Order Button
BUT!…if any of the fields are not aligned just right, by their top edges, with their buddies in the same row, whoever’s highest gets dibs in the tab order. (“D’oh!”)
So we select the fields and labels in question, go to the Arrange tab->Sizing and Ordering group, and click the Align button. In the dropdown, we click Top to get everything in a given row to line up.

Align Top Command

Problem solved.
Then, go back to the Tab Order box, click Auto Order once more, and test. That should do it.
Yes, it’s kinda nitpicky. But it’s worth it, as getting this sort of thing right can improve data entry speed in Access, and other database programs quite a bit…and win you the approval and accolades of the people who do it.

Access vs Excel—Which should we use for what?

Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even when it’s working fine.

So let’s just ask the question: When should we use each one?

To answer this, we need to understand what each one can do. Excel is a spreadsheet program. Even though information on one sheet can talk to info on another, it’s primarily meant to do math-related things. So if all you need is the equivalent of a sheet of graph paper to visually organize, say, a personal or house inventory, Excel does pretty well. You could use Word for this same purpose, and have about the same amount of work, if you don’t want to use Excel. Formatting and other aesthetic things are only about as hard as in Word, so it’s almost as if you built a giant table in Word and jotted stuff in it. Or if you want to budget monthly, quarterly, and annually, and have all of those update each other, great.

On the other hand, if you need to track something more complicated, such as information on the employees of a small (or large) company, you may run into trouble with Excel. The problem is, you may need to have three or four kinds of information that all relate to the employees, but are not directly related to each other: Personal info (home address, birthday, family), office info (building, floor, office number, phone number, business email), health plan (HMO, policy type, policy number), and maybe transportation (car make and model, parking spot, bus route, commute time). Yet all of these need to tie together, as it were. You might have each of these connect to the others through, say, the employee ID number or Social Security number—make that ID part of each of the four categories so they have a common element. This is where the term “relational database” comes from—the different categories of information nevertheless relate to each other, like a baseball team where each player has a separate job, but all cooperate to play and win.

If this idea makes sense, that we separate the types of information but allow them to relate through one item, then you understand one of the main concepts of the relational database. Like Access.

So the basic answer to the question is, If you have just one kind of information (or several that are unrelated), you can store each chunk on a separate worksheet in Excel and probably have no trouble. But if the different subsets (or as Access would call them, data tables) are related after all, using Access to store the information and work with it would actually be worth the extra time and effort. As a bicycle is good for getting around by yourself, but a pickup truck can haul some serious cargo by comparison, so Excel and Access. (Sort of.  🙂  )

As a postscript, the reason I’m not bringing up PowerPivot here—which lets us do some database-ish things in Excel—is that it’s a different tool again from Access, though it is useful. And not everyone knows about it, or how it works. We do teach it at SkillForge, but again, it’s a somewhat specialized tool, whereas Access is a more general database application and has more multi-user features, making it more suitable for business database work.

MS Access Resources

ms-accessMicrosoft has recently published a very thorough set of MS Access 2010 resources to the TechNet site. Access has always been the part of the Office Suite that straddles the end-user/IT Professional fence. The resources on TechNet are definitely geared toward Access Developers rather than folks that might be using MS Access more casually. (more…)

What’s New in Microsoft Office 2010 – Part 1 of 6

With every new release of Microsoft Office (or really any software product for that matter) users of a previous version ask themselves the question “should I upgrade?” And, as always, the answer is…it depends. I’ve collected a few of the new features of Office 2010 to hopefully help you make your decision. However, if you’re one of the many folks who didn’t move to Office 2007 because of the new Ribbon interface – you may want to seriously consider Office 2010. It corrects some of the confusion introduced by the Ribbon interface and adds quite a few useful new features as well. In this post, we’ll explore some of the new features that are common to most, if not all, of the Office 2010 applications. (more…)