Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Dec 2004
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How to Eliminate Duplicates from a field within recordset in ASP

    Hi All,

    Any help/suggestions on the following problem would be greatly appreciated. I am having trouble eliminating duplicate names that are displaying from a recordset in ASP. The ASP page is connected to an Access database. The database holds information on bad checks. There are 2 tables involved DEFENDANT and CHECK DECEPTION. I am using fields Defendant Name, Address1, City, State, Zip from DEFENDANT and fields Theft from CHECK DECEPTION. I am then using DISTINCT in the main select statement along with a subselect statement to eliminate duplicates. The problem is that when I add the field Theft (which is a yes/no field) to the main select statement...if a defendant has written more than one check (one being a theft and one not), the name will be displayed twice. On my ASP page, I want the name to only be displayed once, and the one with the (T) next to it, is the one I'd like to have displayed. For example if in February, John Smith wrote 2 checks and the one check was a Theft...here's how it displays:

    Bad Check List for February 2006
    (T) SMITH, JOHN 5555 S THIS ST LAFAYETTE, IN 47905
    SMITH, JOHN 5555 S THIS ST LAFAYETTE, IN 47905

    I just want the first line to display...I have tried everything under the sun to eliminate the duplicate name from within the query in Access and nothing has worked. I was wondering if there is a way to eliminate duplicates from within ASP?? My query in Access is a make table query that creates a table named CHECKS_ORIG, I then write the SELECT statement in ASP to pull records from this table. Here's my make table query in Access:
    Code:
    SELECT DISTINCT Defendant.[Defendant Name], Defendant.Address1, Defendant.City, Defendant.State, Defendant.Zip, [Check Deception].Theft, [Check Deception].[Received Date] INTO Checks_Orig
    FROM Defendant, [Check Deception]
    WHERE (((Defendant.[Check Join])=[Check Deception].[Defendant Join] And (Defendant.[Check Join]) In (SELECT [Defendant Join] FROM [Check Deception])))
    ORDER BY Defendant.[Defendant Name];
    Here is my code for the asp page:

    Code:
     <%
        Set objConn = Server.CreateObject("ADODB.Connection")
    	Set objRS = Server.CreateObject("ADODB.Recordset")
    
    	objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("BadChecks.mdb")	 & ";"
    
         f=Request.QueryString("f")
    
    	   If f = "" Then
    	   	f = 1
    	   End If
       Select Case f
    	   Case 1
    
        sysyear = year(date())
    
      %>
    <%
    Response.Write "<FORM ACTION='" &Request.ServerVariables("SCRIPT_NAME")& "?f=2' METHOD='Post' NAME='form1'>"
    %>
    <!-- <form action="Checks2.asp?f=2" method="post" name="BAD_CHECKS_FORM" class="style1" id="BAD_CHECKS_FORM"> -->
      <!--<% Response.Write sysyear %>-->
      <p align="center"><strong>This is a database of bad checks written in <br>
      Tippecanoe County, Indiana. &nbsp;</strong></p>
      <p align="center">Select the month and year from the drop-down boxes below.<br>
        <br>
        Names listed with a '<strong>T</strong>' have been charged with theft.<br>
        All others are misdeameanor check deception charges. <br>
      </p>
      <p align="center">Month:&nbsp;
        <select name="MONTH" size="1" id="MONTH">
          <option value="1" selected>January</option>
          <option value="2">February</option>
          <option value="3">March</option>
          <option value="4">April</option>
          <option value="5">May</option>
          <option value="6">June</option>
          <option value="7">July</option>
          <option value="8">August</option>
          <option value="9">September</option>
          <option value="10">October</option>
          <option value="11">November</option>
          <option value="12">December</option>
        </select>
        <br>
        Year:&nbsp;
        <select name="YEAR" size="1" id="YEAR">
          <%
          numyear = (sysyear - 2)
          Do until sysyear = numyear
    
         %>
           <!--Value is set to equal to the EstablismentID -->
    	  <option value="<%=sysyear%>">
    	  <!--What is going to appear in the combo box -->
    	  <%=sysyear%></option>
    
          <%
          'response.write("The counter is: "&sysyear&"<br>")
          sysyear= (sysyear-1)
          loop
    
          %>
         <!-- <option value="2004">2004</option> -->
         <!--  <option value="2005" selected>2005</option> -->
       </select>
      </p>
      <p align="center">
        <input type="submit" name="Submit" value="Submit">
    </p>
    </form>
      <%
        Case 2
    
        'SQL statement goes here
         strMonth = Request.Form("MONTH")
         strYear = Request.Form("YEAR")
       ' Response.Write strMonth
        ' Response.Write (" ")
        ' Response.Write strYear
    
         SQL =  "SELECT DISTINCT [Defendant Name], Address1, City, State, Zip, Theft" & _
    			" FROM Checks_Orig" & _
             	" WHERE (Month([Received Date]) = '" & strMonth & "' and Year([Received Date]) = '" & strYear & "')" & _
    			" ORDER BY [Defendant Name]"
    			 
    	 '  Response.write (SQL)
        objRS.Open SQL, objConn
       %>
    
       <TABLE CELLPADDING="2" CELLSPACING="1" WIDTH="80%" ID="Table1" border="1" align="center" bordercolor="#425284">
    
       			<%
    			   If Not objRS.EOF Then
     			       'strDate = objRS("CheckDate")
     				   'strMonthDate = objRS("MonthDate")
     				   'strYearDate = objRS("YearDate")
    				   strTheft = objRS("Theft")
     				   strName = objRS("Defendant Name")
     				   strAdd1 = objRS("Address1")
    				   'strAdd2 = objRS("Address2")
     				   strCity = objRS("City")
           		       strState = objRS("State")
                       strZip = objRS("Zip")
                       strMonth = Request.Form("MONTH")
                       strYear = Request.Form("YEAR")
                       Dim strDisplay
    				   
    	  Function iif(psdStr, trueStr, falseStr)
    	      if psdStr then
    		    iif = trueStr
    		  else
    		    iif = falseStr
    		  end if
        	End Function
    
                      If strMonth = 1 Then
                        strDisplay = "January"
                       ElseIf strMonth = 2 Then
                        strDisplay = "February"
                       ElseIf strMonth = 3 Then
                        strDisplay = "March"
                       ElseIf strMonth = 4 Then
    				    strDisplay = "April"
    				   ElseIf strMonth = 5 Then
    				    strDisplay = "May"
    				   ElseIf strMonth = 6 Then
    				    strDisplay = "June"
    				   ElseIf strMonth = 7 Then
    				    strDisplay = "July"
    				   ElseIf strMonth = 8 Then
    				    strDisplay = "August"
    				   ElseIf strMonth = 9 Then
    				    strDisplay = "September"
    				   ElseIf strMonth = 10 Then
    				    strDisplay = "October"
    				   ElseIf strMonth = 11 Then
    				    strDisplay = "November"
    				   ElseIf strMonth = 12 Then
    				    strDisplay = "December"
                    End If
    					 Response.Write "<TR>"
    					 Response.Write "<TH>" & "Bad Check List for " & strDisplay & " " & strYear & "</TH>"
    			         Response.Write "</TR>"
                     objRS.MoveFirst
    				
                     Do While Not objRS.EOF
    				 
    				        Response.Write "<TR>"
                            Response.Write "<TD>" & "<B>" & iif(objRS("Theft"), "(T) ", "") & objRS("Defendant Name") & "</B>" & " " & objRS("Address1") & "&nbsp;" & "&nbsp;" & "&nbsp;" & objRS("City") & (", ") & objRS("State") & " " & objRS("Zip") & "</TD>"
         		     		Response.write "</TR>"
                      
         	    	   	objRS.MoveNext
    					
         	    	 Loop
                    
    
    				Else
    				objRS.Close
    				Response.Write "<TR>"
    				Response.Write "<TD>" & "No Records Found" & "</TD>"
    				  		Response.Write "</TR>"
    
    	    		End If
    %>
    <tr><td>
      <%
      Response.Write "<a href='" &Request.ServerVariables("SCRIPT_NAME")& "?f=1'>"
      %>
       Back </a> </td></tr>
      <%
    	End Select
    
        objConn.Close
    	Set objConn=Nothing
    
    	%>
    Thank You,
    Shannon

  • #2
    Registered User
    Join Date
    Jan 2006
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not going to take the time to go through your code (it's time to go home from work!) but I can get you started quick.

    I'm assuming you're running your recordset through a loop. So create a temp variable for that loop.

    At the END of the loop say something like

    tempName = strName

    And that at the BEGINNING of the loop wrap everything in an if statement

    If (tempName <> objRS("Defendant Name")) Then

    }


    That should get you started...I've used this method before.

  • #3
    New Coder
    Join Date
    Dec 2004
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Archangel,

    Thanks so much for your reply and help. I did get it to work...here's the code I used if anyone is interested:

    Code:
    Do While Not objRS.EOF
    				    strCurrentName = objRS("Defendant Name")
    					 If strCurrentName<>strLastName Then
    				        Response.Write "<TR>"
                            Response.Write "<TD>" & "<B>" & iif(objRS("Theft"), "(T) ", "") & objRS("Defendant Name") & "</B>" & " " & objRS("Address1") & "&nbsp;" & "&nbsp;" & "&nbsp;" & objRS("City") & (", ") & objRS("State") & " " & objRS("Zip") & "</TD>"
         		     		Response.write "</TR>"
    					End If
    					strLastName = strCurrentName	
    					objRS.MoveNext
                  	Loop
    Thanks Again,
    Shannon

  • #4
    Registered User
    Join Date
    Jan 2006
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Glad to help


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •