Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: concatnating fields

    Hi,
    I have a table say "tbl_route" and i have three fields in it "Dep", "Dest" and "Priority" (amung other fields).... I want to create another field and that field "lineID" and it should be auto-populated with the data values such that Dep+'-'+Dest+'-'+Priority

    Example a trip form 'ABC' to 'XYZ' on a 'EXP' bus should have the lineID "ABC-XYZ-EXP"

    How do I do this in a table? (and not a query preferably)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't do this in a table.. its derived data

    in a query youd do this aas
    select dep & "-" & Dest & "-" Priority as LineID from mytable

    you could set that value as part of a forms before update event. eg:-
    mycontrol.value = acontrol.value & "-" & bcontrol.value & "-" & ccontrol.value

    why do you believe you must have this as a column in the table,a s opposed to creating it on the fly as required?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    i want it in a table because that table is related to some other tables too and i wan the lineID to be part of the relationship.
    I cant do that with a query right...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so define your relationship with the 3 separate columns as part of the same relationship. you can make it easier for yourself by defining those columns as part of that tables primary key, and then it will automatically propogate those columns when you use that key as a foreign key in other tables
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...by trying to store those in a column in a table you are creating a major headache for yourself.. there is a risk that someone could change one of the component elements, but forget to update the composite column meaning that your data will loose its integrity and value
    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
  •