Discreet Count of Data

[Guest]

I am trying to count how many specific individual values appear in a query
column, but the COUNT function returns the number of records, not specific
values.
EG:
JAN
FEB
FEB
FEB
MAR

Is there any way using group funtions to return "3" to the above question
(i.e. JAN, FEB & MAR), rather than 5 (the count of records).
Alternatively is there any other way I can get the same details?

 

[Allen Browne]

You could create a query that returns the unique records, e.g.:
SELECT DISTINCT FieldName FROM TableName;
and then get the number of records with an expression like this:
=DCount("Query1")

If you want a DCount() type expression that counts distinct values, see:
ECount() - an extended DCount(). Count distinct values
at:
http://allenbrowne.com/ser-66.html

 

[Guest]

Using a group by query with a count on the month field, something like

Select MonthField , Count(MonthField) As CountMonth
From TableNAme
Group By MonthField


You can copy this SQL, change the name of the table and the field and try it

 

[Guest]

The long way round would be to right click in the top half the design grid,
go to the properties and change the "unique values" property to "yes". This
should give you:

Jan
Feb
Mar

Then create a second query using the first query as the source. Change it to
"totals" query and change the Total from "group by" to "count". This will
give you 3.

I am sure there is a shorter route

 

[Gary Walter]

I am trying to count how many specific individual values appear in a query
column, but the COUNT function returns the number of records, not specific
values.
EG:
JAN
FEB
FEB
FEB
MAR

Is there any way using group funtions to return "3" to the above question
(i.e. JAN, FEB & MAR), rather than 5 (the count of records).
Alternatively is there any other way I can get the same details?

In addition to all the sage responses,
there is also the Dassin xtab method
where you count the alias of transform, i.e.,

if your table were "T1" and month field "Mnth"

TRANSFORM Count(T1.Mnth) AS cnt
SELECT
Null AS Blank,
Count(T1.Mnth) AS TotalCnt,
Count([cnt]) AS DistinctCnt
FROM T1
GROUP BY Null
PIVOT T1.Mnth;

should give you

Blank TotalCnt DistinctCnt JAN FEB MAR
5 3 1 3 1

tricky SQL voodoo!

good luck,

gary