Access, hmmm I'm not going to try this with SQL.
Try the following in your loop, thats if I understand corrrectly what you
are trying to achieve.!
<cfparam name="previousYear" default="">
<cfparam name="i" default="1">
<cfloop query="qMagazine">
<cfif previousYear EQ qMagazine.year>
<cfset i = i + 1>
<cfelse>
<cfset i = 1>
</cfif>
#i# #qMagazine.YEAR# #qMagazine.Name#<br />
<cfset previousYear = qMagazine.year>
</cfloop>
If your year was a date in your db you would use #year(date)#
Hope its what your after.
Taco Fleur
----- Original Message -----
From: "Ryan Sabir" <[EMAIL PROTECTED]>
To: "CFAussie Mailing List" <[EMAIL PROTECTED]>
Sent: Wednesday, February 05, 2003 8:06 AM
Subject: [cfaussie] Re: SQL grouping
> Hiya,
>
> Can't really send the actual data, but its not too different from what I
> wrote in the email.
>
> Also, I should have mentioned, this is in an Access database, not SQL
> Server.
>
> And just to make things complicated.. there is not always the same number
of
> issues in a particular year...
>
> bye!
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Taco Fleur
> Sent: Wednesday, 5 February 2003 8:59 AM
> To: CFAussie Mailing List
> Subject: [cfaussie] Re: SQL grouping
>
>
> Ryan,
>
> Do you work with stored procedures?
> If so, I might be able to work something out for you.
>
> - Can you send the actual db lay-out and data? If not,
> - Are there always the same number of records in one year?
> ----- Original Message -----
> From: "Ryan Sabir" <[EMAIL PROTECTED]>
> To: "CFAussie Mailing List" <[EMAIL PROTECTED]>
> Sent: Tuesday, February 04, 2003 1:58 PM
> Subject: [cfaussie] SQL grouping
>
>
> >
> > Hi all, I have a database that looks something like this:
> >
> > Record ID YEAR Name
> > 1 2001 Some name1
> > 2 2001 Some name2
> > 3 2002 Some name3
> > 4 2002 Some name4
> > 5 2002 Some name5
> > 6 2002 Some name6
> > 7 2002 Some name7
> > 8 2003 Some name8
> > 9 2003 Some new name
> > 10 2003 Some other name
> >
> > I want to generate a number for each of these records the shows what
> record
> > of that particular year it was... imagine these are magazines and I want
> to
> > generate an issue number that resets to 1 at the start of each year
> >
> > Record 1 would be issue number 1 of 2001
> > Record 4 would be issue number 2 of 2002
> > Record 8 would be issue number 1 of 2003
> > Record 10 would be issue number 3 of 2003
> >
> > Now I can think of some simple CF ways to find this out, but what would
be
> > really elegant is to get SQL to work it out in the query, something
like:
> >
> > <CFQUERY blah>
> > SELECT recordID, year, name, funkyfunction(boink) AS issueNumber
> > FROM myTable
> > </CFQUERY>
> >
> > Anyone willing to have a try at this? There's a free cookie in it for
you!
> > (must be redeemed by visiting my web site).
> >
> > bye!
> >
> >
> > -----------------------
> > Ryan Sabir
> > Newgency Pty Ltd
> > 2a Broughton St
> > Paddington 2021
> > Sydney, Australia
> > Ph (02) 9331 2133
> > Fax (02) 9331 5199
> > Mobile: 0411 512 454
> > http://www.newgency.com/index.cfm?referer=rysig
> >
> >
> > ---
> > You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> > To unsubscribe send a blank email to
> [EMAIL PROTECTED]
> >
> > MX Downunder AsiaPac DevCon - http://mxdu.com/
> >
>
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
[EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
[EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
>
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
