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  

AutoNumber



 
 
Thread Tools Display Modes
  #1  
Old April 8th, 2010, 09:55 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

Hi,

I have a table that holds information for jobs. The job number is an auto
number field. It has grown to a 5-digit number and users want to start over
at 1000 due to problems with number spaces in accounting system. I could
start a new database, but the problem is with jobs that are still working
the the current db. That would cause users to run two db's until the jobs
in the first db are completed. I thought about making the current job
number field a regular number and then entering a new auto-number field for
job numbers, but how would they search for both the 5-digit numbers and the
new numbers?

Does anyone have any suggestons as to how I can start a new list of numbers?

Any help is appreciated.
Thanks,
Pam


  #2  
Old April 8th, 2010, 11:17 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default AutoNumber

Pam

First, if you are referring to the Access Autonumber data type, be aware
that it really isn't fit for human consumption. It's designed to provide a
unique row identifier ... that's it.

If you/your users need a sequential numbering system, you'll need to "roll
your own" (but just search on "custom autonumber" for several idea on how
to).

You/your users may have a more serious issue though ...

If you are identifying your jobs with a job number, and if you "start over",
how will you be able to tell the difference between Job Number 12345 (from
1993), and Job Number 12345 (from 2010)?

Rather than start another database, and have to look in more than one place,
what about the idea of creating a new field that holds [JobNumber]. You can
start out filling it with the values you already have in your Autonumber
field. Then you can use the custom function to generate new [JobNumber]
values, and let the Access Autonumber field continue to be the primary key
.... it IS the primary key for your records, right?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Hi,

I have a table that holds information for jobs. The job number is an auto
number field. It has grown to a 5-digit number and users want to start
over at 1000 due to problems with number spaces in accounting system. I
could start a new database, but the problem is with jobs that are still
working the the current db. That would cause users to run two db's until
the jobs in the first db are completed. I thought about making the
current job number field a regular number and then entering a new
auto-number field for job numbers, but how would they search for both the
5-digit numbers and the new numbers?

Does anyone have any suggestons as to how I can start a new list of
numbers?

Any help is appreciated.
Thanks,
Pam



  #3  
Old April 9th, 2010, 04:30 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default AutoNumber

Jeff,




I realize that this if off the subject, but why the disclaimer? Have you
had problems? And what do you mean by "You can thank the FTC of the USA for
making this disclaimer possible/necessary."?

Thanks,

Dennis



  #4  
Old April 9th, 2010, 04:39 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default AutoNumber

Pam,

Another issue that Jeff alluded to, but did not metion. What happens when
your revised 4 digit job number reach the 5 digit in another 9000 jobs for
the third time? At that point, you will have three jobs with the same number.

Can you add a field like year and start your count each year at 1000. The
actual job number would be year and job number, but you could print the year
in one spot and the 4 digit job number in another spot?

I agree with Jeff on adding a new field (or two) instead of another database
since you will have to add a 3rd database when you second series of job
numbers run out.

Good luck,

Dennis



  #5  
Old April 9th, 2010, 05:05 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default AutoNumber

Based on recent media stories, "soccer moms" who were blogging (positively)
about a product that they had received at little/no cost, but who failed to
disclose that fact, were coming under the scrutiny of the FTC. The reported
ruling sounded like it was being expanded to anyone who received any product
and said anything nice about it without disclosing that they had received it
at 'other-than-retail-cost'.

Better safe than sorry...


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dennis" wrote in message
...
Jeff,




I realize that this if off the subject, but why the disclaimer? Have you
had problems? And what do you mean by "You can thank the FTC of the USA
for
making this disclaimer possible/necessary."?

Thanks,

Dennis





  #6  
Old April 9th, 2010, 05:31 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

Jeff,

Thank you for the suggestions. Per your suggestion:

"Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds [JobNumber].
You can start out filling it with the values you already have in your
Autonumber field. Then you can use the custom function to generate new
[JobNumber] values, and let the Access Autonumber field continue to be the
primary key ... it IS the primary key for your records, right?"

