Counting distinct values

[Guest]

Hi-
I am attempting to create a summary query where I can provide a count of how
many records exist per category (Grouped by Category), but I also need to
provide a count of how many unique values are in a particular field. I would
like these summarized fields in the query to exist side by side (right next
to each other in the same query). Is there a specific function I can utilize
in Access that will aggregate based on a unige value?

 

[Guest]

Some data examples and the table description would help. Sounds like you are
needing:
Select count([YourTbl].[SomeField] as CountOfField
From YourTbl
Group By YourTbl.Catagory

But I am taking a wild guess without some exaples of what you have and what
you want. Maybe somebody else will have a better solution

 

[James A. Fortune]

Hi-
I am attempting to create a summary query where I can provide a count of how
many records exist per category (Grouped by Category), but I also need to
provide a count of how many unique values are in a particular field. I would
like these summarized fields in the query to exist side by side (right next
to each other in the same query). Is there a specific function I can utilize
in Access that will aggregate based on a unige value?

This is a very interesting problem. Here is how I went about it:

tblGroupedData
RID Auto
GroupID Long
Data Text

RID GroupID Data
1 1 A
2 1 Null
3 1 Null
4 2 D
5 2 E
6 2 F

qryCountDistinct:
SELECT GroupID, (SELECT COUNT(*) FROM tblGroupedData AS A WHERE
Nz(A.Data, '') IN (SELECT Nz(B.Data, '') FROM tblGroupedData AS B WHERE
B.GroupID = tblGroupedData.GroupID AND B.RID > A.RID)) AS
CountDuplicated, Count(*) AS GroupCount, GroupCount - CountDuplicated AS
CountDistinct FROM tblGroupedData GROUP BY GroupID;

!qryCountDistinct:
GroupID CountDuplicated GroupCount CountDistinct
1 1 3 2
2 0 3 3

With different data:

RID GroupID Data
1 1 A
2 1 Null
3 1 Null
4 1 Null
5 2 D
6 2 D
7 2 F

!qryCountDistinct:
GroupID CountDuplicated GroupCount CountDistinct
1 2 4 2
2 1 3 2

With another set of data:

RID GroupID Data
1 1 A
2 1 A
3 1 B
4 1 B
5 2 Null
6 2 Null
7 2 F

!qryCountDistinct:
GroupID CountDuplicated GroupCount CountDistinct
1 2 4 2
2 1 3 2

Then the final version:

qryCountDistinct:
SELECT GroupID, GroupCount-(SELECT COUNT(*) FROM tblGroupedData AS A
WHERE Nz(A.Data, '') IN (SELECT Nz(B.Data, '') FROM tblGroupedData AS B
WHERE B.GroupID = tblGroupedData.GroupID AND B.RID > A.RID)) AS
CountDistinct, Count(*) AS GroupCount FROM tblGroupedData GROUP BY GroupID;

!qryCountDistinct:
GroupID CountDistinct GroupCount
1 2 4
2 2 3

It seems to work. Try it out and let us know. It may be possible to
improve it once I figure out how it works .

James A. Fortune
(e-mail address removed)