Total Record Count per Individual

[hotflash]

Hi CroCrew et. All,

I am currently working on a quick report that count a total of completed records per Individual. The code that I have will add up all Individual completed records and what I want is to get a total for each of the Individuals per month. For example,

CroCrew
....
....
Total = 2

Jim
....
....
Total = 2

Below is part of the show completed records that I used and wonder if you can help. This is my first time to try to put the code into the format that you and HOPE YOU IT WILL SHOW UP RIGHT. Thanks once again for your help.

 
<TR BGCOLOR="CEEFFF">
    <TD> <DIV ALIGN="CENTER">RECORD</DIV></TD>
    <TD> <DIV ALIGN="CENTER"><STRONG>INDIVIDUAL</STRONG></DIV></TD>
    <TD> <DIV ALIGN="CENTER"><STRONG>COMPLETE DATE</STRONG></DIV></TD>
  </TR>
 
 
<%
 
WHILE NOT RS.EOF 
 
Response.Write("<TR>")
Response.Write("<TD>" & RS("RecordID") & "&nbsp;</TD>")
Response.Write("<TD>" & RS("Individual") & "&nbsp;</TD>")
Response.Write("<TD>" & RS("CompleteDate") & "&nbsp;</TD>")
 
 
Response.Write("</TR>")
 
strCount=strCount + 1
 
RS.MoveNext
Wend
 
RS.Close
Conn.Close
set Conn = nothing
set RS = Nothing
 
Response.Write("<FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">Total Records:   ")
                      Response.Write("</FONT>")
                        Response.Write("<FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#FF0000"" SIZE=""2"">")
                        Response.Write(strCount)
 
%>
 
 

[hotflash]

Thanks for your outstanding support and let me know if you have any luck or not.

Meanwhile, Dr B and CroCrew, any comments? Thanks.

[DrBunchman]

Hi Hotflash,

Sorry for the late reply - I've been on holiday - hopefully you've got it working by now but if not....

Do you mean you want to use a different column in your count depending on which search type the user selects?

This shouldn't cause you any problems once you get started. Just remember that any columns that you Select alongside your Count need to be included in your Group By clause. For example, in the statement below I've selected Col1 and Col2 so I've had to include them in my Group By:

SELECT Col1, Col2, COUNT(Col3) FROM Table1 WHERE Col1 = 'A' GROUP BY Col1, Col2

Keep it simple - build a complete sql statement for each value of sType in your case statement. You can start to make it more efficient once you've got it working.

Looking at your code I can't see you attempting to implement this anywhere - give it a try yourself then come back to us if you have any problems.

Let us know how you get on,

Dr B

[hotflash]

Hi Dr B (The Expert),

Thanks for your replying and hope you had a good holiday. I have been extremely busy on my daily projects as well. This is something very new to me and just want to do it for experience. Hope you can help me know. I know you can because you are the EXPERT on this stuff. Just want to make sure we are on the same sheet of music here.

Let's say there are 3 people in the team: myself (hotflash), dr b and jha. Each of us here completed 2 projects such as "server install" (this is a type of project or Project Type as listed in the case statement). The current working code, if you select By Project Type, type in "Server Install" in the search box and put in the From and To Dates, it will display the following and it is sorted by Individual.

Individual Project Type
Hotflash Server Install
Hotflash Server Install
Dr B Server Install
Dr B Server Install
Jha Server Install
Jha Server Install

Total Records: 6

Again, the code that I provided you WORK as listed above!

What I want but not knowing what to do is:

Individual Project Type
Hotflash Server Install
Hotflash Server Install

Total Records: 2

Dr B Server Install
Dr B Server Install

Total Records: 2

Jha Server Install
Jha Server Install

Total Records: 2

Grand Total: 6

I have sent Jha the codes for him to look over as well. I think it will be easy for me to send you the codes so you can see the output. I can't seem to integrate what you provided that's why I replied to this forum again to make sure I am not confusing you. As always, you are the best of all best and thanks for your outstanding support.

[DrBunchman]

Hi hotflash, have you got this sorted out yet? If not let me know and I'll take a look at it for you.

Dr B

[hotflash]

Hi Dr. B,

Haven't tried again lately. Honestly, I am new to the ASP world and this portion is way over my head and would appreciate if you can take a look for me. Please let me know if you can help. Thanks once again for your outstanding support.

[deric]

<% 
strSearchType = trim(Request("SearchType")) 
strFDates = trim(Request("FDates")) 
strTDates = trim(Request("TDates")) 
If Not IsNumeric(strSearchType) Then sType = 0 Else sType = CDBL(strSearchType) 
 
strSearchWith = trim(Request("SearchWith")) 
sqlSearchWith = "'%" & Replace(strSearchWith,"'","''") & "%'" 
 
FDate = "NO" 
TDate = "NO" 
On Error Resume Next 
    FDate = DateValue( CDate( Request("FDates") ) ) 
    TDate = DateValue( CDate( Request("TDates") ) ) 
On Error GoTo 0 
 
If strFDates = "" OR _
   strTDates = "" Then
Response.Redirect("SearchReports-Test.asp?error=Please+enter+all+the+required+fields+before+searching. Thanks.")
 
Else
Set Conn = Server.CreateObject("ADODB.Connection") 
Set RS = Server.CreateObject("ADODB.RecordSet") 
 
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Database.mdb") _ 
        & "; Jet OLEDB:Database Password=happy" 
 
select case sType 
    case 0 : where = " WHERE 1=1 " 
    case 1 : where = " WHERE Individual LIKE " & sqlSearchWith 
    case 2 : where = " WHERE ProjectType LIKE " & sqlSearchWith 
    case 3 : where = " WHERE Network LIKE " & sqlSearchWith 
    case 4 : where = " WHERE Region LIKE " & sqlSearchWith
    case else 
        Response.Redirect ("SearchReports-Test.asp?error=Sorry+...+Please+enter+a+valid+search+type.")   
end select 
 
strSQL = "SELECT * FROM TableProjects " & where 
 
If FDate <> "NO" AND TDate <> "NO" Then 
    strSQL = strSQL & " AND CompleteDate BETWEEN #" & FDate & "# AND #" & TDate & "#" 
End If 
 
strSQL = strSQL & " ORDER BY Individual, RecordID" 
 
RS.Open strSQL, Conn 
 
If (RS.EOF) Then 
    Response.Redirect ("SearchReports-Test.asp?error=Sorry+...+Report+does+not+exist.  +Please+enter+a+valid+search+type.+  Thanks.") 
End If 
End If
%> 
 
<HTML>
<HEAD>
<TITLE>Homepage</TITLE>
 
 
</HEAD>
 
<BODY ONLOAD="jsClock()" BGCOLOR="#FFFFFF">
 
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
 
<DIV ALIGN="LEFT"> 
 
<TABLE WIDTH="100%"BORDER="1" CELLSPACING="0" CELLPADDING="1">
  <TR BGCOLOR="CEEFFF">
    <TD WIDTH="5%"  CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>RECORD ID</STRONG></DIV></FONT></TD>
    <TD WIDTH="15%" CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>INDIVIDUAL</STRONG></DIV></FONT></TD>
    <TD WIDTH="8%" CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>COMPLETE DATE</STRONG></DIV></FONT></TD>
    <TD WIDTH="15%"  CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>NETWORK</STRONG></DIV></FONT></TD>
    <TD WIDTH="25%" CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>PROJECT TYPE</STRONG></DIV></FONT></TD>
    <TD WIDTH="25%" CLASS="Table_Black"> <DIV ALIGN="CENTER"><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""1""><STRONG>REGION</STRONG></DIV></FONT></TD>
  </TR>
 
<CENTER>
<%
 Dim count As Int
Dim curr, prev As String
 
strCount = 0
prev = ""
 
WHILE NOT RS.EOF 
 
 curr = RS("Individual")
If curr = prev Then
  count = count + 1
Else
  count = 1
End If
 
Response.Write("<TR>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("RecordID") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Individual") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("CompleteDate") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Network") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("ProjectType") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Region") & "&nbsp;</FONT></TD>")
 
