A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Record Order



 
 
Thread Tools Display Modes
  #1  
Old September 25th, 2009, 10:46 PM posted to microsoft.public.access.tablesdbdesign
jerseygirl
external usenet poster
 
Posts: 15
Default Record Order

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
  #2  
Old September 25th, 2009, 11:03 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default Record Order

In message ,
jerseygirl writes

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?



--
Bernard Peek
  #3  
Old September 26th, 2009, 12:06 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Record Order

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

"jerseygirl" wrote in message
...
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



  #4  
Old September 26th, 2009, 06:52 AM posted to microsoft.public.access.tablesdbdesign
Stephen Rasey
external usenet poster
 
Posts: 4
Default Record Order

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.

--
Stephen Rasey
WiserWays, LLC
Houston, TX


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.