PDA

View Full Version : How to Eliminate Duplicates from a field within recordset in ASP

sueberry
03-02-2006, 09:15 PM
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:

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:


<%
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
Archangel
03-02-2006, 11:01 PM
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.
sueberry
03-03-2006, 09:11 PM
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:


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
Archangel
03-03-2006, 09:54 PM
Glad to help :thumbsup: