Count Property-Multiple Values in Same Field Not Working 1

alljax · Nov 24, 2000

I need to count different values from the same field and have them print out four separate counts. I also need to be able to sort these by Site ID. Here is how my data looks:

X SiteID
---------------- ---------------------
3 35012
3 12345
3 35012
1 35012
1 35012
3 35012
2 35012
2 12345

So for example, I need to be able to count all the three’s, two’s, and one’s WHERE ((Tbl.SiteID)= '35012') and display the count results individually

Example
Report for Site ID “35012”

Value 3 = 3 Records
Value 2 = 1 Record
Value 1 = 2 Records

This is the code we are using that isn’t working :

sql = "SELECT COUNT(DECODE(x,1,1,NULL)) count_of_1, COUNT(DECODE(x,2,1,NULL)) count_of_2, COUNT(DECODE(x,3,1,NULL)) count_of_3 FROM FinLog.AppSta WHERE ((Tbl.SiteID)= '35012')"

PLEASE HELP!!!!

swilliams · Nov 25, 2000

Try this:

SELECT Table.x, Count(Table.SiteID) AS Records
FROM Table
GROUP BY Table.x, Table.SiteID
HAVING (((Table.SiteID)=35012))
ORDER BY Table.x DESC

Simon

alljax · Nov 25, 2000

Simon,
Thanks for the help, unfortunately its not working for me. I think that maybe there is something wrong with my script. Here is my entire script. I am pulling from a database named “ONLAPPCYC2K” and the table is “FinLog”.

~ Chris

<html>
<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>test</title>
</head>
<body>
<body BGCOLOR="FFFFFF">

<%
Param = Request.QueryString("Param&quot

Data = Request.QueryString("Data&quot

%>
<%

Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "ONLAPPCYC2K","",""
Set Session("ONLAPPCYC2K_conn&quot

= conn

%>
<%
sql = Request ("sql&quot

if sql = "" then
sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SiteID)=CY2KFC)) ORDER BY FinLog.AppSta DESC"

If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
End If
End If
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open sql, conn, 3, 3
%>

<CENTER>
<table BORDER="1"CELLSPACING="0">

<tr>
<th BGCOLOR="Beige" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="Black">Please Work</font></th>

</tr>
</thead>
<tbody>

<%Do while not RS.EOF%>

<%
On Error Resume Next%>

<tr VALIGN="TOP">
<td BORDERCOLOR="#c0c0c0"><font SIZE="-1" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("AppSta&quot

.Value)%><br></font></td>

</tr>
<%
On Error Resume Next
rs.MoveNext
loop
%>

<%set Conn = nothing%>

</tbody>
<tfoot></tfoot>
</table>
</CENTER>

</body>
</html>

swilliams · Nov 25, 2000

I can see two things wrong without running the code:

1. in the sql statement you have "... GROUP FinLog.AppSta,..." but that should be "... GROUP BY FinLog.AppSta,..."

2. The field SiteID looks like it is a text field, by the WHERE clause you are using, so you should have quotes around CY2KFC in the WHERE clause.

Simon

alljax · Nov 25, 2000

I still get this error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/please.asp, line 23, column 135
sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SiteID)="CY2KFC&quot

) ORDER BY FinLog.AppSta DESC"
--------------------------------------------------------------------------------------------------------------------------------------^

~ Chris

swilliams · Nov 25, 2000

Try this:

sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SiteID)='CY2KFC')) ORDER BY FinLog.AppSta DESC"

OR try this:

sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SiteID)=" & chr(34) & "CY2KFC" & chr(34) & &quot

) ORDER BY FinLog.AppSta DESC"

Simon

alljax · Nov 25, 2000

I think we are on the right track. Now I come up with this error, but it has nothing to do with your SQL statement, I think.

Here is the error:Error

Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
/please.asp, line 33

Here is that script:

<%
Param = Request.QueryString("Param&quot

Data = Request.QueryString("Data&quot

%>
<%

Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "ONLAPPCYC2K","",""
Set Session("ONLAPPCYC2K_conn&quot

= conn

%>
<%
sql = Request ("sql&quot

if sql = "" then
sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SiteID)=" & chr(34) & "CY2KFC" & chr(34) & &quot

) ORDER BY FinLog.AppSta DESC"

If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
End If
End If
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open sql, conn, 3, 3
%>

I appreciate all this help..

~Chris

swilliams · Nov 25, 2000

This error means that you are truing to reference a field in the SQl statement that does not exist in the table.

I notice that you have Count(FinLog.SitID) instead of Count(FinLog.SiteID).

And the same error in the GROUP BY clause.

Change this and see what happens.

Simon

alljax · Nov 25, 2000

This seems to work:

sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SitID)='CY2KFC')) ORDER BY FinLog.AppSta DESC"

