<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=251313316-05032003>Okay,
simple table, ID (AutoNumber), SomeText (text field) and MyDate (Date
field). I called this table tblTest. The following SQL will show the
first five records for each year:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=251313316-05032003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=251313316-05032003>SELECT
ID, SomeText, MyDate<BR>FROM tblTest AS T1<BR>WHERE ID In <BR>(SELECT TOP 5
ID<BR>FROM tblTest<BR>WHERE Year(MyDate)=Year(T1.MyDate)<BR>ORDER BY
MyDate);<BR></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=251313316-05032003>The
subquery pulls up a group of up to 5 ID's, which the main query then uses to
determine if it's ID should be displayed or not. I used an ID field,
because if there are more then 5 dates (say you have 6 records for January 1st
in one year...) you would get more then 5 records back if you made the IN
statement compare the date field to the subquery.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=251313316-05032003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=251313316-05032003>Drew</DIV></SPAN></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Pedro Janssen
[mailto:pedro@plex.nl]<BR><B>Sent:</B> Wednesday, March 05, 2003 10:24
AM<BR><B>To:</B> AccessD@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD]
first five dates per year<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Thanks for your help Juan. </FONT></DIV>
<DIV><FONT face=Arial size=2>This is an usefull function. Never noticed
that is was present. But it gives me only the Top 5 of the total of
records.</FONT></DIV>
<DIV><FONT face=Arial size=2>I tried to give me the top5 dates each year with
this function, but i can't make it work</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Drew, i don't have a criterium for the records i
don't want to. I just need the first 5 each year.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Pedro</FONT></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=mastercafe@ctv.es href="mailto:mastercafe@ctv.es">MastercafeCTV</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, March 05, 2003 12:30
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] first five dates
per year</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff
size=2>Yes simply use TOP 5 clausule in SQL Query , check SQL help in A2k
the command TOP and BOTTOM for the first or last in a
query</FONT></SPAN></DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff
size=2>Juan Menendez</FONT></SPAN></DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff
size=2>Mastercafe SL</FONT></SPAN></DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff size=2><A
href="http://www.mastercafe.com">www.mastercafe.com</A></FONT></SPAN></DIV>
<DIV><SPAN class=080472023-04032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Mensaje original-----<BR><B>De:</B> <A
href="mailto:accessd-admin@databaseadvisors.com">accessd-admin@databaseadvisors.com</A>
[<A
href="mailto:accessd-admin@databaseadvisors.com">mailto:accessd-admin@databaseadvisors.com</A>]<B>En
nombre de </B>Pedro Janssen<BR><B>Enviado el:</B> martes, 04 de marzo de
2003 23:14<BR><B>Para:</B> AccessD@databaseadvisors.com<BR><B>Asunto:</B>
[AccessD] first five dates per year<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Hello Group,</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>i have a table with 10000 records. I would
like to filter out, by query, the first 5 or 10 dates per different
years.</FONT></DIV>
<DIV><FONT face=Arial size=2>Is this possible.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>TIA </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Pedro
Janssen</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>