[AccessD] need help with date-query

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));


-- 
Stuart



On 1 Oct 2010 at 12:48, pedro at plex.nl wrote:

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