Stuart McLachlan
stuart at lexacorp.com.pg
Fri Oct 1 12:06:22 CDT 2010
The second version will list *all* checks more than 13 days after the first operation which are before the second operation. If you just want the last check, the simplest way is to wrap the second version in another query: SELECT Patientnr, FirstOp, SecondOpDate, Max(FollowUpDate) AS LastFollowUp, Max(DaysAfterFirst) AS Days FROM qryDoubleOpFollowUps GROUP BY Patientnr, FirstOp, SecondOpDate; -- Stuart On 1 Oct 2010 at 16:21, Pedro Janssen wrote: > > Maybe i wasn't clear enough. Except the criteria i mentioned in my > question, there is one more thing that is important: I only need the > count of the days of the last postup-check between the two dates in > case of an operation at both eyes. > > I don't know if the queries have to be adjusted, but i can't test it > now, because i just went home, and have again time on monday to do so. > > Thanks already for your help. > > Pedro > > > > > > > > > > Op 1-10-2010 13:46, Stuart McLachlan schreef: > > This: > > > > SELECT tblCataracts.Patientnr, Min(tblCataracts.Date) AS FirstOp, > > Max(tblCataracts.Date) AS SecondOpDate, tblCataracts_1.Date AS > > FollowUpDate, [tblCataracts_1].[date]-Min([tblcataracts].[date]) AS > > DaysAfterFirst, > > [tblCataracts_1].[date]-Max([tblcataracts].[date]) AS > > DaysAfterSecond > > FROM tblCataracts LEFT JOIN tblCataracts AS tblCataracts_1 ON > > tblCataracts.Patientnr = tblCataracts_1.Patientnr WHERE > > (((tblCataracts_1.Code) Like "19*") AND > > ((tblCataracts.Code)="031241")) GROUP BY tblCataracts.Patientnr, > > tblCataracts_1.Date HAVING (((Count(tblCataracts.Patientnr))>1) AND > > (([tblCataracts_1].[date]- Min([tblcataracts].[date]))>13)); > > > > > > will return this: > > > > Patientnr FirstOp SecondOpDate FollowUpDate DaysAfterFirst DaysAfter > > Second 01111111 1/04/2010 25/04/2010 20/04/2010 19 -5 > > 01111111 1/04/2010 25/04/2010 26/04/2010 25 1 > > 02222222 1/05/2010 1/06/2010 4/06/2010 34 3 > > > > > > If you want to exclude follow ups after the second operation, you > > can use the following to just return the first record: > > > > SELECT tblCataracts.Patientnr, Min(tblCataracts.Date) AS FirstOp, > > Max(tblCataracts.Date) AS SecondOpDate, tblCataracts_1.Date AS > > FollowUpDate, [tblCataracts_1].[date]- Min([tblcataracts].[date]) AS > > DaysAfterFirst FROM tblCataracts LEFT JOIN tblCataracts AS > > tblCataracts_1 ON tblCataracts.Patientnr = tblCataracts_1.Patientnr > > WHERE (((tblCataracts_1.Code) Like "19*") AND > > ((tblCataracts.Code)="031241")) GROUP BY tblCataracts.Patientnr, > > tblCataracts_1.Date HAVING > > ((([tblCataracts_1].[date]-Min([tblcataracts].[date]))>13) AND > > (([tblCataracts_1].[date]-Max([tblcataracts].[date]))<0) AND > > ((Count(tblCataracts.Patientnr))>1)); > > > > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > ------------------------------- > > > > Dear List, > > > > > > For the Cataract performance indicators, i have to find out how > > > many patients with a cataract operation at both eyes, have done a > > > post-checkup after the first operation, but not before 14 days. > > > > > > So i have a table with three fields. > > > > > > Patientnr Code Date > > > 01111111 031241 01-04-10 > > > 01111111 190013 20-04-10 > > > 01111111 031241 25-04-10 > > > 01111111 190011 26-04-10 > > > 02222222 031241 01-05-10 > > > 02222222 190011 05-05-10 > > > 02222222 190012 07-05-10 > > > 02222222 031241 01-06-10 > > > 02222222 190013 04-06-10 > > > 04444444 031241 01-07-10 > > > 04444444 190013 20-07-10 > > > 04444444 190013 22-07-10 > > > > > > > > > The code for cataract operation is: 031241 > > > The codes for post-checkups are: 190011, 190012, 190013 > > > > > > > > > as result i would like: > > > Patientnr Days > > > 01111111 19 > > > > > > > > > Patientnr 02222222 does not match the criteria, because the > > > post-checkup is 4 days after the first operation. > > > > > > Patientnr 04444444 does not match the criteria, although the > > > post-checkup is 19 days after the operation, there isn't a second > > > operation. > > > > > > > > > I'll hope someone can help me with this. > > > Although i can level the patients down to those who have two > > > operations and checkups, but when there are more then one > > > checkup, i have to control them by hand. This is a lot of work. > > > > > > Thanks > > > > > > Pedro > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >