Help with next number in sequence in Access
Hello Folks,
I have a "Complaints" Database on MS Access at work and I would welcome some expert assistance in setting up a system to automatically flag up the next number in sequence when logging a new complaint.
At present the DB has 200 entries and is split into into years.
CX/00/** - For Complaints from 2000 to 2001
CX/01/** - For Complaints from 2001 to 2002
CX/02/** - For Complaints from 2002 to 2003
The last set of asterixes represent the nymber of cases logged each particular year.
Each reference no. also serves as a hyperlink linking to an Adobe Acrobat link of the relevant case.
Basically what I need is a system to automatically flag up the next number in sequence when logging a new complaint i.e. this year if there have been 63 previous loged cases, to flag CX/02/64 as the next number.
What seems to confuses things whe I try to set this up myself are the that the Ref. Nos. are also links and the fact that although there are 200 cases in total, only a small percentage relate to the given year.
My knowledge of MS Access is very basic so please the more detail the better.
Regards
Andrew
ROB posted this at 00:21 — 10th July 2002.
They have: 447 posts
Joined: Oct 1999
i believe you'd have to handle this in a form with VBA code. if all you wanted were an auto-incrementing number you could create a field of the type 'autonumber', but that would just be a number and wouldnt fit your current CX/YY/** format.
with an enterprise databases, you could do it (i think). Access isn't really very ANSII compliant, and doesn't support many SQL features.
Peter J. Boettcher posted this at 12:38 — 10th July 2002.
They have: 812 posts
Joined: Feb 2000
Access is painfully inadequate in this area. Once you've defined a field as a certain type you cannot change it to autonumber, and even if you did it would start at 1. With a RDBMS you could easily change the datatype to autonumber (identity) and tell it what number you would like to start at.
You will have to do a lookup to see what the last reference number was and create a record with the new reference number.
Maybe it's time to upgrade to a better database?
PJ | Are we there yet?
pjboettcher.com
Andrew_Samuel posted this at 01:20 — 13th July 2002.
They have: 82 posts
Joined: Jun 2002
Cheers Folks.
I'll bear all your advice in mind.
Thanks for the replies
Regards
Andrew
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.