[AccessD] query to slow

I think all you need is

SELECT DISTINCT qryKreatinine.Patientnr, Max(qryKreatinine.UitvoerDatum) AS MaxOfUitvoerDatum
FROM qryKreatinine
GROUP BY qryKreatinine.Patientnr;

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
Sent: Tuesday, September 28, 2010 12:05 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] query to slow

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