ok, If you're using Access 2000 you should be able to do something like this:
SELECT GAMES.name,count(L) AS lCount FROM GAMES INNER JOIN (SELECT LEFT(name,1) AS L FROM GAMES) a ON LEFT(GAMES.name,1) = a.L GROUP by GAMES.name Which will give you a column called lcount telling you how many records have the same first letter as the value for name in the current row. That's assuming your table is called games, and the column you want to sort on is called name. Spike Stephen Milligan Team Macromedia - ColdFusion Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content Management' http://spikefu.blogspot.com > -----Original Message----- > From: Giles Roadnight > [mailto:[EMAIL PROTECTED]] > Sent: 21 November 2002 19:21 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Counting titles that do not start > with letter using SQL > > > Access 2000 > > Have got it working now but not very neatly. > > Thanks for the help everyone. > > > Giles Roadnight > http://giles.roadnight.name > > > -----Original Message----- > From: Spike [mailto:[EMAIL PROTECTED]] > Sent: 21 November 2002 18:09 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Counting titles that do not start > with letter using SQL > > Ah yes, > > Sorry mis-read the question... > > Did you mean to say you aren't on MSSQL? > > If not, which database are you using? > > Depending on the DB type, there may be a function that allows > you to do a count based on the first letter. Also, if the DB > supports sub-select statements, you should be able to do it. > > Spike > > Stephen Milligan > Team Macromedia - ColdFusion > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > Content Management' http://spikefu.blogspot.com > > > -----Original Message----- > > From: Giles Roadnight > > [mailto:[EMAIL PROTECTED]] > > Sent: 21 November 2002 17:40 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] Counting titles that do not start > > with letter using SQL > > > > > > Not quite what I want, don't have mysql either. > > > > Thanks anyway > > > > > > Giles Roadnight > > http://giles.roadnight.name > > > > > > -----Original Message----- > > From: Spike [mailto:[EMAIL PROTECTED]] > > Sent: 21 November 2002 16:30 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] Counting titles that do not start > > with letter using SQL > > > > SELECT colname > > FROM tablename > > WHERE colname LIKE ('[^A]%') > > > > That will match anything except an 'A'. > > > > Spike > > > > Stephen Milligan > > Team Macromedia - ColdFusion > > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > > Content Management' http://spikefu.blogspot.com > > > > > -----Original Message----- > > > From: Giles Roadnight > > > [mailto:[EMAIL PROTECTED]] > > > Sent: 21 November 2002 17:17 > > > To: [EMAIL PROTECTED] > > > Subject: [ cf-dev ] Counting titles that do not start with > > > letter using SQL > > > > > > > > > I am looping through a query of titles and I want to output the > > > results per letter in two columns so under A there would be two > > > columns of titles starting with A. To do this I need the count of > > > records beginning with A which I have done like this: > > > > > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > > select count(gameid) as lettercount > > > from qry_getgames > > > where name like '#request.currentLetter#%' > > > </cfquery> > > > > > > How though do I get the count of titles that do not start with a > > > letter? > > > > > > I could do it like this: > > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > > select count(gameid) as lettercount > > > from qry_getgames > > > where name not like 'A%' > > > and name not like 'B%' > > > ect... > > > </cfquery> > > > But I don't want to have to write that out 52 times (upper and > > > lowercase) > > > > > > Also is there a way to make SQL non case sensitive so the first > > > query will count upper and lower case? > > > > > > Thanks > > > > > > Giles Roadnight > > > http://giles.roadnight.name > > > > > > > > > > > > -- > > > ** Archive: > > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > > > > To unsubscribe, e-mail: > > [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