Response.Write("</TR>")
 
strCount=strCount + 1
 
 If prev <> "" And curr <> prev Then
Response.Write("<TR>")
Response.Write("<TD COLSPAN=6><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & count & "&nbsp;</FONT></TD>")
Response.Write("</TR>")
End If
prev = curr
 
RS.MoveNext
Wend
 
RS.Close
Conn.Close
set Conn = nothing
set RS = Nothing
%>
</CENTER>
</TABLE>
<BR>
<%        
                      Response.Write("<FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">Total Records:   ")
                      Response.Write("</FONT>")
                        Response.Write("<FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#FF0000"" SIZE=""2"">")
                        Response.Write(strCount)
                        Response.Write("</FONT>")
%>
</DIV>
 
</BODY>
</HTML>
 

I am not sure if it will work cause I've not tested it, but you may try the code (in bold text) I inserted in your quoted code above. Let me know what would happen. Good luck..

[hotflash]

Hi deric,

Thanks for your help. It looks like we are getting close now. This is the output that I got when I ran it. 3 issues. Thanks once again for your outstanding support.

1. The total is ALWAYS 1 instead of 2 because let's say Hotflash completes 2
records, etc.

2. For some reason the user keeps jumping around like Dr B jumps up to
Hotflash, Jha jumps up to Dr B.

3. I redefined Dim Int count
Dim String curr, prev

Got "Expected end of statement" errors by using Dim count As Int
Dim curr, prev As String

Individual Project Type

Hotflash Server Install
Dr B Server Install

1

Dr B Server Install
Jha Server Install

1
Jha Server Install
Jha Server Install

1

Grand Total: 6

[deric]

 prev = ""
count = 0
 
WHILE NOT RS.EOF
 
 curr = RS("Individual")
If prev <> "" And curr <> prev Then
  Response.Write("<TR><TD COLSPAN=6><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & count & "&nbsp;</FONT></TD></TR>")
End If
 
If curr = prev Then
  count = count + 1
Else
  count = 1
End If
 
Response.Write("<TR>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("RecordID") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Individual") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("CompleteDate") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Network") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("ProjectType") & "&nbsp;</FONT></TD>")
Response.Write("<TD><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & RS("Region") & "&nbsp;</FONT></TD>")
Response.Write("</TR>")
 
strCount=strCount + 1
 
RS.MoveNext
 
 prev = curr
 
If RS.EOF Then
  Response.Write("<TR><TD COLSPAN=6><FONT FACE=""HELVETICA,HELV,ARIAL"" COLOR=""#000000"" SIZE=""2"">" & count & "&nbsp;</FONT></TD></TR>")
End If
 
Wend
 

Hi hotflash

Did you try to debug it?
Yeah, I did not guarantee that it will work as expected but the idea was already there, we just have to work around it. :-)

I made a modification on the code (in bold text above), I hope it will help you this time around. ;-) Again, I have not tested it but I have simulated it on a paper just once cause I didn't have enough time.

Regarding the issue (3).. What are you using to execute the program? Try not declaring the variables instead, as you have done with your strCount.

[hotflash]

Hi deric,

You are truly another HERO of this ASP forum. Everything works perfectly.
Thanks once again for your OUTSTANDING SUPPORT.

[deric]

Hi deric,

You are truly another HERO of this ASP forum. Everything works perfectly.
Thanks once again for your OUTSTANDING SUPPORT.

Glad it helped you out....