Data Types, Properties, and Relationships in Access

Microsoft Access, Microsoft Office

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.

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.

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 ready.

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 the field names should be consistent across tables. So anyone who needs to look at or use the database won’t find it confusing.

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.

The reason is, the program can’t assume that what goes 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.

One relationship 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. 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 should 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.

To find out more, take a look at our Access classes.