Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: multiple links from same row to one cell

    I hope the title made sense but I'll try to explain better.
    I have a table that has a lot info in it but the important bit is like this:
    VisitNum(pk), Diag01, Diag02, Diag03....Diag15

    I have another table with
    DiagCode(pk), DiagText

    What I have been asked to do it to put the DiagText data where Diag01 - 15 are (as I'm sure you can guess, it is codes in there now and the text will be more "human readable"). I have looked at it a couple different ways but haven't been able to solve it. If I try to like Diag01 to DiagCode and Diag02 to DiagCode and so on, I get no results. I considered making a query where I had DiagText, DiagCode AS DiagC01, DiagCode AS DiagC02.....and so one but that seems like it wouldn't return anything or at least not anything relevant. I tried doing it through series of queries changing one code per query except that by the time I get to Diag6 the query is really slow to load and I can't image how long Diag 15 will take.

    Any suggestions?

    thanks very much.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you are only doing this once, just run 15 update queries...

    UPDATE TABLE1
    SET DIAG01=DIAGTEXT
    FROM TABLE2
    WHERE TABLE1.DIAG01=TABLE2.DIAGCODE

    You'll just have run it, then change DIAG01 to DIAG02, and so on...

    BUT:
    This is denormalization, and I wouldn't do it. Actually, I'd restructure
    TABLE1, and supply the requested data via a query.

    TABLE1
    VisitNum
    DiagNum
    DiagCode (fk)

    With VisitNum and DiagNum being the primary key
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    Any suggestions?
    Normalise the table:
    http://r937.com/relational.html
    check out first normal form. Your complications are a result of this.

    I suspect you will want a get around (kludge) - well it is ugly and slow. Something like:
    Code:
    SELECT VisitNum, MAX(a.diagtext) AS diagtext_1, MAX(b.diagtext) AS diagtext_2, MAX(c.diagtext) AS diagtext_3 
    FROM 
    VisitTable AS vt 
    LEFT OUTER JOIN
    DiagTable AS a ON a.DiagCode = vt.DiagCode
    LEFT OUTER JOIN
    DiagTable AS b ON b.DiagCode = vt.DiagCode
    LEFT OUTER JOIN
    DiagTable AS c ON c.DiagCode = vt.DiagCode
    GROUP BY VisitNum
    and repeat for all fifteen columns. I am afraid I can never remember where access likes the parentheses in the FROM clause so you are probably best using this to guide you writing\ designing your own query.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's
    normalisation: 2
    as it is now: 0
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where will you put Diag16 ?

    anyhow,
    SELECT diag1 & diag2 & diag3 AS combinedDiag
    ...might be the direction you are looking for.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    Interesting to see normalization come up twice, I'm learning about normalization this week in my class I am taking.

    I should clarify with the issue above ( I will try to use the update table solution, I think that'll do it) is using an archive table created by someone I don't know and maintained by a guy I have only had a phone conversation with so I can't actually effect any changes to it.
    That said, I will read the suggested page as well as my chapter for the week to avoid such issues with any tables I may create.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Well, as long as you can blame someone else, and promise to normalize in the future, we'll let you slide!
    Inspiration Through Fermentation

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    ....[the table is] maintained by a guy I have only had a phone conversation with so I can't actually effect any changes to it.
    Sounds like he has passed on his maintenance responsibilities like a pro
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    I can always blame somebody else. :-)

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like you jjust want to concatenate the strings like so:

    SELECT Diag01 & ' ' & Diag02 & ' ' & Diag03 ...
    FROM MyTable
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2007
    Posts
    348
    I actually need to turn the alpha numeric code into the text that describes it in english. ex:
    diag01 = 7955
    diagCode = positive tuberculin skin test without active tuberculosis
    (source: http://icd9cm.chrisendres.com/index....ld&recordid=98)

    I'm trying theupdate table thing, I keep bombing out with a mesage that I used all the temporary space available (I don't have the exact error in front of me). Tomorrow I will try doing it in smaller chunks and see if that works.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How many rows are in the table? If you're getting that kind of error message
    just updating one field at a time, I think you'll run into bigger problems long before you get to field 15.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Feb 2007
    Posts
    348
    4128 rows in the table.
    I initially tried to do thirty columns (diag01 - diag15 and proc01 - proc15), then I tried 15, then it time to go. Today I will try 7, 5 3, 1 I'll get there. According to the help popup there is some kind of Windows setting I can monkey with to improve this but I haven't felt daring today.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm going to jump back in here and ask - is this combination of multiple fields into a single row's cell really necessary?

    From a users perspective you can use simple concatenation to add them all together to make one string when necessary (in the GUI) but do you really need to make one field for all of them?
    George
    Home | Blog

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Unless I completely misunderstood your table structure, you can't update more than 1 column at a time (at least not with SQL in Access).

    I'm curious about which windows setting it's telling you to change?
    Inspiration Through Fermentation

Posting Permissions

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