Pedro Janssen
pedro at plex.nl
Tue Sep 28 15:55:59 CDT 2010
David, its a normal selectionquery. Is it better to change it to a tablemakequery and do the "max"query from the table? Does that speed things up? Pedro Op 28-9-2010 19:06, David McAfee schreef: > 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 >>