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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
To Autonumber or not to Autonumber...
I am setting up a job number database for my company. As of now the
"database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? Thanks in advance for the help! -- ellsanto |
#2
|
|||
|
|||
To Autonumber or not to Autonumber...
Please do not confuse "Autonumber" with your (meaningful) [JobNumber].
Microsoft Autonumbers are intended to be used as unique row identifiers. They are not generally fit for human consumption. If you need a "simple four digit number ... somewhere around 9100...", add a new field. You can name it [ProjectNumber]. You can get Access to automatically generate the "next" number in sequence using the DMax() function (check mvps.org/access or search on line for "custom autonumber"). Regards Jeff Boyce Microsoft Office/Access MVP "ellsanto" wrote in message ... I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? Thanks in advance for the help! -- ellsanto |
#3
|
|||
|
|||
To Autonumber or not to Autonumber...
I concur with Jeff,
I always use an autonumber as primary key field and a separate field to store the meaningfull "human key" field. You can ensure it's unique by creating an unique index on that field. To generate it I use a numbers table: see my answer to the topic "Invoice number" in this discussion group. It gives you more flexibility then using the dmax function. -- Kind regards Noëlla "Jeff Boyce" wrote: Please do not confuse "Autonumber" with your (meaningful) [JobNumber]. Microsoft Autonumbers are intended to be used as unique row identifiers. They are not generally fit for human consumption. If you need a "simple four digit number ... somewhere around 9100...", add a new field. You can name it [ProjectNumber]. You can get Access to automatically generate the "next" number in sequence using the DMax() function (check mvps.org/access or search on line for "custom autonumber"). Regards Jeff Boyce Microsoft Office/Access MVP "ellsanto" wrote in message ... I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? Thanks in advance for the help! -- ellsanto |
#4
|
|||
|
|||
To Autonumber or not to Autonumber...
In message ,
ellsanto writes I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? If there is a need to keep the run of numbers contiguous then use a programmatic generator. If not then an ordinary autonumber field should be sufficient. -- Bernard Peek |
#5
|
|||
|
|||
To Autonumber or not to Autonumber...
I strongly disagree with using a table of numbers for this process. Why have
to keep up with such a table and why slow the process with an extra disc fetch when the DMax function works so well? In addition, such a method could cause your numbering to get out of sync if any user had a crash at the right time. Here is the basic concept: =Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1 The only issue you have to be concerned with is if you are in a multi user environment, you need to plan for multiple users trying to create a new invoice at the same time. -- Dave Hargis, Microsoft Access MVP "NG" wrote: I concur with Jeff, I always use an autonumber as primary key field and a separate field to store the meaningfull "human key" field. You can ensure it's unique by creating an unique index on that field. To generate it I use a numbers table: see my answer to the topic "Invoice number" in this discussion group. It gives you more flexibility then using the dmax function. -- Kind regards Noëlla "Jeff Boyce" wrote: Please do not confuse "Autonumber" with your (meaningful) [JobNumber]. Microsoft Autonumbers are intended to be used as unique row identifiers. They are not generally fit for human consumption. If you need a "simple four digit number ... somewhere around 9100...", add a new field. You can name it [ProjectNumber]. You can get Access to automatically generate the "next" number in sequence using the DMax() function (check mvps.org/access or search on line for "custom autonumber"). Regards Jeff Boyce Microsoft Office/Access MVP "ellsanto" wrote in message ... I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? Thanks in advance for the help! -- ellsanto |
#6
|
|||
|
|||
To Autonumber or not to Autonumber...
Perfect. Thanks, Dave. I modified the line you gave me and inserted it into
the data default value property. Works perfectly. I spent more time than I want to admit sifting through suggestions from a bunch of sites. Thanks for the quick easy solution. -- ellsanto "Klatuu" wrote: I strongly disagree with using a table of numbers for this process. Why have to keep up with such a table and why slow the process with an extra disc fetch when the DMax function works so well? In addition, such a method could cause your numbering to get out of sync if any user had a crash at the right time. Here is the basic concept: =Nz(DMax("[InvoiceNumber]","tblInvoice"),0) + 1 The only issue you have to be concerned with is if you are in a multi user environment, you need to plan for multiple users trying to create a new invoice at the same time. -- Dave Hargis, Microsoft Access MVP "NG" wrote: I concur with Jeff, I always use an autonumber as primary key field and a separate field to store the meaningfull "human key" field. You can ensure it's unique by creating an unique index on that field. To generate it I use a numbers table: see my answer to the topic "Invoice number" in this discussion group. It gives you more flexibility then using the dmax function. -- Kind regards Noëlla "Jeff Boyce" wrote: Please do not confuse "Autonumber" with your (meaningful) [JobNumber]. Microsoft Autonumbers are intended to be used as unique row identifiers. They are not generally fit for human consumption. If you need a "simple four digit number ... somewhere around 9100...", add a new field. You can name it [ProjectNumber]. You can get Access to automatically generate the "next" number in sequence using the DMax() function (check mvps.org/access or search on line for "custom autonumber"). Regards Jeff Boyce Microsoft Office/Access MVP "ellsanto" wrote in message ... I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? Thanks in advance for the help! -- ellsanto |
#7
|
|||
|
|||
To Autonumber or not to Autonumber...
Using an autonumber field for a value of any meaning is always a bad idea.
No exceptions. -- Dave Hargis, Microsoft Access MVP "Bernard Peek" wrote: In message , ellsanto writes I am setting up a job number database for my company. As of now the "database" is kept in a binder. It will soon be housed on our server. Yay! The most important field in the database is the job number. It is referenced by our accounting software, our project management team, upper management, etc. It is a simple four digit number that is somewhere around 9100 right now. What is the best way to generate that number on Access 2007? I have read about the limitations of the AutoNumber field on multiple forums . Does anyone have any suggestions on the best way to generate that field? If there is a need to keep the run of numbers contiguous then use a programmatic generator. If not then an ordinary autonumber field should be sufficient. -- Bernard Peek |
#8
|
|||
|
|||
To Autonumber or not to Autonumber...
Klatuu wrote:
I strongly disagree with using a table of numbers for this process. Why have to keep up with such a table and why slow the process with an extra disc fetch when the DMax function works so well? One reason is for a clients welding shop when they ran QC tests. They wanted to ensure if that if a test was deleted, for whatever reason, the number was reused. They never wanted to explain to the client why there were missing numbers. Yes, accountants would be exceedingly upset with this. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#9
|
|||
|
|||
To Autonumber or not to Autonumber...
Youu next question is whether or not to use your job number as a PK. My
advice for your situation is no. Your "deleted job" is a good example of why not. You will have had 2 different job records in existence with the same job number, |
#10
|
|||
|
|||
To Autonumber or not to Autonumber...
That is an entirely different circumstance, Tony.
Even for that, I would probably not use a table of available numbers. I would probably use a boolean field to denote the record as deleted and when I wanted a new record to use a previously used number, I would do a DMin filtered for only deleted records. -- Dave Hargis, Microsoft Access MVP "Tony Toews [MVP]" wrote: Klatuu wrote: I strongly disagree with using a table of numbers for this process. Why have to keep up with such a table and why slow the process with an extra disc fetch when the DMax function works so well? One reason is for a clients welding shop when they ran QC tests. They wanted to ensure if that if a test was deleted, for whatever reason, the number was reused. They never wanted to explain to the client why there were missing numbers. Yes, accountants would be exceedingly upset with this. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|