[AccessD] first five dates per year

Drew Wutka DWUTKA at marlow.com
Sun Mar 9 17:38:00 CST 2003


Completely understand.  No problem.

If Gustav's solution works best for you, that's the way to go.  If you do
want to further the subquery solution, let me know, you could send me a
'blank' database, with no data, I just need the table structure (I can fill
in my own dummy data).

Drew

-----Original Message-----
From: Pedro Janssen
To: accessd at databaseadvisors.com
Sent: 3/9/03 4:54 PM
Subject: Re: [AccessD] first five dates per year

Hello Gustav and Drew,

thanks Gustav for your explanation. The dates where not unique.
I used the Union All sql and now its working fine.

Drew, thanks for your offer, but i can't send You an example because
the data is classified. From the management in our hospital
and my boss i may not present Patientdata to other. Sorry, not even to
a database developer who wants to help me.

Pedro Janssen



----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Pedro Janssen" <accessd at databaseadvisors.com>
Sent: Sunday, March 09, 2003 3:08 PM
Subject: Re: [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.
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list