David McAfee
davidmcafee at gmail.com
Tue Sep 28 16:15:35 CDT 2010
Well, it is eliminating one queries. Probably won't help much, but it doesn't hurt either. :) D On Tue, Sep 28, 2010 at 1:55 PM, Pedro Janssen <pedro at plex.nl> wrote: > > 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 >>> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >