[AccessD] query to slow

SELECT
	A.Patientnr,
	MAX(A.UitvoerDatum) AS MaxReturnDate
FROM qryKreatinine_LaatsteDatum AS A
GROUP BY
	A.Patientnr,
ORDER BY A.Patientnr;

Would work, but what is the SQL for qryKreatine?

May be able to speed it up a bit as well.



On Tue, Sep 28, 2010 at 10:04 AM,  <pedro at plex.nl> wrote:
> Dear List,
>
> i have a query <qryKreatinine> with about 400000 records.
> This query contains patientnumbers and dates
>
>
> <qryKreatinine>
> Patientnr   UitvoerDatum
> 01000616    1-6-2009
> 01000616    29-7-2009
> 01000661    1-5-2010
> 01000661    3-6-2010
> 01000661    3-6-2010
> 01000832    11-9-2009
> 01000832    17-9-2009
> 01000832    1-3-2010
>
> I need to have for each Patientnumber, the last date (see final result).
> I did this with two queries (see below), but only with low number queries (a few thousand records.
> With more then 500000 records it takes to much time to get the result.
>
> Can i do this with 1 query in a normal time span for 400000 records??
>
>
> Thanks
>
> Pedro
>
>
>
>
> SELECT qryKreatinine.Patientnr, qryKreatinine.UitvoerDatum
> FROM qryKreatinine
> WHERE qryKreatinine.UitvoerDatum =(SELECT MAX(T.UitvoerDatum) as MaxDate
> FROM qryKreatinine as T
> WHERE T.Patientnr = qryKreatinine.Patientnr);
>
>
> result:
> Patientnr   UitvoerDatum
> 01000616    29-7-2009
> 01000616    29-7-2009
> 01000661    3-6-2010
> 01000661    3-6-2010
> 01000661    3-6-2010
> 01000832    1-3-2010
> 01000832    1-3-2010
> 01000832    1-3-2010
>
>
>
>
> SELECT qryKreatinine_LaatsteDatum.Patientnr, qryKreatinine_LaatsteDatum.UitvoerDatum
> FROM qryKreatinine_LaatsteDatum
> GROUP BY qryKreatinine_LaatsteDatum.Patientnr, qryKreatinine_LaatsteDatum.UitvoerDatum
> HAVING (((qryKreatinine_LaatsteDatum.Patientnr) In (SELECT [Patientnr] FROM [qryKreatinine_LaatsteDatum] As Tmp GROUP BY [Patientnr])))
> ORDER BY qryKreatinine_LaatsteDatum.Patientnr;
>
> final result:
> Patientnr   UitvoerDatum
> 01000616    29-7-2009
> 01000661    3-6-2010
> 01000832    1-3-2010
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>