[AccessD] need help with date-query

  Hello Stuart,

"Patient  02222222 also had a check up 34 days after the first op.  Are you saying that it
doesn't count because it is after the second op?"

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	DaysAfterSecond
> 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