count number of search results

Hi all,

I'm a newbie in ASP here and I need some help from those experts out there

I'm trying to display the total number of records for every different items from a search result. Say that in my access DB there is a field called Products. In it is stored 4 different kinds of products (Prod A, Prod B, Prod C and Prod D). Lets say the total number of records in the DB for each products are:

Products----Total
Prod A--------3
Prod B--------6
Prod C-------32
Prod D-------1

Say I typed in some name that starts with 'M' in the search field. The search results will be:

Name------------Products
Mr. W--------------Prod A
Ms. X-------------Prod A
Mrs. Y--------------ProdC
Ms. Z------------Prod B

Now I have to create a table in my ASP page where it displays something like below:

Products------Totals_From_Search_Results
Prod A---------2
Prod B--------1
Prod C--------1
Prod D--------0

Do you think this is possible? I'm using Access/ASP and am totally stumped. Hope to hear from u guys soon~

Well ermm Im not sure what it is you want to achive and you have some issuses with your Db design Name is a reserved word so you should change this to something like Seller_Name

*Arghhg

Brains gone :¬|

Ill think of somthing and repost in a sec

post what your database structure looks like.
Tables, fieldNames, etc...

Hi guys,

Forgot to say I've found the solution a couple of days ago. Sorry for taking your time. Anyone interested can take a look at the codes I used below. Found it in some website. Cheerio!


<%
call query2table("SELECT Field1, COUNT(Field1) AS Search FROM MyTableName WHERE " & u_where &" LIKE '%%"& u_search & "%%' GROUP BY Field1")
%>

<%
sub query2table(inputquery)
DIM conntemp,myPath
SET conntemp = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("database.mdb")
conntemp.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"

SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>

<table width="36%" border="0" cellspacing="1" cellpadding="2" height="161">
<tr>
<td height="98">
<table border=1 bordercolor="#000066" bgcolor="#0066CC" align="center">
<tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
<td><b><font color="yellow"><%=rstemp(i).name%></font></b></td>
<% next %>
</tr>
<% ' Now lets grab all the records
do while not rstemp.eof %>
<tr>
<% for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue="&nbsp;"

end if%>
<td valign=top><%=thisvalue%></td>
<% next %>
</tr>
<%rstemp.movenext
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
<div align="center"><b></b></div>
<div align="center"></div>
</td>
</tr>
</table>