Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Unanswered: Count records with a particular ID. Help with query.

    Hi there! I am coding a database (web based front end) which has (among other things) an articles section, where people can submit articles into a particular category.

    Now, I have an article 'overview' screen, that shows which categories are available, and I would like to also show *how many* articles there are in each category. I can execute a seperate query for each category, but that seems wasteful. I think it *should* be possible to get the results back in a record set, one row containing the count for each category, using one single query. Only trouble is, I can't code the query!

    I have two tables in my database, one called tblCategories:

    ID - Number (primary key)
    CategoryName (string)
    CategoryDesc (string)

    Now, a second table stores the actual articles: tblArticles

    ID - Number (primary key)
    CategoryID (foriegn key into tblCategories)
    etc...

    (please see the next post - message length!)
    Mark Wills.

  2. #2
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    So, if I want to know how many records in tblArticles have a CategoryID of 1, and how many articles have a CategoryID of 2 etc, I thought the query would be something like:

    SELECT COUNT (*) FROM tblArticles WHERE CategoryID = (SELECT ID FROM tblArticleCategories)

    However, access complains that 'At most one record can be returned by this sub-query.' Hmmmm.... Ok, so where am I going wrong?

    If anyone can shed some light on this I'd be grateful...

    Mark Wills.
    Mark Wills.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select CategoryName, CategoryDesc
    , count(*) as CategoryArticles
    from tblCategories
    inner join tblArticles
    on tblCategories.ID = tblArticles.CategoryID
    group by CategoryName, CategoryDesc

    rudy
    http://rudy.ca/

Posting Permissions

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