Kaup, Chester
Chester_Kaup at kindermorgan.com
Tue Sep 28 10:28:59 CDT 2010
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