[AccessD] query to slow

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
>>



More information about the AccessD mailing list