I'm trying to write a query based on the following tables:
Patient ID #
Patient's Last Name
Patient 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
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).
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.....
[edit - I'm using access 2007 if that makes a difference.....]
Last edited by darth.pathos; 05-10-11 at 11:06.
Reason: Forgot Version of Access
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.
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...