Autonumber field - increment

[KarenF]

Hi,

A colleague of mine has several tables in her database and it seems to me
that an autonumber field would help her relate her data rather than having 2
primary keys in several tables and linking them (she is linking the tables as
a 1:1 on both fields in each table). The linked tables may not include
every record that the main table has. This is the fun part. The records are
sorted in date order - this is the order in which they were entered into the
tables. It is the Date and Event Name fields that are her primary keys, and
she's linking them both to their foreign keys in the other tables. When I add
an autonumber field to any of the tables, and I use incrementing values, the
sort order of the table remains the same (as expected), but, the
Autonumbering seems to start half way down the table, go to the end record
and then continue from the first record to the half way point it started
with. Am I not understanding autonumbering correctly? I know that each
record will be given a unique number which will never be duplicated (even if
the record with that number is deleted). Am I wrong to assume that Access
would increment the numbers starting with 1 as the first record etc.? My
colleague and I have tried this many times and Access always numbers the
records in the same way.

Does anyone have any ideas? Have I just discovered something about
Autonumbering that I didn't know before (and I'm not sure now why it's doing
it)?

I think this is a different question really, but my colleague is
experiencing some oddities with her database. Could this be because of any
of the table relationships?

Thanks for your time.

Karen

 

[Jerry Whittle]

Hi,

1. Autonumbers are not guaranteed to be sequential. If they are in a primary
key field, autonumbers are guaranteed to be unique (if you discount a bug or
two that are fixed with normal updates). Therefore you shouldn't worry about
the number order. It's just the glue that links tables together and should
not be used for ordering.

2. Speaking of ordering, tables aren't ordered! They may seem to be ordered.
Access might even present records in an order consistantly; however, there
would be nothing wrong if the next time that you opened the table to find all
the records scattered around. If you need records in a certain order, you
need to have a field which can be sorted in a query, form, or report. As
mentioned above, autonumbers are not good at this. I find that a date field
in a table with the default value of Now() is a good candidate for ordering
if all records are added one at a time. As the database in question has a
date field, use it for sorting.

3. You mention that there are some problems AND that there are records in
one table without matching records in another. Maybe you need Left or Right
joins between the tables. If the SQL statement says INNER JOIN or the little
line between the tables in QBE design view don't have a little arrow pointing
at one table, that would cause ONLY records that are in both tables to show.

 

[KarenF]

Hi Jerry,

Thanks very much for your help. Yes, I've changed the join properties to
join type 2 (so that all of the main table detail is shown, regardless of
whether or not a related record appears in the other tables).

Thanks for the info about autonumbering. My colleague felt it would be
easier to manage if the record numbers were in the same order as the event
dates. I'm sure it will work just as well without that being the case -
especially with the use of lookups to aid data entry.

I'm used to tables opening in different orders. While I was trying to get
the autonumbers to start at number 1 for the first record in the table, I
looked at the Table Properties box and played with the Order property. This
is what prompted my question. I was trying to see if changing the order
property to date, so that the earliest date was at the top of the table (yes,
I could have just sorted the table but didn't know if this property was more
concrete - I'm still learning!), made the autonumbers start with number 1
corresponding to the earliest date. As you know, it didn't! I'll advise my
colleague that unless she wants to type in a unique id for each event record,
then the Autonumber's bizarre order shouldn't be a problem.

Thanks again Jerry. I thought I was going mad!

Cheers,

Karen