Record Order

[jerseygirl]

Ok, here goes…..

Working in Access 2003, I made a database for all of the invoices that come
into my department.

For a Primary Key, I used a field called ID, with AutoNumber for the data
type. Each record would get its own number when I entered it; the numbers
were incremental. I figured that this was the best way, since there were no
fields that I could trust to be unique (a large number of vendors means that
more than one might use the same invoice number). When I entered my batches
of invoices using a form, I would enter them in the correct order for my
report, so that I could sort by the ID field and everything went very
smoothly for close to three years.

Recently, I’ve discovered that this is not how AutoNumbers should be used,
and I’ve wound up needing help from this forum. I’m going to create a new
database to start at the beginning of next year. What should I do so that my
invoices show up in the order I enter them? Can I somehow make it so that
each record gets timestamped when I create it (not as Primary Key), so they
stay in order? Is there a way to use AutoNumber for this safely? Or is
there something else I should be doing?

All help is appreciated.

Thanks!

-Michelle

--
jerseygirl
~~~~~~~~~~
Sing like nobody's listening
Dance like nobody's watching
Love like it's never gonna hurt

 

[Bernard Peek]

In message <[email protected]>,

Recently, I’ve discovered that this is not how AutoNumbers should be used,
and I’ve wound up needing help from this forum. I’m going to create a new
database to start at the beginning of next year. What should I do so that my
invoices show up in the order I enter them? Can I somehow make it so that
each record gets timestamped when I create it (not as Primary Key), so they
stay in order? Is there a way to use AutoNumber for this safely? Or is
there something else I should be doing?

You can add a date field with a default value =now() which will
timestamp each record. On the other hand, an autonumber field does the
job so why change anything?

Have you consider a composite primary key composed of supplier ID and
supplier invoice-number?

 

[Jeff Boyce]

Bernard points out that you can keep your Autonumber to uniquely identify
each invoice, but add a date/time field so you can sort them according to
when you entered them.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stephen Rasey]

The Autonumber will cause you grief in at least two circumstances:

1. If you ever replicate the database, so that data entry can be done in
two or more places not on a network, then all replicated Autonumber fields
convert to Random and order will be lost.

2. If you copy the database so a friend in another department can use your
application, and later you want to merge the two tables of data, you will
have duplicate numbers.

Adding a datefield has lots of other advantages -- it can document your
process time. It can help you find work you did last week.