Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: System to decode a denormalized table (rehash old question)

    I've asked about this before but when I reviewed the thread I found two things:
    1) I didn't ask the question very well
    2) Things have changed a bit since then

    The original thread can be found here, but I think I can explain it better.

    We have a casemix system that is used to pull a lot of data. I can do a lot of things with it but one of the increasingly popular requests is what I call the "data dump". The system pulls the row for a given visit from one table. Then it pulls all rows associated with those visits and denormalizes them into a single row so that you can pour it all on a spreadsheet where each visit, all the diagnoses and procedures are on one row, to make it easier for people to work with.
    Then I have the pleasure of decoding all the columns. The folks who designed the system where nice enough to give me access to the lookup tables so I can link in and decode many of the specifics. The problem comes when I get to those denormalized procedures and diagnoses.
    It used to always be 15 columns and it was a request I didn't get a lot so I would build one query for each diagnosis and procedure. Now I am getting this request more often and the number of columns has increased to 30 for each of the two types of columns. It's pretty much a days work to run the dump then decode all the columns individually.

    Can someone help me think of a way to do this better, quicker, more modular. I assume a lot of VBA would be involved, maybe something that looks at the table and reads the number of columns named Diag_01, Diag_02....Diag_zz (since it is generated automatically, there is a naming convention in place) and creates a query for each. Based on experience, about halfway through the process I need to create a new table, the queries start erroring out if i just keep building query upon query.

    Feel free to just shout out some ideas...of course, if you know a proven solution, that would be totally awesome.

    Thanks all for reading

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    could you stuff the PK(s) into the excel spreadseet and go back to the original data?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just so I understand - you want VBA to create something like:
    Code:
    SELECT pk_col, diag_1
    FROM myTable
    UNION ALL
    SELECT pk_col, diag_2
    FROM myTable
    UNION ALL
    SELECT pk_col, diag_3
     FROM myTable
    UNION ALL
    SELECT pk_col, diag_4
     FROM myTable.....
    ????

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by healdem
    could you stuff the PK(s) into the excel spreadseet and go back to the original data?
    I'm not sure I understand the question entirely. In the case of the diagnosis, the diagCode is the PK or the lookup table, in the case of the patient data visit number is PK.


    Pootle Flump,
    I'm not sure if that is what I want, if I do a UNION query, aren't I adding rows?

    I think I should do a little prototype DB, cause it is a weird idea.

    Lemme see what I can whip up in an hour or so.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    The system pulls the row for a given visit from one table. Then it pulls all rows associated with those visits and denormalizes them into a single row
    Quote Originally Posted by starkmann
    Pootle Flump,
    I'm not sure if that is what I want, if I do a UNION query, aren't I adding rows?
    Well - yeah

    Perhaps you should show us some sample data (wrap it in [ code] tags so formatting "sticks") and what result you want from the query.

    Gotta say Mark's idea ain't bad. He is saying that if you get the person's id why not just look up the diagnoses in the database rather than fiddle about with the report? Only problem is if they changed in the meantime....

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    I'll go you one better than code in tags. The attachment is an Access 2003 file that has a very small set of made up data. Of course there are many times more columns and rows in the real thing. The tables represent the data I have to work with and the queries show how I currently do the decoding.

    There is no real data in this so you may do whatever you like with it.


    Can I go back to the original data? I can, I would then need to denormalize it myself but it is a perfectly valid option. Would it be easier to skin the cat that way?
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wish I had time to look into this properley, but one thing I will say is that it makes me feel slightly better about the query I had the pleasure of looking at yesterday
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    oh, dish dish, what were you up against George?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So all you want to do is replace the diagnosis code with the name of the diagnosis?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by starkmann
    oh, dish dish, what were you up against George?
    http://www.dbforums.com/showpost.php...ostcount=14612
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    So all you want to do is replace the diagnosis code with the name of the diagnosis?
    I've reread your question and I was totally over complicating it. Looks like I got it here ^^. I originally thought you wanted to normalise the data again.

    Why not denormalise the actual names in the first place rather than the codes?

    Also, you do know you can include a single table in a query as many times as you like right? You don't need three queries for the three diagnosis code columns.

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    Pootle,

    When I had tried it previously recalling the same tables (in Access mind you) it crashed even sooner in the process of getting all the columns filled in. I could probably do a few rather than on at a time but I still have to build the whole process by hand for each new request, I'm hoping to create something that cuts the process down to just a couple queries rather than 10 -20 or if it does require 30 queries, it is automated in making all those queries. I'll see how far I can stretch that one table before it snaps though.

    I don't denormalize the names because this is invariable for someone who is looking at the data per patient. Really, the original data could be said to be denormalized by name, this was the idea that healdem was getting at, I think, that I pull from the table that has one row for each diagnosis for each visit. I'm thinking he's maybe onto something. I haven't tested the theory yet but I think I may even be able to envision a process.

    Personally, I don't think they have really thought about their request before beginning it and I have tried to have a chat about "what do you really want to know" but they don't know. I think in a lot of cases, the just want to sit down and look for trends, any trend between their patients. The "finished product" has 166 columns which seems insane to me.

    that's....em...impressive.
    It looks like it's just trying to check for almost every possible variation and allow it anyway, but I'm still not that good at SQL (but I'm reading Joe Celko when I can )

Posting Permissions

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