Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    50

    Unanswered: Ms Access (counting fields)

    Hello everyone

    I have a two tables Products and Suppliers

    in the Products table there's fields such as

    product ID , ProductName, SupplierID etc.

    and in Suppliers table i have fields such as

    SupplierID, CompanyName, CompanyAddress etc.

    What I wanna do is. I wanna make a query that creates a table that shows the number of products that a supplier Supplies.

    so something like this

    Company A - 3 products
    Company B - 2 products
    Company C - 5 products
    and so on..

    can someone tell me how to do that plz

    Thanks in Advance
    Deep

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Programmer,

    You can make a select query that shows companies and their count of products like this:

    SELECT
    Suppliers.CompanyName,
    Count(Products.ProductId) AS CountOfProducts
    FROM Suppliers
    INNER JOIN Products ON Suppliers.SupplierId = Products.SupplierId
    GROUP BY Suppliers.CompanyName
    ORDER BY Suppliers.CompanyName;

    Change it to a "make table" query if you want to store this in a table, but I'd suggest you leave it as a select query, that way, you don't have an extra table hanging around that need to be updated when the data change.

Posting Permissions

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