Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: How to write this query??

    Hi all,

    I'm trying to write a query based on the following tables:

    Table A
    Patient ID #
    Patient's Last Name
    Death Date

    Table B
    Patient ID #
    Procedure ID
    Type of Procedure
    Date of Procedure

    What I need to do is develop a dataset to be used in Time to Event analysis, so I need to have

    Table A.Patient ID | Table A.Patient's Last Name | Table A.Death Date | Table B.Type of Procedure | Table B.Date of Procedure | Table B_1.Type of Procedure | Table B_1.Date of procedure

    where:

    1) All records from Tables A and B are returned
    2) Records from Table B and B_1 are returned where B_1.Date of Procedure is greater than B.Date of Procedure (or is null), and B_1.Type of Procedure is the same as B.Procedure (or is null).

    My end result would look something like

    1233|Smith|NULL |Procedure A|Jan 1 2011|NULL | NULL
    1234|Jones|Sep 14 2007|Procedure C|Oct 1 2005|Procedure C|Oct 3 2005
    1234|Jones|Sep 14 2007|Procedure D|Oct 16 2005|NULL |NULL

    so because I am looking at the Time to a repeat of the procedure, this is the only way I can think of putting everything together. The problem I'm having right now is that I'm getting duplicate rows of the same data, and I'm stumped as to how i can write my query to get what I need.

    Any help would be greatly appreciated.....
    Chris

    [edit - I'm using access 2007 if that makes a difference.....]
    Last edited by darth.pathos; 05-10-11 at 12:06. Reason: Forgot Version of Access

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    It is difficult for me to understand your goal. But as a db designer it strikes me that your Table A & B are classic Parent/Child 1:many relationship.

    It seems your need to put B record fields side-by-side, rather than their normalized stack is the problem. If you did not have this layout requirement - are you able to create/assemble the correct records? It would seem to me that is the first step - and then deal with layout as a second issue.

    Hope this helps.
    www CahabaData com

  3. #3
    Join Date
    Jan 2011
    Posts
    8
    Hi NTC - Thanks for the reply.

    I can easily join Tables A & B (using the regular inner join); that gives me all the patients that had a Procedure (or multiple procedures) but did not have a repeat of the procedure(s). The problem arises when I try and build a second query to show Table A+B+B_1, where I try doing things like (B.Date of Procedure < B_1.Date of Procedure AND B.Procedure = B_1.Procedure), I get all sorts of duplication (Procedure 1 and Procedure 2, and then in the next row Procedure 2 and Procedure 1). Ugh.

    I doubt I'm explaining this properly...let me know if you need more info...
    Thanks
    Chris

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    well I'll restate my original post; begin by making a query that results in the correct records.

    don't worry about the layout at this point

    your reply was 'get all sorts of duplication '......so you need to resolve this point first and have a clean record set.

    my opinion.
    www CahabaData com

  5. #5
    Join Date
    Jan 2011
    Posts
    8
    Thanks NTC....because they need this graph by the end of today and I don't have time to argue with Access, I've gone through and set the data set up manually in Excel - it's not pretty but it's done.

    Thanks again for your help - I'll definitely be back to working on this problem in the next day or so and let you know what I accomplish......

    Chris

Posting Permissions

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