SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    Northern Ireland
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help MS Access please

    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

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the "CX/00/**" code some sort of ID column?
    (Don't know if I like that, but anyway...)
    If a value like "CX/02/63" is stored in column named x, in a table called complaints, then
    select max(x) from complaints
    will return the last complaint entered
    Put this value in a variable named y, and
    Left(y, InStrRev(y, "/")) & (Right(y, Len(y) - InstrRev(y, "/"))) + 1
    will give you the next value ("CX/02/64")

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why I don't like "CX/02/63" as ID value
    1. It's not a number, it's a text
    2. Every value start with "CX" = no new information
    3. 02 = the year, saving date_entered in it's own column would be better

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2002
    Location
    Northern Ireland
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Jofa
    I'll bear all your advice in mind.
    Regards
    Andrew


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •