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
  #11  
Old April 13th, 2010, 03:50 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default AutoNumber

Fred,

I thinkg that is a great idea. However, the only issue is one that Pam has
not responded to and that is how much control she has over the accounting
system software. As I understand it, the issue is with the accounting system
- not the Access module. If she cannot make changes to that accounting
system, then there is no way to hide the initial digit on the accounting
system.

If she can make changes to the accounting system, this is a great solution.


Pam,

Can you make changes to the accounting system software?


Dennis


  #12  
Old April 13th, 2010, 10:21 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

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









  #13  
Old April 13th, 2010, 11:07 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

Dennis,

Thank you for replying with suggestions and questions. I am not involved in
the accounting dept or software, so I cannot change the digits available in
the software. The accounting software doesn't interact with the db. We
start our repair jobs and use the next available job number to track until
complete and then the job number is entered as a document number in
accounting and then is issued an invoice number in the accounting software.
In the past, when the db would give us problems or we would want to make
major changes, I would start another db and insert the "in progesss" jobs in
the new db and let the autonumber pick up where the "in progress" jobs left
off. That wasn't a problem until we hit the five digits and then users
wanted to "start over" with four digits to allow for "A"'s and "B"s parts to
job numbers in accounting. I know that "start over" doesn't sound right,
but in our situation it works. If I start my jobs now at 1000, our last job
numbered 1000 was prior to early 2006 and then it would be in an archived db
and wouldn't conflict with this db.

I like the idea of copying the "in progress" jobs over to a new db, which is
another reason why I want to do this - I'm redesigning the whole database.
(I've learned so many new and interesting ideas here that I know my new
design will be much smoother.) Anyway, copy to new db and enter the job
numbers as 4-digit, dropping the leading "1" from the number as it is now.

If you have other suggestions or see where this could lead to problems in
the future, I would very much like to hear them.

Thanks again for your time and help.
Pam

"Dennis" wrote in message
...

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




  #14  
Old April 13th, 2010, 11:09 PM posted to microsoft.public.access.tablesdbdesign
Pam
external usenet poster
 
Posts: 131
Default AutoNumber

Fred,

Thank you for the simple solution. I never thought of making the leading
digit invisible or just plain dropping it and starting the new db with my
four-digit numbers and letting the autonumber pick up from where the "in
progress" jobs leave off.

This is so much easier.

Thank you for your input.
Pam

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




  #15  
Old April 14th, 2010, 12:26 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default AutoNumber

Pam

Please see comments in-line below...

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











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

Hi Pam,

Not sure I understand, but that's fine.

My advice for your new design (in the area visible in this discussion) is
that each record in each table have a UNIQUE (never ever to be repeated, and
never, ever to be changed once it is in) identifier (= Primary Key) for every
record in every table. In your case, to assure that "external" forces will
never have the "hooks" for force to you violate "never ever change or repeat
it", make it an internal number that nobody ever sees, and which has no real
world meaning.

Sincerely,

Fred


 




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 11:51 AM.


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