8 Replies Latest reply: Mar 9, 2012 11:06 AM by BKBK RSS

    counting submitted records

    rockhiker Community Member

      Hello

       

      I am trying to count users who have registered for something and display the count in a table so people will know how many people have signed up. I am a beginner but I think I am close. Please help. Thank you

       

      See code

       

       

      <cfquery name="techday" datasource="webAdmin">

       

       

      Select 8-count(class) as signedup, date1, date2, location, class

      From DSclasses

      Where date1 >= Date()

      and ClassID not in ('20120425', '20120426')

      group by class, date1, date2, location, classid

      order by classID

      </cfquery>

       

       

      <table width="536" style="border:1px solid #333; font-size:0.90em;">
      <tr bgcolor="#ffd940">
      <th width="262">Class</th>

       

      <th width="124" align="left">Date</th>
      <th width="63">Location</th>
      <th width="57">Signed Up</th>

       

      </tr>

       


      <cfoutput query="techday"> <tr>
      <td>#class#</td>
      <td>#DateFormat(date1, "mm/dd/yyyy")# - #DateFormat(date2, "mm/dd/yyyy")#</td>
      <td style="text-align:center;">#location#</td>

       

      <td style="text-align:right;">

      <cfif #signedup# eq ''> 8</cfif>
      <cfif #signedup# gte 0> #signedup#</cfif>
      <cfif #signedup# eq 0>
      FULL

       

      </cfif></td>

       


      </tr></cfoutput>

      <table>

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • 1. Re: counting submitted records
          BKBK MVP

          It is unclear what the problem is.

          • 2. Re: counting submitted records
            JR "Bob" Dobbs Community Member

            I sounds like you will need to use the COUNT() function provided by whatever database product you are using to count the number of student records associated with each class.  Your posted cfquery is close, but I'm not sure what the "8-" is supposed to do.

            • 3. Re: counting submitted records
              rockhiker Community Member

              I only have room for 8 people to attend each session. The value returned does not relect the number of students registered. The code i posted yesterday returns a a value of 7 but I have 4 people registered.

               

              I did some testing this AM and reomved the 8- and a value of 1 is returned even though four are registered.

               

              Using MS Access DB

              • 4. Re: counting submitted records
                Owain North Community Member

                Using MS Access DB

                Well there's your first mistake.

                 

                Break it down slowly, don't try and do maths on numbers when you don't even know what the numbers are. Start by removing the count() functions and grouping, just get the results. Are they the rows you expect? If so, the grouping's wrong. If they're not, there's no point going any further until you've fixed that.

                • 5. Re: counting submitted records
                  JR "Bob" Dobbs Community Member

                  rockhiker,

                   

                  I suggest that you get the query to work in Access, then work on the ColdFusion code.  In order to assist you it would be useful to have.

                  1. The table structure.

                  2. The query, if the one in your sample code has changed.

                  3. A sample of the data.

                  4. The results desired from executing the query against the data sample.

                  5. Error messages, if any you encounter.

                   

                  I will also agree with Owain North that using MS Access in a web application is not a good idea.  Access was designed for a single user or small workgroup, not as a backend for multi-user applications such as web sites.  I suggest that you look into alternatives such as:

                  1. MS SQL Server.  Pros: Should be relatively easy to move from Access to SQL Server.  Cons: Licensing costs.

                  2. If cost is an issue you might use an open-source product such as PostgreSQL or MySQL. 

                  3. ColdFusion includes the Java based Derby DB. See Charlie Arehart's site for info on Derby.  http://carehart.org/resourcelists/derby_for_CFers/

                  3. Other.  There are many good database products the one you pick should fit the needs of your organization and your IT infrastructure.

                  • 6. Re: counting submitted records
                    Owain North Community Member

                    1. MS SQL Server.  Pros: Should be relatively easy to move from Access to SQL Server.  Cons: Licensing costs.

                     

                    Not a problem.There is a very good free version of SQL Server - SQL Server Express - which I would recommend to anyone as a better alternative to any of the open-source databases, with simple and multiple upgrade paths to a fully-scalable enterprise-level platform without having to re-write a single query. There really is no excuse for people on a Windows platform not to use it.

                    • 7. Re: counting submitted records
                      rockhiker Community Member

                      Ok I got work to do. I think converting to SQL Database will not be a problem.  I am currently working on querying the individual classes and trying to get a count of the students registered. I'll be back!(meeting )

                      • 8. Re: counting submitted records
                        BKBK MVP

                        What the query misses also happens to be the most significant column in most databases: the userID. Aren't those the entities you wish to count?