Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010

    Unanswered: Library Reference Codes


    i am trying to catalogue our library and want to create a Reference code with information from 2 other columns. Additionally if the code already exists i would need it to put an increasing number behind every version of this code.


    Country: Colombia
    Topic: Human Rights
    Reference code: CO.HR.01

    Thank you in advance for your help.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so you need some form of autonumber coding
    using the ISO 2 digit codes makes sense
    using a code such as HR to represent 'Human Rights' is a bit trickier as that code infers a meaning which could be misused or misinterpreted, eg HR may mean to others Human Resources or some other abbreviation.
    I'd be tempted to store the data as 3 separate elements, store the digits as numbers bu display padded with the appropriate leading zeros. consider the likelihood that the users may overflow the number ie get up to 99. if you think they will never exceed 99 then use at least 3 digits eg 999 as the upper limit, possibly even use 4 digits to cater for unexpected problems.

    getting the ascending number is relatively trivial
    if you use a DMAX to get the current highest value and then add to the number you should be there or thereabouts. specify the country code and the type code as part of domain term. look n the help or online for how to use dmax
    if you must store the data as a composite field you will have to do a lot more work to break up the composite key

    bear in mind if this is a multi user DB you will need to lock the DB for the time it takes to find the current highest number. there is a sneakier way of doing this, write the record, then after the record is created go back and update the ID based on the then available knowledge. secure a record lock, DMAX, get the number, update this record, then release the lock. the advantage is that you will lock out other users for the minimum possible time. don't be tempted to secure a number before writign the record.. why the write may fail, someone else may be wanting to create or edit a record and you don't want to have record locks causing such issues.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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