loop thru record, filter the name?

loop, filter, record, access, asp

[Mansi]

Lo All,

I have a Access Db
table tblAddCounter
column counter_browse

Now in here is stored all Browser names,
i wanna loop thru them and then show on screen
the names that are in the record, and the total
count of that specific record name.

I can succeed in the count , i just cannot seem
to be able to filter the loop so the Browser names only shows on screen 1 time in stead of all the times it is in a record.

ok what i have so far :

<%
set rs = db.execute("Select counter_browse FROM tblAddCounter")
while not rs.EOF 
lID = rs("counter_browse")
If lID <> "" then
set rs1 = db.execute("Select COUNT(counter_browse) AS Total FROM tblAddCounter WHERE counter_browse = '"& lID &"' ")
%>
<tr>
<td><%=lID%></td>
<td><img src="<%=img%>poll_l.gif" alt=""><img src="<%=img%>poll.gif" alt="" height="12" width="<%= rs1("Total")%>"><img src="<%=img%>poll_r.gif" alt=""> <b><font class="orange"><%= rs1("Total") %></font></b></td>
</tr>
<%
rs1.Close
End If
rs.MoveNext
WEND
rs.Close
set rs = nothing
%>

now i got the empty fields out , and the correct count is shown, only the records get the same amount shown on screen.
Can someone help me with a filter or? to show the founded record only 1 time on screen.

thnx in advance
greets

[Mansi]

ok i solved it already :)

ok what is working :

<%
set rs = db.execute("Select counter_browse FROM tblAddCounter order by counter_browse asc")
while not rs.EOF 
BRID = rs("counter_browse")
if BRID <> last_BRID then
set rs1 = db.execute("Select COUNT(counter_browse) AS Total FROM tblAddCounter WHERE counter_browse = '"& BRID &"' ")
%>
<tr>
<td><%=BRID%></td>
<td><img src="<%=img%>poll_l.gif" alt=""><img src="<%=img%>poll.gif" alt="" height="12" width="<%= rs1("Total")%>"><img src="<%=img%>poll_r.gif" alt=""> <b><font class="orange"><%= rs1("Total") %></font></b></td>
</tr>
<%
rs1.Close
End If
last_BRID=BRID
rs.MoveNext
WEND
rs.Close
set rs = nothing
%>

had to campare each other and order them and voila the solution is presented :D

thnx
ME!?

[aspdaddy]

You could just use:

Select counter_browse, Count(counter_browse)
From tblAddCounter
Group By counter_browse
Order By Count(counter_browse) ASC

[Mansi]

wauw that worked great :)

i now have this :

<%
set rs = db.execute("Select counter_browse, Count(counter_browse) AS Total From tblAddCounter Group By counter_browse Order By Count(counter_browse) ASC ")
while not rs.EOF 
BRID = rs("counter_browse")
IF BRID <> "0" then
%>
<tr>
<td><%=BRID%>&nbsp;&nbsp;</td>
<td><img src="<%=img%>poll_l.gif" alt=""><img src="<%=img%>poll.gif" alt="" height="12" width="<%= rs("Total")%>"><img src="<%=img%>poll_r.gif" alt=""> <b><font class="orange"><%= rs("Total") %></font></b></td>
</tr>
<%
End If
rs.MoveNext
WEND
rs.Close
set rs = nothing
%>

but what if we spice it a little bit up?
like i now have this code :

<%
set rs = db.execute("Select counter_browse, counter_os FROM tblAddCounter order by counter_browse, counter_os desc")
while not rs.EOF 
BRID_today = rs("counter_browse")
OSID_today = rs("counter_os")
'If lID <> "" then
if BRID_today <> last_BRID_today OR OSID_today <> last_OSID_today then
set rs1 = db.execute("Select COUNT(counter_browse) AS Total FROM tblAddCounter WHERE counter_browse = '"& BRID_today &"' AND counter_os = '"& OSID_today &"' AND Day(counter_date) = " & Day(Date) & " AND Month(counter_date) = " & Month(Date) & " AND Year(counter_date) = " & Year(Date))
IF rs1("Total") <> "0" then
%>
<tr>
<td><%=OSID_today%>&nbsp;&nbsp;</td>
<td><%=BRID_today%>&nbsp;&nbsp;</td>
<td><img src="<%=img%>poll_l.gif" alt=""><img src="<%=img%>poll.gif" alt="" height="12" width="<%= rs1("Total")%>"><img src="<%=img%>poll_r.gif" alt=""> <b><font class="orange"><%= rs1("Total") %></font></b></td>
</tr>
<%
End If
rs1.Close
End If
last_BRID_today=BRID_today
last_OSID_today=OSID_today
rs.MoveNext
WEND
rs.Close
set rs = nothing
%>

i am going to think about it to, but it is late

thnx for the code aspdaddy ;)

[Mansi]

ok ok could not resist,
this is what i came up with , the code workz but can u say if it is properly done like this?

<%
set rs = db.execute("Select counter_browse, counter_os, COUNT(counter_browse) AS Total FROM tblAddCounter WHERE Day(counter_date) = " & Day(Date) & " AND Month(counter_date) = " & Month(Date) & " AND Year(counter_date) = " & Year(Date) & " Group By counter_browse, counter_os Order By Count(counter_browse) DESC ")
while not rs.EOF 
BRID_today = rs("counter_browse")
OSID_today = rs("counter_os")
IF rs("Total") <> "0" then
%>
<tr>
<td><%=OSID_today%>&nbsp;&nbsp;</td>
<td><%=BRID_today%>&nbsp;&nbsp;</td>
<td><img src="<%=img%>poll_l.gif" alt=""><img src="<%=img%>poll.gif" alt="" height="12" width="<%= rs("Total")%>"><img src="<%=img%>poll_r.gif" alt=""> <b><font class="orange"><%= rs("Total") %></font></b></td>
</tr>
<%
End If
rs.MoveNext
WEND
rs.Close
set rs = nothing
%>

eheh do like it saves me lines that is a good thing.