calc the total number of distinct values in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I calc the number of distinct values in a field.

Doing the following returns the distinct records (which in my case is 247).
I actually want to return just the number of these distinct records, i.e. 247)

Rgds,

Bruce

SELECT Distinct q_Stats_Pricelist.ProductID
FROM q_Stats_Pricelist
GROUP BY q_Stats_Pricelist.ProductID;
 
Hi Bruce, in SQL there is a count distinct, ie it does the distinct bit and
then does the count, as opposed to counting and then gicing the distinct.
Unfortunately there is not the same in Access.

For example
SELECT DISTINCT Count([Order Details].ProductID) AS CountOfProductID
FROM [Order Details];
Just tells me how many rows in the table, not how many different products
were sold.


If however I save the following as a query

SELECT DISTINCT [Order Details].ProductID
FROM [Order Details];

I can then do
? dcount("*", "Myquery")

or use it in another query.
 
Bruce,

Try this:

SELECT Count(ProductID) AS CountOfProductID
FROM (SELECT DISTINCT ProductID FROM q_Stats_Pricelist)

HTH,
Nikos
 
Back
Top