Display Records from Access tables as a total in an ASP Page??

[Richo1980]

Hi All,

I've been searching through this forum (very informative btw) and am hoping that someone can shed some light on a question I've been trying to answer for days...

The problem:

I'm trying to display records from an access database on an ASP page! However, I want the records to show as a total. eg is it possible to show how many closed calls someone has as a total? I'm thinking that I need to use a COUNT variable but I'm not too sure how to write the ADO to do it.. Here's what I have so far

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\server\gms_stats.mdb"
DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM Closed_Remedy_2006 ORDER BY TechName"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn

' Display result
Response.Write objRS("intTotal")


objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Please let me know if this is possible<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\nsyd9004pap\c$\Inetpub\

http://wwwroot\GMS_Stats\UATDev\skidoo_too\gms_stats.mdb"

DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM Closed_Remedy_2006 ORDER BY TechName"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn

' Display result
Response.Write objRS("intTotal")


objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

If someone could point me in the right direction or advise if this is possible I would be most appreciative

Thanks
-Dave

 

[JoeAtWork]

I think you want something like the following for your SQL statement:

SELECT TechName, Count(AnyFieldName) AS intTotal FROM Closed_Remedy_2006 GROUP BY TechName

 

[Remou]

There seems to be a typo in your statement. Your SQL string is definded as mySQL, but when you are opening the recordset, you have recSQL. I suppose you are aware that Group By can return several rows? If you only want a count of rows from an existing recordset you can use objRS.RecordCount.

There is an ASP forum:
Microsoft: Active Server Pages (ASP) Forum
forum333

 

[JoeAtWork]

how many closed calls someone has as a total"

based on that and the OP's existing code, I came to the conclusion he wanted the count for each TechName, hence the group by.

Personally I always like to do counting, summing, etc. in the SQL statement rather than checking the properties of a recordset. I'm more confident of the Count returned in an SQL statement than the RecordCount property (because if you forget to do a MoveLast, etc., it may not be correct).

 

[Remou]

JoeAtWork

I was not paying proper attention, I thought the Group By was part of the OP.