EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Record Count assistance

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Record Count assistance

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

simonjaddison
01-07-2008 @ 5:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 12
Joined: Nov 2007

I have been trying to do a record count of the following data:

It is a 9 event running event, with each event having a column in the table.  Event 7 is enter on the day so is not in this query.

Basically I want to easily display the number of people for each day competing, and a total value... without having the need for about 20 queries like I currently use.


I use cf5 & Access.

Get the following error:
ODBC Error Code = 37000 (Syntax error or access violation)<P> [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

-----
QUERY & OUTPUT:
-----


<cfquery name="qSadisticEvent" datasource="#application.ds#">
     select
          blah.count1,blah.count2,blah.count3,blah.count4,blah.count5,blah.count6,blah.count8,blah.count9,
          blah.count1+blah.count2+blah.count3+blah.count4+blah.count5+blah.count6+blah.count8+blah.count9 as totalCount
     from (
          select    
               COUNT(NULLIF(c_e1,0)) as count1,
               COUNT(NULLIF(c_e2,0)) as count2,
               COUNT(NULLIF(c_e3,0)) as count3,
               COUNT(NULLIF(c_e4,0)) as count4,
               COUNT(NULLIF(c_e5,0)) as count5,
               COUNT(NULLIF(c_e6,0)) as count6,
               COUNT(NULLIF(c_e8,0)) as count8,
               COUNT(NULLIF(c_e9,0)) as count9,
          from waientry_competitors) blah</cfquery>


<cfoutput query="qSadisticEvent">
     <table width="200px" border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td width="100px"><strong>Event</strong></td>
              <td width="100px"><strong>Numbers</strong></td></tr>
          <tr><td>1</td><td>#count1#</td></tr>
          <tr><td>2</td><td>#count2#</td></tr>
          <tr><td>3</td><td>#count3#</td></tr>
          <tr><td>4</td><td>#count4#</td></tr>
            <tr><td>5</td><td>#count5#</td></tr>
          <tr><td>6</td><td>#count6#</td></tr>
          <tr><td>8</td><td>#count8#</td></tr>
          <tr><td>9</td><td>#count9#</td></tr>
          <tr><td><strong>Total:</strong></td><td>#totalCount#</td></tr>
     </table></cfoutput>

-----
END QUERY & OUTPUT
-----

Any assistance would be most appreciated.  I take it that basically I cannot do the nullif with Access?


Cheers
Simon

louissto56
01-07-2008 @ 6:22 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

It says you are using a reserved word. Access has A LOT of them. Are you using names that are the same as functions like "count" for example?

____________________________
My Biz List.com.au :: Feb 08

Lossed
01-07-2008 @ 4:57 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Like I may have alluded to in earlier posts, Simon, blardy Aussies couldn't find their way out of a wet paper bag. So please forgive louissto56 for not spotting the trailing comma in your select list that inadvertently tells the dogshite DB we all know and h8 that is M$ Access to go looking for a field named 'from' which, as I'm sure we are all aware, is highly likely to be a reserved word.

Gotta love a bit of Aussie bashing. I note the Indians tried it on 'the monkey' in the recent cricket test and look where that got them. Thank gawd me old cobba louissto56 has a SOH.

Good luck.
BTW, I like the rest of your code. Very intuitive and efficient Wink

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

This message was edited by Lossed on 1-7-08 @ 5:47 PM


Website Designed and Developed by Pablo Varando.