The Missing Manual describes a little procedure to force Access to start an autonumber sequence at a specific value for purposes of generating unique customer ID numbers. While I did find the book quite helpful in understanding how Access works, I've learned that some of what it says is simply wrong in terms of general database design. I was going to use an 8-digit number. Is this a good idea or is there something better for this purpose?

Hi,

An Autonumber field is a good one to use as a Primary Key. It should not have any meaning to the users.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I found this thread here but it doesn't seem to work. I enabled the content but it still doesn't do anything when I click on the button labeled "counter table method." The Dmax method is out because of its drawbacks noted in the readme. Any suggestions?

Hi,

You might want to take a look at Jack's Custom Autonumbers demo in the Code Archive.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thanks... I just looked at that one. Out of curiosity, under what circumstances would an auto number field generate a negative number? Does it just randomly occur sometimes or would some condition have to be met for this to happen?

If you use Access Replication all Autonumbers will switch to Random. It won't do it all by itself though. You can also manually set an autonumber to Random (but not back to sequential once you've added a random record).

The other (very unlikely!) possibility is that if you add enough records (deleting most of them along the way, lest the database bloat over 2Gbyte) to reach 2147483647 the next autonumber assigned will be -2147483648 (that's 2^32-1 and -2^32 if you find the numbers perplexing).

If that's what it would take, I would never reach a negative number. As for using an auto number field as a customer ID, the arguments I've seen against it so far have to do with things that are of no concern to me. The numbers don't have to be sequential and they won't be questioned in an audit of the system. If my state Insurance Department wants to take a look, they'll be interested in what happened with the policies I wrote, not what number I assigned to the customer. All in all, I don't see a compelling reason to get more complicated than I have to. Unless someone else has the smoking gun reason to not do it, I think I'll use the little trick in my book... I really appreciate you guys answering my questions, tho. I find it very helpful.

Now, I do have another little issue. I've got 2 tables...one with states and one with states & counties as such:

tblStates
StateID (PK - consisting of 2-letter state abbreviations)
StateNames

AND

tblStateCounties
Autonumber (PK)
StateID (FK)
County

The problem is that many of the county names are the same from one state to another so they are repeated throughout that 2nd table. I would like to split it so that I have tblStates, tblCounties, and then a junction table, tblStateCounties, with the FKs from the first 2 tables. The table analyzer in Access is able to split my table but it creates tables with lookup fields. I can't figure out how to get Access to do it the way I want it (and, apparently, the way it's supposed to be done). How about that one?

Hi dreamregent

PMFJI

You asked if using the Autonumber is a good idea, and the general consensus is that the primary key field, (which an autonumber is usually used for), should, as theDBguy points out, be an internal Access field that should not have any meaning to the user. While it may be of no concern to you now, there may be changes in the future that may mean that the customer ID needs to have some meaning.

If you use an autonumber then it becomes virtually impossible to change the ID and imbue it with any meaning. Starting off with a separate field for your CustomerID means that you are effectively future proofing the field for any changes to it's requirement in the future, which, for the addition of a single field, is a small overhead.

On your second point, Once the wizard has created tables with table level look up fields you should simply be able to change the lookup display control in the table back to 'text'

hth

You can have a list of thousands of incremental autonumbers, and all of a sudden the next one is negative. There doesn't need to be any particular reason - the fact is, Autonumbers are only guaranteed to be unique, not ordered. Thus, they make a terrible (IMO) field for displaying to users (there's much... discussion... about this (IMG:style_emoticons/default/smile.gif) ).

Check out the Autonumbers wiki article, it gives some excellent examples of how they should be used along with some common misconceptions about them.

I'd be inclined to argue that your Counties table is just fine as it is. Benton County, Washington and Benton County, Arkansas have nothing in common but the name, and I can't see any benefit to having a table where there is just one record for a mythical "Benton County" entity!

So I think splitting up this table would be overnormalizing and of no benefit.

Overnormalizing? That's not what I expected to hear around here... lol... ok, the county table stays as is.

