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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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

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" <(E-Mail Removed)> wrote in message
news:5C1288B3-8028-42E5-869A-(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:eUr7H8$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

Jeff,

Thanks for responding with additonal input. We've had a db for about ten
years now and have used the autonumber field as the job number. As stated
in another reply in this message thread, it's not critical that all job
numbers are accounted for. We just need a number for each job and the
autonumber field makes it easier. In the past, whenever I needed to make a
great deal of changes to the db, I would create a new db and then enter the
"in progress" job numbers and related info to the new db and the new numbers
would pick up from there. It was a continuance of four-digit numbers. Our
accounting system, which I have no control over, is limited to six-digits.
Sometimes, just for accounting purposes, we need to add an "A" or "B" to the
end of the job number and with our jobs starting with an R for Repair, we
can't use the "A" or "B" ending any longer. Also, I need to totally rework
the db - include a time tracking portion related to job numbers and new
design.

What I've done is created a new field, as you stated, and used the +1
equation to make it also autonumber. I was concerned with how users would
search both fields, but in the query backing the form I've included the
parameter [enter job number] for both fields. I'll have to temporairly make
one field JobNumber2 on forms and such until "in progress" jobs are
complete.

There won't be two jobs with the same job number as the jobs now are, for
example, 11095 and when I start with the new numbers it will be 1095. By
the time I get back to 11095, they will probably have a new system they want
to use.

I hope this makes sense and with your expertise in Access if you see
anything wrong or that may cause headaches down the road, please let me
know. I appreciate your time and help.

Pam

"Jeff Boyce" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eUr7H8$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

Pam

Please see comments in-line below...

"Pam" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jeff,
>
> Thanks for responding with additonal input. We've had a db for about ten
> years now and have used the autonumber field as the job number. As stated
> in another reply in this message thread, it's not critical that all job
> numbers are accounted for. We just need a number for each job and the
> autonumber field makes it easier. In the past, whenever I needed to make
> a great deal of changes to the db, I would create a new db and then enter
> the "in progress" job numbers and related info to the new db and the new
> numbers would pick up from there. It was a continuance of four-digit
> numbers. Our accounting system, which I have no control over, is limited
> to six-digits. Sometimes, just for accounting purposes, we need to add an
> "A" or "B" to the end of the job number and with our jobs starting with an
> R for Repair, we can't use the "A" or "B" ending any longer. Also, I need
> to totally rework the db - include a time tracking portion related to job
> numbers and new design.

If you need to "add" an "A" or "B" or "R" to a job number, you can use a
query and concatenate the value in the [job number] field.

>
> What I've done is created a new field, as you stated, and used the +1
> equation to make it also autonumber.

A point of clarification ... an Access Autonumber is automatic. Your "+1"
routine generates a sequence number, but you probably don't want to be
calling it an "autonumber", as some folks could get confused about what it
is...

> I was concerned with how users would search both fields, but in the query
> backing the form I've included the parameter [enter job number] for both
> fields. I'll have to temporairly make one field JobNumber2 on forms and
> such until "in progress" jobs are complete.

My point in suggesting creating a new field and copying over the numbers was
so that you would ONLY need to search one field, the new one you created.

>
> There won't be two jobs with the same job number as the jobs now are, for
> example, 11095 and when I start with the new numbers it will be 1095. By
> the time I get back to 11095, they will probably have a new system they
> want to use.

If you've reached 11095 in the old numbering system, and are starting at
1095 in the new numbers, I still don't have any idea where your old numbers
started ... it isn't so much when the new ones reach the end of the old
ones, but when it reaches the first of the old ones.

>
> I hope this makes sense and with your expertise in Access if you see
> anything wrong or that may cause headaches down the road, please let me
> know. I appreciate your time and help.
>
> Pam

Good luck on your project!

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.

>
> "Jeff Boyce" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:eUr7H8$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>