[AccessD] first five dates per year

Gustav Brock gustav at cactus.dk
Wed Mar 5 13:37:00 CST 2003


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




More information about the AccessD mailing list