There's actually quite a few situations that can be overnormalized. Company Addresses & Contacts are a great example. You can run circles around yourself trying to design a single person that can be associated with companies X, Y and Z, along with any of those company's possible branches (at the fact that the contact may regularly jump between branches), then throw in personal addresses, phone numbers, email addresses, etc, all into the same contact entity. In some cases it's easier just to call it good enough with a simple but not quite as flexible structure.

Generally speaking, a more flexible system equates to a more complicated system, as you've probably noticed when looking at this zip code stuff. The more variables our system handles, the more costly it is to write. A complicated system isn't necessarily a bad one, but in my experience the complexity:workload scale is a logarithmic one. Sometimes it just doesn't seem worth it to have that tiny bit of extra functionality that would be used less than 5% of the time at the expense of 5x more design/setup.

(Doesn't your manual cover this? Jeesh! (IMG:style_emoticons/default/shocked.gif) )

Cheers,

It's funny that you'd use that example because, on some levels, that's kind of what I have to do. I have used a system similar to this when I worked in insurance claims. That system was devoted to claim handling for each state across the nation. This is a retail agency but the work is just getting too complicated because we are now dealing with all sorts of different companies and a recently acquired customer base of potential clients all across the country. My office is an agency that has been relying on a paper system and it is just too complicated trying to get meaningful information out of the records when they're all stuffed in a filing cabinet. The time we have to devote to things like compiling reports is truly ridiculous. I know this is a lot of work but we have tried other products. There are a plethora of websites you can "rent" online but they only do part of the job and they don't give us all the information we need due to being "cookie-cutter" in nature. We are still forced to do a bunch of paperwork because we don't have an integrated system. This will alleviate those concerns. I may be an amateur but I feel certain I'm capable of doing this with the proper reference material.



That's the most hysterical thing I've heard all week! The manual was pretty good at getting me familiar with the Access GUI and giving me a basic understanding of database design and the system as a whole. It is worth it as an introductory guide specifically for Access 2007, but I can tell you that The Missing Manual: Access 2007 has some severe drawbacks. I've had a passing familiarity with database for a long time. Therefore, it's a good thing I already knew what a "foreign key" was because that term is something this manual never mentioned when it talked about relational structure. It did, however, wax poetically about multi-value fields and putting lookups in your table fields. I have ordered another book that is much more thorough, though: Access 2007 Inside Out. The Missing Manual wasn't very long but this book has some 1500 pages and good reviews from what I saw. Unlike the other book, I expect it to have complete lists and explanations of all the functions, controls, options, etc. The index is also very extensive and detailed. Amazon has an extensive preview of the pages and it looked good so I ordered it.

>> It's funny that you'd use that example because, on some levels, that's kind of what I have to do <<

In my case, I had to do the same type of intricate layout with a manufacturing plant. Contacts didn't mean too much in the broad scheme of things, so I was content to draw that line at a relatively simple level. The rest, no so much. I definately know where you're coming from.

>> Access 2007 Inside Out <<

I think you'll be very pleased with this. I haven't acutally used the Inside Out serious (my only general Access book goes way back), but Jeff and John (the authors) are two of the most experienced people with Access, and both have real world experience. Jeff was an MVP for ages, he now works for MS on the Access development team. John as well has been an MVP for ages, and still is.

If you find yourself requiring some reference for VBA (and you will, if you aren't fluent in it yet), I would recommend an applicable book by Wrox. Their programming references for Access throughout the years have been stellar. The authors, again, are long time and highly knowledgable MVPs. (I mention this because I'm not sure if Inside Out goes into depth with VBA, where I know Wrox does (I own two Wrox programmers references, both are excellent)).


If I may offer one last bit of advice (for the time being anyway), it would be to consider moving up from 2007 and into 2010. 2007 was a bit of a black sheep... the first of a complete renovation of both UI and the underlying data engine (parts of it anyway). It's got a few quirks... 2010 offers a much more robust application, in my opinion. It includes web publishing capabilities, but you need not use them if you don't require them, and the other general desktop app improvements over 2007 are significant. Just something to keep in mind, not the end of the world either way though.

Good luck with the project.