Accessing Database Information

I am trying to pull data from an Access database. The results of this data will be displayed in a table. I am guessing that it should be done with If/Then statements but can't seem to get the results I want. <br><br>As an example, if a database is a list of 1000 houses from around the USA. Some of the houses do not have pets, some have dogs, some have cats, some have goats, some are mixed with cats and dogs, etc. <br><br>I am trying to search on this database based on state. I want to retrieve all results per state then display if they have cats, dogs, horses, cats and dogs, etc. With the results looking like this:<br><br><br>CITY OWNER ANIMALS <br>Los Angeles Joe Smith Cat <br>San Fransisco Jane Smiley Dog, Horse <br>Fresno Cindy Person Cat, Horse <br>San Diego Mark Wilson Cat, Dog, Horse <br><br><br>I already have all the connections and returning results based on state. I just can't get the part as to how to display the other results, if there are any. <br><br>Here is the code I have so far: <br><br><br><br>Dim strAnimals <br>Dim strCat <br>Dim strDog <br>Dim strHorse <br><br>if u_id <> "" then <br> strSQL = "select * from AnimalRegistry1 WHERE user_id =" & u_id <br>else <br> strSQL = "select * from AnimalRegistry1 WHERE state LIKE '%" & request.form("state_abr") & "%' " & "ORDER BY City, Priority;" <br>end if <br><br>scriptresponder = "DisplayTrainer.asp" <br><br>objREC.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly, adCmdText <br> <br>numpages = objRec.PageCount <br>numrecs = objRec.RecordCount <br><br>If mypage > numpages Then mypage = numpages <br>If mypage < 1 Then mypage = 1 <br><br> if not objRec.EOF then <br><br>strAnimals = strAnimals + strCat + strDog + strHorse <br> <br>objRec.AbsolutePage = mypage <br><br>if u_id <> "" then <br><br> Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><td><div class='smallcopy' align='top'><b>Name: </b>" & objRec.Fields("First") & " " & objRec.Fields("Last") & " <br><b>City: </b>" & objRec.Fields("City") & " <br><b>State: </b>" & objRec.Fields("State1") & " <br><b>Zip Code: </b>" & objRec.Fields("Zip") & "</div></td><td><div class='smallcopy' align='top'><b>Phone Number: </b>" & objRec.Fields("Phone") & " <br><b>Email Address: </b><a href='mailto:" & objRec.Fields("Email") & "'>" & objRec.Fields("Email") & "</a>" & " <br><b>Animals: </b> <br>" & strAnimals & "</div></td></tr><tr><td> </td></tr>" <br><br>else <br><br>If objRec.Fields("Cat") = "Y" Then strCat = "Cat" <br>If objRec.Fields("Dog") = "Y" Then strDog = "Dog" <br>If objRec.Fields("Horse") = "Y" Then strHorse = "Horse" <br><br> Response.Write("<div class='maincopy' align='top'>Number of records found : " & numrecs & "</div> <br>") <br> <br> Response.Write "<table border='0' cellspacing='0' cellpadding='3'><tr><th><u>City</u></th><th><u>Owner</u></th><th><u>Animals</u></th></tr>" <br><br> Dim i <br> For i = 1 To pagesize <br> <br> If NOT objRec.EOF Then <br> <br> Response.Write "<tr><td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("City") & "</a>" & "</td>" & "<td><div class='smallcopy' align='top'>" & "<a href='" & my_link & "'>" & objRec.Fields("First") & " " & objRec.Fields("Last") & "</a></td>" & "<td><div class='smallcopy' align='top'>" & strAnimals & "</div></td></tr>" <br> objRec.MoveNext <br> 'loop <br> End If <br> Next <br> <br> Response.Write "<tr><td> </td></tr></table>" <br><br> Dim x, lb, ub <br> For x = 1 To numpages <br> lb = (x-1) * pagesize + 1 <br> ub = x * pagesize <br> If ub > numrecs Then ub = numrecs <br> If x <> mypage Then <br> Response.Write("<a href=DisplayTrainer.asp?page=" & x & "&recs=" & pagesize & "'>" & lb & "-" & ub & "</a>") <br> Else <br> Response.Write("<div class='maincopy' align='top'>Display results " & lb & "-" & ub & "</div>") <br> End If <br> <br> If x <> numpages Then Response.Write("|") <br> Next <br><br>end if <br><br> else <br> Response.Write "<div class='pageheadfont'>Animal Search: <br><img src='../../images/misc/transparent.gif' width='1' height='5'> <br></div><div class='maincopy'>Sorry, there are no Animals for this location.</div>" <br> Response.Write " <br> <br>" <br> end if <br> <br> objRec.Close <br> set objRec = nothing <br><br><br><br>Any help on this would be very helpful. Thank you.