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

[jeffstl]

So did it work for you?

I did not see you post an actual question or problem you had.

I will point out that rather then loop through all your records you can get a total immediately from just using an SQL query.

http://www.w3schools.com/sql/func_count_ast.asp

[hotflash]

Hi Jeff,

I got the total records count for ALL individual show up correctly. What I want to do is to get a total count for EACH individual. Please see below and thanks for your help.

Current:
CroCrew
CroCrew
Jim
Jim

Total Records: 4

Want it to be:

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

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

[DrBunchman]

Hi Hotflash,

Jeff is right about getting the sql query to do the work for you. In order to get a count of each individual you can use the following simple query:

SELECT Individual, COUNT(CompleteD ate) AS RecordCount FROM MyTable Group By Individual

If you want to put a condition in here then you can do so like this:

SELECT Individual, COUNT(CompleteD ate) AS RecordCount FROM MyTable WHERE CompleteDate IS NOT NULL Group By Individual

Use this in your sql string then cycle through each of the records as you would before:

 While Not RS.eof 
Response.Write("<tr>")
Response.Write("<td>" & RS("Individual") & "&nbsp;</td>")
Response.Write("<td>" & RS("RecordCount ") & "&nbsp;</td>")
Response.Write("</tr>")
RS.MoveNext
WEnd
 

Let us know how you get on,

Dr B

PS This sounds silly I know but you should keep all your html as lower case - although it won't affect you at the moment if you ever start using .NET you'll find your html won't validate because it's in uppercase. Just thought I'd let you know ;-)

[idsanjeev]

Hi Jeff,

I got the total records count for ALL individual show up correctly. What I want to do is to get a total count for EACH individual. Please see below and thanks for your help.

Current:
CroCrew
CroCrew
Jim
Jim

Total Records: 4

Want it to be:

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

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

Hi hotfalsh
Try this sql query in select statement

SELECT YourColumn,yourcolumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
ORDER BY COUNT(*) ASC

thanks

[hotflash]

Hi idsanjeev and DrBunchman,

Your recommendations sound EXTREMELY GOOD however, I am having problem to try to integrate them into the actual code that I used because I have case statements, etc for different type of search as well.

Is there a way, I can send you the 2 files that I used (1 search option, 1 display search result) for your recommendations ? Please advise or send me a PM message. Thanks once again for your outstanding support.

[DrBunchman]

Hi idsanjeev and DrBunchman,

Your recommendations sound EXTREMELY GOOD however, I am having problem to try to integrate them into the actual code that I used because I have case statements, etc for different type of search as well.

Is there a way, I can send you the 2 files that I used (1 search option, 1 display search result) for your recommendations ? Please advise or send me a PM message. Thanks once again for your outstanding support.

Hi Hotflash, have you managed to fix this yet or has idsanjeev helped you out?

[idsanjeev]

Hi Hotflash
you can simply attached your file in post or pm

[hotflash]

Hi Sanjeev,

Below are the files that I used for this search and display reports accordingly.
The MS Access fields are defined as follows:

RecordID: AutoNumber and Primary Key
CompleteDate: Date/Time
ProjectType: Text
Network: Text
Individual: Text (First and Last Name)

Thanks once again for your outstanding support.

 
<HTML>
<HEAD>
<TITLE>Homepage</TITLE>
 
<STYLE> 
table.MAIN { border: none; } 
table.MAIN TR TD { font-size: 14px; font-family: helvetica, helv, arial; } 
table.MAIN TR TH { font-size: 14px; font-family: helvetica, helv, arial; font-weight: bold; 
                   width: 350px; text-align: right; } 
</STYLE> 
 
<SCRIPT> 
var msgs = new Array("",
                                         "Enter Individual Name (Full or Partial Name):",
                                         "Enter Project Type (Full or Partial):",
                     "Enter Network (Full or Partial):", 
                     "Enter Region (Full or Partial):");
 
