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