Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Plano, Texas
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selective Record Count

    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.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Plano, Texas
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thx for your time, Whammy.

    I Have Quiz Questions in table:

    Quiz
    QuizID
    Name
    CategoryID

    I Have The Categories in table:

    Category
    CategoryID
    Name

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I just did something really similar...

    Code:
    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.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Plano, Texas
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fantastic! Thank You.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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/
    Last edited by whammy; 02-08-2003 at 10:38 PM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #7
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    I don't think he was going for the "The page cannot be found" look in the webdesign, but it's ORIGINAL!
    hehe.

  • #8
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    LOL. I liked it so much I just renamed that page to default.asp ...
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #9
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Originally posted by whammy
    LOL. I liked it so much I just renamed that page to default.asp ...
    I keep getting 404.

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?!?
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #11
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •