Skip navigation
rockhiker
Currently Being Moderated

counting submitted records

Mar 8, 2012 5:05 PM

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>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
Replies
  • Currently Being Moderated
    Mar 8, 2012 11:55 PM   in reply to rockhiker

    It is unclear what the problem is.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 5:58 AM   in reply to rockhiker

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 8:07 AM   in reply to rockhiker

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 8:35 AM   in reply to Owain North

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 8:38 AM   in reply to JR \"Bob\" Dobbs

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 11:06 AM   in reply to rockhiker

    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?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points