The good thing is that I no longer have errors, the bad part is that I must not be requesting the data right because it's just blank.

Here is what I am using:

<%=Server.HTMLEncode(rs.Fields("AppSta&quot

.Value)%>

is this right?

~Chris

swilliams · Nov 25, 2000

What do you want to do with the returned records??

Server.HTMLEncode(...) is used for encoding strings that you are going to use in URL's, so that spaces get converted to %20, ....

If you want to output the results in a table, put the following code immediately after the line rs.Open ... and immediately before the last %> in the code posted in your message beginning "I think we are on the right track...":

%>
<table>
<tr>
<td colspan="2">Report for site ID "CY2KFC"</td>
</tr>
<%
Do Until rs.EOF
Response.Write "<tr><td>Value " & rs("AppSta&quot

& "</td><td>" & rs("Records&quot

& " Records</td></tr>" & chr(13)
rs.MoveNext
Loop
%>
</table>
<%
rs.Close
conn.Close
Set conn = Nothing

I also do not know what the following code is meant to do:

If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
End If

Simon

alljax · Nov 25, 2000

Yes, I want to output the results in a table. However I am still geting nothing. It just reads (Report for site ID "CY2KFC"), but nothing else.

Here is the full code:
<html>
<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>test</title>
</head>
<body>
<body BGCOLOR="FFFFFF">

<%
Param = Request.QueryString("Param&quot

Data = Request.QueryString("Data&quot

%>
<%

Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "ONLAPPCYC2K","",""
Set Session("ONLAPPCYC2K_conn&quot

= conn

%>
<%
sql = Request ("sql&quot

if sql = "" then
sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SitID)='CY2KFC')) ORDER BY FinLog.AppSta DESC"

End If
Set rs = Server.CreateObject("ADODB.Recordset&quot

rs.Open sql, conn, 3, 3
%>
<table>
<tr>
<td colspan="2">Report for site ID "CY2KFC"</td>
</tr>
<%
Do Until rs.EOF
Response.Write "<tr><td>Value " & rs("AppSta&quot

& "</td><td>" & rs("Records&quot

& " Records</td></tr>" & chr(13)
rs.MoveNext
Loop
%>
</table>
<%
rs.Close
conn.Close
Set conn = Nothing

%>

Thanks again for all of this help,
~ Chris

swilliams · Nov 26, 2000

Send me the database - e mail address swilliams@paragon.bm - so that I can check the SQL statement (remember to compact the database, and if it is still big, zip it as well)

Simon

swilliams · Nov 26, 2000

The problem now is the way that you have the tables linked behind the scene. FinLog.SitID does NOT hold the site reference CY2KFC. This references the table "SiteID". If you look in there, the ID for CY2KFC is 1, and so the sql statement:

sql = "SELECT FinLog.AppSta, Count(FinLog.SitID) AS Records FROM FinLog GROUP BY FinLog.AppSta, FinLog.SitID HAVING (((FinLog.SitID)='1')) ORDER BY FinLog.AppSta DESC"

works as we were expecting before.

Simon

alljax · Nov 26, 2000

Simon,
Thank you so much you really saved me. Please let me know where I can send a check because you deserve to have some compensation. The last question I have is how to make it so that a dealer can enter his site ID into a form and have that submit to the asp page and bring up the appropriate values?

Thanks,
Chris

alljax · Nov 26, 2000

Never mind, I figured it out.

~ Chris

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Count Property-Multiple Values in Same Field Not Working 1

alljax

ISP

swilliams

Programmer

alljax

ISP

swilliams

Programmer

alljax

ISP

swilliams

Programmer

alljax

ISP

swilliams

Programmer

alljax

ISP

swilliams

Programmer

alljax

ISP

swilliams

Programmer

swilliams

Programmer

alljax

ISP

alljax

ISP

Similar threads

Part and Inventory Search

Sponsor