function setInfo( ) 
{ 
    var sts = document.xForm.SearchType; 
 
    // set message according to searchtype: 
    for ( var n = 0; n < sts.length; ++n ) 
    { 
        if ( sts[n].checked ) document.getElementById("SLABEL").innerHTML = msgs[n]; 
    } 
    document.getElementById("SBOX").style.visibility = sts[0].checked ? "hidden" : "visible"; 
} 
</SCRIPT> 
 
</HEAD>
 
<BODY ONLOAD="jsClock()" BGCOLOR="#FFFFFF">
<BR>
 
<TABLE CLASS="MAIN" CELLSPACING="0" CELLPADDING="4" C> 
 
<DIV ALIGN ="LEFT"> 
 
<FORM ID="xForm" NAME="xForm" METHOD="post" ACTION="DisplayReports-Test.asp"> 
 
  <SCRIPT LANGUAGE="JavaScript" ID="jscal1xx">
            var cal1xx = new CalendarPopup("testdiv1");
            cal1xx.showNavigationDropdowns();
    </SCRIPT>
    <SCRIPT LANGUAGE="JavaScript">writeSource("jscal1xx");</SCRIPT>
 
    <SCRIPT LANGUAGE="JavaScript" ID="jscal2xx">
            var cal2xx = new CalendarPopup("testdiv2");
            cal2xx.showNavigationDropdowns();
    </SCRIPT>
    <SCRIPT LANGUAGE="JavaScript">writeSource("jscal2xx");</SCRIPT>
<P></P>
 
<TR> 
    <TH ALIGN="right" VALIGN="top"><FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2">Type of Search: </FONT></TH> 
    <TD> 
            <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"><INPUT TYPE="radio" NAME="SearchType" VALUE="0" onClick="setInfo()"> All Completed Records <BR/> </FONT>
        <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"><INPUT TYPE="radio" NAME="SearchType" VALUE="1" onClick="setInfo()"> By Individual <BR/> </FONT>
        <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"><INPUT TYPE="radio" NAME="SearchType" VALUE="2" onClick="setInfo()"> By Project Type <BR/> </FONT>
        <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"><INPUT TYPE="radio" NAME="SearchType" VALUE="3" onClick="setInfo()"> By Network <BR/> </FONT>
        <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"><INPUT TYPE="radio" NAME="SearchType" VALUE="4" onClick="setInfo()"> By Region <BR/> </FONT>
    </TD> 
</TR> 
 
<TR>  
    <TH VALIGN="top" ID="SLABEL"></TH>
    <TD ID="SBOX" STYLE="visibility: hidden;"><FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2"> 
        <INPUT NAME="SearchWith" Size="40"></FONT>
    </TD> 
</TR>
 
<TR> 
    <TH ALIGN="right" VALIGN="top"><FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2">Enter Dates </FONT></TH> 
    <TD> 
         <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2">&nbsp;From </FONT>
         <INPUT ID="FDates" NAME="FDates" Size="9">
 
         <FONT FACE="HELVETICA,HELV,ARIAL" SIZE="2">&nbsp;To </FONT>
         <INPUT ID="TDates" NAME="TDates" Size="9">
    </TD> 
</TR> 
 
<TR> 
    <TD></TD> 
    <TD><INPUT CLASS="Table_Blue" TYPE="submit" NAME="Submit" VALUE="SEARCH YOUR REPORT"></TD> 
</TR> 
</TABLE> 
</FORM> 
 
<CENTER>
<FONT FACE="HELVETICA,HELV,ARIAL" COLOR="#FF0000" SIZE="2">
                <%
                If Request.QueryString("error") <> "" Then
                Response.Write(Request.QueryString("error"))
                End If
                %>
</FONT>
</CENTER>
 
</BODY>
</HTML>
 
 

<% 
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>
<%
 
WHILE NOT RS.EOF 
 
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
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>
 

[idsanjeev]

Hi hotflash
i think you have to change your code for count individually.
But Dr. B can help you out if is possible to count individually with your code.
i havn't any idea but i try to think if is possible.
regards
jha