counting submitted records

[rockhiker]

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>

<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>

[BKBK]

It is unclear what the problem is.

[JR__Bob__Dobbs]

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.

[rockhiker]

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

[Owainnorth]

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.

[JR__Bob__Dobbs]

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.

[Owainnorth]

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.

[rockhiker]

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 )

[BKBK]

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?