View Full Version : Selective Record Count

02-08-2003, 03:20 AM
I Have A MS Access 2000 Database that has Records with CategoryID identifying categories such as

Animals. Automobiles, Brain Teasers etc...

On a Selection Page, How Do I get the number to reflect the correct record count for those items. Similar to WebPages that have free scripts and they show you the categories with the number of scripts in each one.?

I know how to handle the link to pull the records for the category selected, but I would like to know when I build the index page how to capture that record count the most effective way. Do I have to loop thru the DB each time and count the records with the category as a select parameter or what??

The desired output would be something like below particularly on how to get the count in bold:

Animals {134}
Automobiles {72}
Brain Teasers {34}
Business {212}
Celebrities {23}
Entertainment {25}
For Children {325}
General {15}
Geography {32}
History {175}
Hobbies {32}
Humanities {46}

Any suggestions and or direction would certainly be appreciated.

02-08-2003, 11:59 AM
How is your related table set up, with the primary and related keys I should be able to whip up a nice join statement for you.

02-08-2003, 04:03 PM
Thx for your time, Whammy.

I Have Quiz Questions in table:


I Have The Categories in table:


02-08-2003, 04:32 PM
Ok, I just did something really similar...

Sub DisplayCategories()
CATEGORYQUERY = "SELECT cid,name FROM category WHERE active = True ORDER BY name"
Set rs = Conn.Execute(CATEGORYQUERY)
Do While NOT rs.EOF
categorystring = categorystring & rs("cid") & "|" & rs("name") & ";"
If categorystring <> "" Then
categorystring = Left(RTrim(categorystring),Len(categorystring)-1)
End If

categoryarray = Split(categorystring,";")
For i = 0 to UBound(categoryarray)
splitcatarray = Split(categoryarray(i),"|")
Response.Write(Space(indent*3) & "<div class=""bolded"">" & splitcatarray(1) & "</div>" & vbCrLf)
Response.Write(Space(indent*3) & "<div>" & vbCrLf)
Response.Write(Space(indent*4) & "<ul>" & vbCrLf)
SUBCATEGORYQUERY = "SELECT subid, subname FROM subcategory WHERE cid = " & splitcatarray(0) & " AND Active = True ORDER by subname"
Set rs = Conn.Execute(SUBCATEGORYQUERY)
Do While NOT rs.EOF
Dim countquery, rsCount
countquery = "SELECT COUNT(subid) AS MyCount FROM scripts WHERE subid = " & rs("subid")
Set rsCount = Conn.Execute(countquery)
Response.Write(Space(indent*5) & "<li><a href=""scripts.asp?subid=" & rs("subid") & """>" & rs("subname") & " (" & rsCount("MyCount") & ")</a></li>" & vbCrLf)
Set rsCount = Nothing
Response.Write(Space(indent*4) & "</ul>" & vbCrLf)
Response.Write(Space(indent*3) & "</div>")
End Sub

Access is a pain when trying to do counts and stuff, I find it easier with Access to do a separate query while looping, as shown above.

Hope this helps, basically what I did was get the "subid" from my first query, and then use that to get a count each time (about like what you said in your first post).

If this was SQL Server, it would be much easier to get the count, but oh well.

02-08-2003, 04:35 PM
Fantastic! Thank You.

02-08-2003, 04:39 PM
No- thank you! You inspired me to add this nice feature to my website (still in redesign, excuse the ugliness!), I assume something like this result is what you're looking for:


02-08-2003, 06:03 PM
I don't think he was going for the "The page cannot be found" look in the webdesign, but it's ORIGINAL!

02-08-2003, 11:39 PM
LOL. I liked it so much I just renamed that page to default.asp ...

02-09-2003, 06:38 AM
Originally posted by whammy
LOL. I liked it so much I just renamed that page to default.asp ...

I keep getting 404.

02-09-2003, 06:53 PM
Yeah... something weird happened and everything got deleted from my website?

Good think I back it up; however now it looks like write permissions got changed or something... I can't login or write to tables, but I can read from them?!?

02-09-2003, 09:58 PM
Yeah, thats the write perrmissions..
Check with your admin, or if this is your server, on a windows paltform, right click, go to properties, then go to secruity, then everyone should be set to modify and write.