[AccessD] first five dates per year

Pedro, if I remember right, Gustav's union query essentially provides
records for each year.  Each segment of the union query is for a seperate
year.  Thus, to show results for the last 20 years, you would need 20
segments in the Union query.  If you were making this SQL on the fly, that
would be the fastest option.

However, to speed things up for my query, make sure that you the date field
indexed.  That will speed things up immensely.

Drew

-----Original Message-----
From: Pedro Janssen [mailto:pedro at plex.nl]
Sent: Friday, March 07, 2003 3:30 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] first five dates per year


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
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com