Will you please explain further how I may use this? Are you saying I could
put the job numbers that are still open and in use in an new field along
with the new list of numbers?

Like this:

11095 open job
11096 open job
1000 new job number sequence

If this is possible, how might I go about doing so?

And yes, the Access Autonumber field is the primary key. We've been using
this setup for about 9 years now. It's not critical that we have a job for
each and every number. If a number is deleted, we just go to the next
number available. The jobs aren't tracked as closely as say a list of
purchase orders where they all need to be accounted for.

Thanks for your help.
Pam


"Jeff Boyce" wrote in message
...
Pam

First, if you are referring to the Access Autonumber data type, be aware
that it really isn't fit for human consumption. It's designed to provide
a unique row identifier ... that's it.

If you/your users need a sequential numbering system, you'll need to "roll
your own" (but just search on "custom autonumber" for several idea on how
to).

You/your users may have a more serious issue though ...

If you are identifying your jobs with a job number, and if you "start
over", how will you be able to tell the difference between Job Number
12345 (from 1993), and Job Number 12345 (from 2010)?

Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds [JobNumber].
You can start out filling it with the values you already have in your
Autonumber field. Then you can use the custom function to generate new
[JobNumber] values, and let the Access Autonumber field continue to be the
primary key ... it IS the primary key for your records, right?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Hi,

I have a table that holds information for jobs. The job number is an
auto number field. It has grown to a 5-digit number and users want to
start over at 1000 due to problems with number spaces in accounting
system. I could start a new database, but the problem is with jobs that
are still working the the current db. That would cause users to run two
db's until the jobs in the first db are completed. I thought about
making the current job number field a regular number and then entering a
new auto-number field for job numbers, but how would they search for both
the 5-digit numbers and the new numbers?

Does anyone have any suggestons as to how I can start a new list of
numbers?

Any help is appreciated.
Thanks,
Pam





  #7  
Old April 9th, 2010, 05:37 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

Dennis,

Thank you for replying. We've been using this db for about nine years and
have just now reached a 5-digit series of numbers. To reach this number
again is so far in the future, I don't think it would be a problem. I'm
more concerned with how to leave the current open jobs in the same db but
still start over with new numbers (perhaps with another field as suggested
but then how do I search for both and have both fields in queries) and make
users happy in doing so.

I'd welcome any further suggestions or advice.
Thanks,
Pam



"Dennis" wrote in message
...
Pam,

Another issue that Jeff alluded to, but did not metion. What happens when
your revised 4 digit job number reach the 5 digit in another 9000 jobs for
the third time? At that point, you will have three jobs with the same
number.

Can you add a field like year and start your count each year at 1000. The
actual job number would be year and job number, but you could print the
year
in one spot and the 4 digit job number in another spot?

I agree with Jeff on adding a new field (or two) instead of another
database
since you will have to add a 3rd database when you second series of job
numbers run out.

Good luck,

Dennis





  #8  
Old April 9th, 2010, 11:18 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default AutoNumber

Pam

Add a new field to your table. This will be your new "job number"

Create an update query that puts the Autonumber value in the new field.

In your form based on the table, add in the "custom autonumber" routine
described earlier.

I still can't tell from your description how you propose to tell apart two
jobs with the same job number, which it sounded like you were trying to
figure out in your original post. Tell me again why you feel you need to
"start over"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Jeff,

Thank you for the suggestions. Per your suggestion:

"Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds [JobNumber].
You can start out filling it with the values you already have in your
Autonumber field. Then you can use the custom function to generate new
[JobNumber] values, and let the Access Autonumber field continue to be the
primary key ... it IS the primary key for your records, right?"

Will you please explain further how I may use this? Are you saying I
could put the job numbers that are still open and in use in an new field
along with the new list of numbers?

Like this:

11095 open job
11096 open job
1000 new job number sequence

If this is possible, how might I go about doing so?

And yes, the Access Autonumber field is the primary key. We've been using
this setup for about 9 years now. It's not critical that we have a job
for each and every number. If a number is deleted, we just go to the next
number available. The jobs aren't tracked as closely as say a list of
purchase orders where they all need to be accounted for.

