Should I use the automatically created ID field?

[Accessnoobee]

When creating a new table, Access seems to alway automatically create a field called ID, that is an autonumbered field. If I already have a primary key such as "account number" then should I just eliminate the ID field? I recently ran into tons of problems with type mismatch errors and with exports to Excel exporting the record's ID number for a looked up field contents rather than the value itself. While there may be other ways to track down these headache problems, I sense the simplest is to just strip out the ID field in the first place so errors are less likely when modifying the database tables and reports.

[June7]

If it is not in a pk/fk relationship and it annoys you, remove it. Some advanced query techniques can require a unique ID field so this autonumber might be useful in child tables that don't otherwise need a unique id. The autonumber field can always be added later if the need arises.

[Accessnoobee]

If it is not in a pk/fk relationship and it annoys you, remove it. Some advanced query techniques can require a unique ID field so this autonumber might be useful in child tables that don't otherwise need a unique id. The autonumber field can always be added later if the need arises.

I don't understand what Super Moderator means here. If my table already has a pk, then wouldn't it by definition already have a unique ID? When I use the client's account number as the pk (which can't be autonumbered because first it's alphanumeric and second its assigned elsewhere), then when would that ever not suffice?

My question is not about fixing a particular table. My question is that since I always create tables with a pk (i.e. I don't create tables without a unique field) then AS A GENERAL RULE should I always take out Access's automatically created ID when defining a new table? Is that considered good practice or bad practice by DBers and are there troubles I may encounter down the line if I do that? The trouble Super Moderator seemed to refer to not having a pk, rather than not using Access's preconfigured way of doing things.

[orange]

My question is that since I always create tables with a pk (i.e. I don't create tables without a unique field) then AS A GENERAL RULE should I always take out Access's automatically created ID when defining a new table? Is that considered good practice or bad practice by DBers and are there troubles I may encounter down the line if I do that?

Your question(s) are the heart of many debates. That you always create tables and identify a Primary key is very good practice regardless of whether you use a "Natural key" or a "Surrogate key". If you do some searching you will find many proponents of each of these approaches.

For more info on autonumbers (and this concept is not unique to Access) see
http://www.utteraccess.com/wiki/index.php/Autonumbers

For some references to surrogate vs natural keys
http://www.rogersaccesslibrary.com/T...ate%20Keys.doc
http://www.agiledata.org/essays/keys.html
http://weblogs.sqlteam.com/mladenp/a...l-keys-in.aspx
http://www.dbforums.com/database-con...ogate-key.html

I think June7 was suggesting that if you don't need or don't want to use the autonumber, then don't use it, but do use a PK on each table.

Good luck with your decision/research.

[June7]

Yes, if the table has autonumber field then it does probably have a unique ID, except when this field is set to allow duplicates - I know, weird, but if the field is not set as PK this can happen. If the table has another unique value field set as PK then the autonumber definitely is not needed. I further stated that some child tables (the many side of a relationship) don't need a PK field at all. PKs are required only if there are dependent tables that need to store the PK as a foreign key. Setting a field as PK will prevent duplication of values but the table can function without PK designation. If child table does not have its own dependent table(s), PK is not needed. However, I further stated some advanced queries need a unique ID, in which case the autonumber field can be useful in child table, even if it is not designated as PK.

I have built databases with and without autonumber PKs.