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