IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New AutoNumbers not always continuous
Ah, ok. We have that number that increments by one every time we add a new critter, but it isn't the key. It simply keeps count for us of how many critters we own.
Identity columns using the AutoNumber technique have a tendency to introduce periodic gaps in the numbering. What happens is that someone will start to type in values in a new row (which will cause the sequence to increment), and then hit the Escape key to abort the insert (in database terms, a rollback is performed on the transaction). In both Access and SQLServer, the autonumbers are not reset to the previous value (meaning they are autonomous and not part of the transaction process). That number in the sequencer is basically skipped as far as the table is concerned. The only way to get it back would be to do a database repair prior to the next row insert.

Bottom line is that the maximum autonumber will not necessarily be a true indication of the count of the number of rows in the critter table.

Hmmm, all I do is sort by critter type, or critter name to match whether a name exists... is that a string search or comparison? (Need to look those up, I think).
In a larger application, you'd typically not want to use a string column as the primary key. An integer key will make the table joins much faster.

But to make the string searches faster, you might want to build an index on a string field. In Access, there is an Indexed attribute for each column you define in the Design View. Primary Keys are one form of an index. Any column (or group of columns) can participate in Indexes, in various combinations. Indexes are very important when the amount of data becomes large - I've seen queries go from not being finished after waiting an hour, to taking just a few seconds with the proper index. At any rate, indexes are a subject on to themselves.

The other problem you have, of course, is that you want to place a constraint on the name column to not allow duplicates. Primary keys have the quality that they are by definition a UNIQUE INDEX. Databases allow a unique constraint to be placed on any index of your choosing - in the Design View for Indexed you'll see the choice of "Yes (No Duplicates)".

That's why John said my table was so smart, wow. Because every data bit in the row is dependent upon, or related to the name. Neat.
Third Normal Form is sometimes expressed as:

The key; The whole key; And nothing but the key. So help me Codd. :-)
New ICLRPD (new thread)
Created as new thread #119763 titled [link|/forums/render/content/show?contentid=119763|ICLRPD]
"good ideas and bad code build communities, the other three combinations do not"
- [link|http://archives.real-time.com/pipermail/cocoon-devel/2000-October/003023.html|Stefano Mazzocchi]
     Is there some sort of tutorial disk for Access? - (Nightowl) - (31)
         Okay, first question... :) - (Nightowl) - (17)
             Table is multiple columns together as a unit - (FuManChu) - (5)
                 Re: Table is multiple columns together as a unit - (Nightowl) - (4)
                     Third normal form - (ChrisR) - (3)
                         Re: Third normal form - (Nightowl) - (2)
                             1st Normal Form - (ChrisR) - (1)
                                 Re: 1st Normal Form - (Nightowl)
             Tuples - (ChrisR) - (10)
                 Re: Tuples - (Nightowl) - (1)
                     Could be - (ChrisR)
                 How come critterid isn't IDENTITY? -NT - (mmoffitt) - (7)
                     I'm of two minds on identity - (ChrisR) - (6)
                         Re: I'm of two minds on identity - (Nightowl) - (5)
                             In Access, Identity is an AutoNumber - (ChrisR) - (3)
                                 Re: In Access, Identity is an AutoNumber - (Nightowl) - (2)
                                     AutoNumbers not always continuous - (ChrisR) - (1)
                                         ICLRPD (new thread) - (ben_tilly)
                             Identity being the key - (orion)
         Don't recall if there's a tutorial - (ChrisR) - (4)
             Re: Don't recall if there's a tutorial - (Nightowl) - (3)
                 Part of Office Pro - (ChrisR) - (2)
                     Re: Part of Office Pro - (Nightowl) - (1)
                         Word to Access - (orion)
         Access tutorials - (orion) - (4)
             Re: Access tutorials - (Nightowl) - (1)
                 Re: Access tutorials - (orion)
             It worked, Norman!! ( was Re: Access tutorials) - (Nightowl) - (1)
                 Glad to hear it - (orion)
         the first thing to do - (cforde) - (2)
             Better yet - (orion) - (1)
                 Not that I recommend it... - (ChrisR)

Why not just name him Hitler B. Evil?
74 ms