MS-access query

[Bala]

Hi,

I want to count the number of vendors in a table. For example vendor 1 conmtains 10 records, vendor 2 contains 15 records and so on.

How do write a query for it. Pls. help. Thanks

-Bala

 

[SydneyGeek]

Assuming that you want to know how many unique vendors you have in a table (let's assume Orders), ie Vendor A -- 10 records, Vendor B -- 15 records, vendor count -- 2, here is a way to do it using 2 queries.

qryGroupTheVendors:
Create a new query from the Orders table with the field [VendorID] inserted twice. In the toolbar, click the Sigma tool (I think the tooltip says Group or Totals). This will give you a new line, Totals, in the query grid.
In the first [VendorID] field, select Group By. In the second, select Count. Run the query -- you'll see something like

Vendor A 10
Vendor B 15
etc

Close and save the query.

qryUniqueVendors:
New query based on qryGroupTheVendors. Insert the first field from this query in the grid, click the Sigma button, select Count, and there you have it.

A bit round about, but no need for code. (And you may find some use for the first query elsewhere in the system...)

 

[Bala]

Yes, it works, thank you very much....


-Bala