[AccessD] need help with date-query

  Hello Stuart,

thanks for the help.
Its just what is needed.

Pedro




Op 1-10-2010 19:06, Stuart McLachlan schreef:
> 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;
>

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