The Use of Primary Keys and Autonumber (1 Viewer)

[The_Doc_Man]

Invariably this happens to new Access users because of the myriad of issues that crop up - plus the fact that there are no good books for this topic. Some fairly decent books. But no GOOD books. I'll address the reason for that in another place, not here.

When building a table that has descriptive text information, object names, place names, etc., the question always comes up - which is better as a prime key (PK)? The name or an autonumber?

Answer - if the text is variable length and frequently longer than 4 characters then use an AUTONUMBER. You'll save space and time in the long run. Use the raw descriptive data only for short tables with short keys. (Example: USA state-name abbreviations are reasonable non-numeric PK candidates for a state-name lookup.)

When you build a table that contains such descriptives as noted above, the AUTONUMBER provides exactly and ONLY one thing - a unique number that can be used to look up the corresponding record. Looking from the other side of the coin, if you already have an AUTONUMBER field, it might as well be the PK because otherwise it is wasted space.

One thing you cannot do with an AUTONUMBER is guarantee continuous and contiguous numbering. Gaps will occur. Fact of life, live with it. BUT that does not matter to a PK because it is normally just a lookup aid.

Some folks ask about whether an AUTONUMBER PK can have any meaning? Beyond the explanation above, it shouldn't - but sometimes it is OK to assign meaning AFTER THE FACT. Case in point - you go on-line and pay a credit card bill using an electronic funds transfer. The last step is a confirmation page that shows you a confirmation number. Want to bet me that the confirmation number ISN'T an autonumber? It's meaning was assigned AFTER the number was generated, and that meaning DOES NOT REQUIRE either continuous or contiguous numbering. So in that and similar cases, you can indeed assign a limited meaning to an autonumber. Just remember that it has limitations in that usage.

OK, strategy-wise, why go through this design hell of prime key selection anyway? Because if you have a PK, you have the means to save space via proper normalization of your other tables. You don't have to have multiple copies of your data.

Wait, you say... when I use that PK as the foreign key in another table, I am duplicating data. Yes, but only the PK, not any of the accompanying data from the table where the value IS the PK. The space you save is that which would have been occupied had you copied the extra data fields instead of using the PK/FK relationship to ease the lookup for you.

OK, what about sorting in some oddball order? {Best John Wayne imitation} Well, that's an easy one, pilgrim.

Here's a fact that new Access users don't understand too well. A table is just a recordset. A SELECT query can also provide a recordset. And neither forms nor reports are smart enough to know the difference most of the time. So ... if you open a table directly, order of presentation will be in PK order if there is a PK, or apparently unpredictable if no PK - unless you imposed a sort within the FORM. But that imposed sort is a type of "virtual" query.

You could impose the same order with a REAL query by just naming the fields you wanted to see and clicking the appropriate boxes to impose a sort order via the query grid. So if you wanted to see things in alphabetic order DESPITE the presence of a numeric PK, use a query.

Next level: Relationships... If you have a relationship between two tables such that one table's PK is also FK in another table, and if you have FORMALLY defined the relationship through the Access relationship pane, you can build queries using those related tables. And when you do, Access will AUTOMAGICALLY build the right JOIN statement for you.

So you've built this JOINed query and it contains records in the wrong order? Open the design view again and SORT that puppy. It's just a friggin' SELECT query so the sort won't affect native table order. Just PRESENTATION order.

Now, the ultimate question: So here's my table with NO key. What order is it presented in, and how can I make it have another order?

In the absence of a PK, the records in a table are presented in the order in which they were defined, whether or not a date/time field is part of the table. In a table view, you are looking at a VIRTUAL QUERY again. If you save it, you are saving the virtual query, but the table has not changed. If you click on a column to change order, you are affecting only the virtual query. The table order STILL didn't change. If you save the table now, you are AGAIN only saving the virtual query. All that careful sorting, all that hard work, and the table isn't really sorted.

What about if I do a compact and repair? Again, if you have a PK, the new copy of the table is in PK order. If not, the order is not predictable except that it probably didn't change from before the compact/repair.

So the bottom line is, if you want to control the order precisely, use a REAL query. Table views are so rarely proper that I'd be tempted to say they are NEVER right for about 90%+ of all things you could do with Access. Maybe even 95%+, who knows?

Think queries. They are your friends.

Just some food for thought, folks. Take it as you will.