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