Thanks for your help.
Pam


"Jeff Boyce" wrote in message
...
Pam

First, if you are referring to the Access Autonumber data type, be aware
that it really isn't fit for human consumption. It's designed to provide
a unique row identifier ... that's it.

If you/your users need a sequential numbering system, you'll need to
"roll your own" (but just search on "custom autonumber" for several idea
on how to).

You/your users may have a more serious issue though ...

If you are identifying your jobs with a job number, and if you "start
over", how will you be able to tell the difference between Job Number
12345 (from 1993), and Job Number 12345 (from 2010)?

Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds
[JobNumber]. You can start out filling it with the values you already
have in your Autonumber field. Then you can use the custom function to
generate new [JobNumber] values, and let the Access Autonumber field
continue to be the primary key ... it IS the primary key for your
records, right?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Hi,

I have a table that holds information for jobs. The job number is an
auto number field. It has grown to a 5-digit number and users want to
start over at 1000 due to problems with number spaces in accounting
system. I could start a new database, but the problem is with jobs that
are still working the the current db. That would cause users to run two
db's until the jobs in the first db are completed. I thought about
making the current job number field a regular number and then entering a
new auto-number field for job numbers, but how would they search for
both the 5-digit numbers and the new numbers?

Does anyone have any suggestons as to how I can start a new list of
numbers?

Any help is appreciated.
Thanks,
Pam







  #9  
Old April 10th, 2010, 05:05 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default AutoNumber


Pam,




Does the accounting system "link" back to the access database? In other
words, how does a user on the accounting system look up an invoice number?
Is it done manually or is done through software.

If if is done manually, you can do pretty much anything you want. If it is
done through software, then you have a problem because how will the user link
to the new database?

I know this is a dumb question, but can you increase the size of the job
number on the accounting software?

How many open jobs do you have?

Have you thought about MOVING (copy to archieve database and delete from
current database) all of the old closed jobs into an archieve database?
You could also make the primary key a two part key. Fro the existing
records, you could put a value of "O" for old, and a value of "N" for new.
The second part would be the job number. This would allow you to have both
sets of data in the same database. Might make life easier down the road.

You would have two databases that are duplicates of each other, one had new
theThis would allow everything to stay the same. As the existing jobs are
completed, I would move them to the duplicate / purged / archieve database.

In either case, you would then have to change the current autoassigned field
to a number and manually assign new job numbers.

I would also create a new inquiry form. The first question to ask would be
job date, then job number. If the job date is before the conversion date,
then have you software look at the archieve database or all all job numbers
with an "O" for old in the first part of the key. If the job date is after
the conversion date, then look at the existing database or at all jobs with
an "N" for new in the first part of the key.

Your other option is copy old jobs to the archeive data base and convert the
number on the 5 digit number to 4 digit numbers.


Does this give you some ideas?

Anyway you go, you have some work ahead of you.

I think that one of the big issues here is how the accounting system
interacts or doesn't interact with the Access database. What limitations
does the accounting system impose on the Access database other than a 4 digit
job number?

Also, is the job number in the accounting system a 4 DIGIT field or a 4
CHARACTER field? If it is a 4 character field, then we have some other
options availalbe, but it does involved a conversion of the fields in the
accounting database. Is that an option?

If will be helpfull to know that the limitations are so I could play with
some ideas.


Dennis


  #10  
Old April 12th, 2010, 02:16 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default AutoNumber

Echoing what Jeff & Dennis have said, it's a bad idea to mess with your
primary key or unique record identifier. That's one problem with using your
unique identifier field have some type of other meaning, or be exposed to
others. It opens it up to the possibiity of somebopdy wanting to "mess" with
it.

- - - -


Here's a simpler solution. Make the last 4 digits of your autonumber field
be the "job number" for external consumption. You could just show them
those 4 digits, and keep the leading digit invisible to them. Best of both
worlds.


 




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 05:07 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.