...

View Full Version : Selective Record Count



JoeP
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.

whammy
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.

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

I Have Quiz Questions in table:

Quiz
QuizID
Name
CategoryID

I Have The Categories in table:

Category
CategoryID
Name

whammy
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") & ";"
rs.MoveNext
Loop
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
rs.MoveNext
Loop
Response.Write(Space(indent*4) & "</ul>" & vbCrLf)
Response.Write(Space(indent*3) & "</div>")
Next
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.

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

whammy
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:

http://www.solidscripts.com/

Morgoth
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!
hehe.

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

Morgoth
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.

whammy
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?!?

Morgoth
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum