[AccessD] first five dates per year

Pedro Janssen pedro at plex.nl
Fri Mar 7 15:30:00 CST 2003


Hello Gustav,

Drew Wutka his query is working (Thanks for that Drew), but you were right
it is very slow. For 10000 records it almost takes 20 minutes.
You gave me a unionquery that i can't seem to work. Maybe i don't understand
the fields in your query and what do you mean with:
> If you know the maximum number of years to list, you can create a
> union query like this where Year is the first year to list:

Could you explain a little more.

TIA

Pedro Janssen

----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Pedro Janssen" <accessd at databaseadvisors.com>
Sent: Wednesday, March 05, 2003 8:36 PM
Subject: Re: [AccessD] first five dates per year


> Hi Pedro
>
> > I have a table with 10000 records. I would like to filter out, by
> > query, the first 5 or 10 dates per different years.
> > Is this possible.
>
> For a large table the use of a subquery may be painfully or even
> unacceptably slow.
> If you know the maximum number of years to list, you can create a
> union query like this where Year is the first year to list:
>
> <SQL>
>
> PARAMETERS
>   Year Short;
> SELECT TOP 5
>   Year AS Year5,
>   ID
> FROM
>   tblTrans
> WHERE
>   DateTrans >= DateSerial([Year],1,1)
> UNION
> SELECT TOP 5
>   Year+1 AS Year5,
>   ID
> FROM
>   tblTrans
> WHERE
>   DateTrans >= DateSerial([Year]+1,1,1)
>
>   ... <add more sections as needed>
>
> UNION
> SELECT TOP 5
>   Year+n AS Year5,
>   ID
> FROM
>   tblTrans
> WHERE
>   DateTrans >= DateSerial([Year]+n,1,1)
> ORDER BY
>   Year5,
>   ID;
>
> </SQL>
>
> /gustav
>
> _______________________________________________
> 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