5 Replies Latest reply on Jun 5, 2006 11:32 AM by bfinelsen

    Recordcount of Query results

    bfinelsen Level 1
      I currently have a simple access database with 2 tables. The first table is simply a list of available categories in relation to products. The second table list all the products with a field that specifies which category it belongs in. Within my code, I specify a query that pulls all the categories and another query that pull all products. What I am trying to do is put the total number of products in paranthesis next to the category to give the user an idea of how many are in each for example:

      category1 (145)
      category2 (37)
      category3 (567)
      etc...

      How does one achieve this? Thanks in advance for any help.
        • 1. Re: Recordcount of Query results
          paross1 Level 2
          Something like this?

          SELECT c.CategoryName, COUNT(p.ProductID) AS prodCount
          FROM product p
          INNER JOIN category c ON c.categoryID = p.categoryID
          GROUP BY c.CategoryName

          Phil
          • 3. Re: Recordcount of Query results
            bfinelsen Level 1
            Phil,

            I changed the query to make my tables and field names:

            SELECT c.Categories, COUNT(p.ProductID) AS prodCount
            FROM Products p
            INNER JOIN Categories c ON c.categories = p.category
            GROUP BY c.Categories

            And what it appears to be doing is counting how many of the category names were used in the database rather than how many of each. I have 25 category names and 24 of them are being used (All is the other category used to select all of them, but it is not specified in any single record). So now, next to each category name, I have (24) listed for all of them. This seems to be a start, but not exactly what I'm trying to accomplish. What am I doing wrong?
            • 4. Re: Recordcount of Query results
              Dan Bracuk Level 5
              quote:

              Originally posted by: bfinelsen
              Phil,
              I changed the query to make my tables and field names:
              SELECT c.Categories, COUNT(p.ProductID) AS prodCount
              FROM Products p
              INNER JOIN Categories c ON c.categories = p.category
              GROUP BY c.Categories

              And what it appears to be doing is counting how many of the category names were used in the database rather than how many of each. I have 25 category names and 24 of them are being used (All is the other category used to select all of them, but it is not specified in any single record). So now, next to each category name, I have (24) listed for all of them. This seems to be a start, but not exactly what I'm trying to accomplish. What am I doing wrong?

              If you have 24 categories, and are showing results that indicate each category is associated with 24 products, I suspect that you are outputting the query recordcount instead of the prodCount.

              But I am only guessing.
              • 5. Re: Recordcount of Query results
                bfinelsen Level 1
                After taking another look at my code, I realized my error and by using the suggestion you made, Phil, I was able to achieve my desired look. Thank you for the help.