No subject

Couls you give me the sql for Year and Year+1.

Maybe then i undersatnd things better.

Greetings

Pedro






----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Pedro Janssen" <accessd at databaseadvisors.com>
Sent: Saturday, March 08, 2003 11:55 AM
Subject: Re: [AccessD] first five dates per year


> Hi Pedro
>
> > 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.
>
> Sure. In addition to the correct comments of Drew, here is the union
> query which collects data from maximum three years. If you need more
> years, simply insert more "Union Select ..." sections where you for
> each section increase by 1 the number to add to Year.
>
> <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)
> UNION
> SELECT TOP 5
>   Year+2 AS Year5,
>   ID
> FROM
>   tblTrans
> WHERE
>   DateTrans >= DateSerial([Year]+2,1,1)
> ORDER BY
>   Year5,
>   ID;
>
> </SQL>
>
> You will, of course, have to change the names of table and fields to
> those of your table.
>
> /gustav
>
> > ----- 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>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>