Problems with Autonumber

[Guest]

Hi,
I am exporting data from MS Excel into a table that has three major fields
-- Item(memo datatype), CSI field and a keywords field. CSI and Keywords are
not uniques, and though Item field is unique, it's very large (>256 char) and
it's ridiculous to have that as a primary key. I therefore decided to have
Autonumber as a primary key. Note that the items in my worksheet are not
random, but are written in a sequenced order. I now have a problem. If I want
to add a new data item somewhere in the middle of the table, I cannot do it,
since the numbering is fixed by the Autonumber key which is locked. It was
easy doing in Excel (all you had to do was "Insert in the correct record
position" without worrying about any primary key). How do I go about it??

 

[Dan Artuso]

Hi,
Unlike Excel, it shouldn't matter 'where' in the table you insert it because there
really is no order in a table. This is true for any database, not just Access.

Users will never view data directly from the table, you will have forms
in your application to present the data. If you need your data sorted, use
a query with an Order By clause.

If you're going to work with Access, you have to lose the speadsheet way of
thinking, it will only cause you grief.

 

[Guest]

Yeah. You're right. Sorting by some field is what I might need. In that case
I have to introduce another field that gives me the sequence number, apart
from the Autonumber primary key.

Hi,
Unlike Excel, it shouldn't matter 'where' in the table you insert it because there
really is no order in a table. This is true for any database, not just Access.

Users will never view data directly from the table, you will have forms
in your application to present the data. If you need your data sorted, use
a query with an Order By clause.

If you're going to work with Access, you have to lose the speadsheet way of
thinking, it will only cause you grief.

 

[Dan Artuso]

Hi,
If you want to keep track of when records were entered, use a date/time field
and populate it automatically using the now() function.

--
HTH
Dan Artuso, Access MVP

Yeah. You're right. Sorting by some field is what I might need. In that case
I have to introduce another field that gives me the sequence number, apart
from the Autonumber primary key.

 

[Jamie Collins]

Sorting by some field is what I might need. In that case
I have to introduce another field that gives me the sequence number, apart
from the Autonumber primary key.

I suggest you review your choice of primary key (PK) anyhow.

One way in which a Jet (MS Access) table is different from that in
another database is that you do not have a choice over the clustered
index for the table. Clustered index determines physical order on disk
and is therefore an important performance consideration. With Jet, the
PK is the clustered index, so you have to choose your PK very
carefully.

You are simply using the autonumber column, say named ID, to ensure
uniqueness and will not be a suitable choice for the clustered index.
Instead choose another column i.e. the one that you will be using in
GROUP BY or ORDER BY clauses or when selecting BETWEEN values. When
you've chosen this column(s), say MyTextCol, define the PK as
(MyTextCol, ID) in that order. You will need to define a UNIQUE INDEX
on just your autonumber column if it is to be used for DRI.

Jamie.

--

 

[Guest]

Jamie,
The suggestions were informative. Thanks a lot.
Som