Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Deriving field value from another field

    I need to derive an automatic field value that is based from another field.

    Example:

    compet_code
    4013.01

    lvl_number
    3

    The lvl_number is inputted manually here, but I need to make the lvl_number field get an automatic value from the 4th character in the compet_code (namely the 3 in 4013.01)

    how do I do it?


    Also, what's the best practice?
    1) having two table with lvl_number ; compet_code AND compet_code ; compet_inf

    2) one table containing compet_code ; compet_inf; lvl_number (where the level is derived from compet_code) ?

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    It's generally not done to store derived values. It's easier to derive them whenever you need them.

    If you always want the fourth character of compet_code to be lvl_number, then
    Code:
    lvl_number: Mid(compet_code, 4, 1)
    will give this.

    I would advise that you have one table that contains non-derived data, and a query to derive anything required from that.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Assuming Compet_code is a composite number...
    for me best practise would be to store the individual elements separately and create the compet_code as required in a query. if the DB starts storing significantamounts of data then additional string manipulations will cause perfromance bottlenecks equally you may struggle to satisfactorily index required elements

    pulling out the nth character of a numeric value can be tricky unless you limit the numbers to a specific format.
    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
  •