[AccessD] first five dates per year

Hi Pedro

Well, didn't imagine you had one field only.

Anyway, here's the SQL to extract those dates for three years.
If dates are not unique, you'll have to replace every occurrence of
UNION with UNION ALL as shown:

<SQL>

PARAMETERS
  Year Short;
SELECT TOP 5
  Year AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year],1,1)
UNION ALL
SELECT TOP 5
  Year+1 AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year]+1,1,1)
UNION ALL
SELECT TOP 5
  Year+2 AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year]+2,1,1)
ORDER BY
  Year5,
  [datum];

</SQL>

If dates are not unique but you wish to extract the first five unique
dates for each year, you'll have to group by the date like this:

<SQL>

PARAMETERS
  Year Short;
SELECT TOP 5
  Year AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year],1,1)
GROUP BY
  [datum]
UNION
SELECT TOP 5
  Year+1 AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year]+1,1,1)
GROUP BY
  [datum]
UNION
SELECT TOP 5
  Year+2 AS Year5,
  [datum]
FROM
  tblTrans
WHERE
  [datum] >= DateSerial([Year]+2,1,1)
GROUP BY
  [datum]
ORDER BY
  Year5,
  [datum];

</SQL>

All this, of course, assumes that your date field is of type
date/time-value.

/gustav

> When i use only the part of the query with Year the result is good, but the
> Parameters is asking for the year and afterthat for Year5. I don't
> understand this.
> When i use Year and Union Year+1 i get result that don't fit.
> Maybe it has something to do with the fieldnames. In your query which field
> is ID and which field is DateTrans.

> For example in TblTrans i only have one field [datum].
> From this field [datum] i need each year the first 5 dates.
> Couls you give me the sql for Year and Year+1.

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