poll & count syntax

My poll's database has a table called "votos". This table as 3 fields: "date", "ip" and "voto". In the field "voto" I will record the option chosen in the previous form (this input may be "01", "02", "03",..., "19" or "20").
Now I want to show the results and for that, I must count the number of time that each option is displayed. Can anyone tell me the count syntax for that? I've tried to change an example I've found in internet, but didn,t achieve it... The code is:

<%
accessdb="/panteismo/db/poll.mdb"
cn="driver={Microsoft Access Driver (*.mdb)};"
cn=cn & "dbq=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")

sql=sql & "select count(01) as 01,"
sql=sql & "select count(02) as 02,"
sql=sql & "select count(03) as 03,"
(...)
sql=sql & "select count(19) as 19,"
sql=sql & "select count(20) as 20,"
sql= sql & "count(*) AS total_votes "
sql= sql & "FROM votos"
rs.Open sql, cn
total1=rs ("01")
total2=rs ("02")
total3=rs ("03")
(...)
total19=rs ("19")
total20=rs ("20")
count=rs ("total_votes")
%>

Then I have:

<tr>
<td width="195"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><span style="font-size:10.0pt;font-family:Verdana">Agnosticismo</span></font></td>
<td width="295"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><span style="font-size:10.0pt;font-family:Verdana"><img src="cores/red.jpg" height="10" width="<%= formatnumber((total1/count)*200,0) %>"> <%= total1 %> (<%= formatnumber((total1/count)*100) %>%)</span></font></td>
</tr>

I guess the problem is in count syntax, but don´t know how to fix it...

Luís

Hi,

Are you getting any error messages when running your code?

Looking at your sql string I don't know if that is allowed but I don't use MS Access so can't be sure on that.

You can tidy your sql up by doing something like this:



Again not sure if the above will work in access.. works fine in oracle. Two problems with using the above though, the first one is if any row has no numbers then a row will not be returned the second is there is no total figure.

Total figure is easy to get by going through the recordset and adding up the column.



Then to output the data you just need to go through your rs similar to the above just displaying the VotoNum field where you want it.

You could code for the other problem in the loop when your displaying the data but you would probably have to hard code it to work...

hope this helps.

Kong.

I've used:

<%
accessdb="/panteismo/db/poll.mdb"
cn="driver={Microsoft Access Driver (*.mdb)};"
cn=cn & "dbq=" & server.mappath(accessdb)
set rs = server.createobject("ADODB.Recordset")

select Distinct Count(voto) as VotoNum, voto
from votos
group by voto

and receive the following message error:

Microsoft VBScript compilation error '800a03fd'

Expected 'Case'

/panteismo/pollresults.asp, line 130

select Distinct Count(voto) as VotoNum, voto
-------^

Any sugestion?

Luis