Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: Primary key from concatenation?

    Hello everybody, this is may first post on the forum, hope somebody can help me.
    In a table with several fields I would like to concatenate two of these fields (one containing an employee number and one containing a week number, both field are formatted as text) to fill in a third field that would be the primary key.
    Is this possible? I know concatenation is obtained though a query but how do I assign the value generated by the query to the primary key field?
    Thank you in advance.
    Greetings

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kilmister_2
    Is this possible?
    Yes. The next question should be "Is this advised?" and the answer would be "definitely not!"

    You should instead use a composite key. A composite key is where you declare two or more columns to act as the primary key, rather than concatenating them into a single column. Ideally do this via the Indexes pop up so you can put the columns in the optimal order in the index.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can achieve this without concatenating the columns
    ...you can make two or more columns a primary key in the table designer
    .. or you could do it the old school way using SQL's very own DDL
    I hope when you say the fields are formatted as text you mean that the display is text, NOT the storage.. please don't even go there.. numerics as numerics, dates and times as datetime......

    unless you are disciplined ie zeropadding numbers you are creating a rod for your back by storing numbers as text.

    move the two(or more) columns so they are adjacent, tradition would suggest they are the top of the list
    select both columns select the primary key button
    ...jobsagoodun
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    select both columns select the primary key button
    ...jobsagoodun
    I was going to suggest this, but wasn't sure if you can control the order of the columns in the index - can you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2008
    Posts
    3
    Thank you both for the reply. The two columns are now primary key.
    Now just for a better understanding of the concatenation subject, supposing the two columns were not going to be primary key, and I simply wanted to use the concatenation of the two values in order to fill in the third column, what would the solution be?
    Thanx again to both for your help

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Use a query to concatenate the results... but you would never stuff that back into a table.

    For example, in a query you could use:

    NewFieldname: EmployeeID & WeekID

    To concatenate two fields into one new expression.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't do it..its derived data.... you can get to that in a query

    but if you must
    myconcatenatedvalue = mycolumn1 & mycolumn2

    for a number
    myscrewedupsystemvalue = format(employeenumber,"00000000") & format(weeknumber,"00")

    if you want to screw up your data with an update query
    update mytable set mycompositecolumn= format(employeenumber,"00000000") & format(weeknumber,"00")

    there probably should be a where clause tacked onto the query, possibly somethign like...
    where lackofsense=true
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Tee Hee... I won a snipe... and my God that must have been close to the exact time!

    Probably what caused your double-post
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2008
    Posts
    3
    Thanx to all

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh but ST, you lost because you tried to concatenate two (assumingly) integer fields without explicitly converting them to character first
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    But I don't need to
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You were doing oh so well!

    ...shame
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    georgie, the expression is "case in point"

    must i continue to teach english to the kids born there?

    ... shame
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Who says I was using that expression? I was showing my case and my point; both happened to be the same offending article...

    ...that's my story and I'm sticking to it!
    George
    Home | Blog

Posting Permissions

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