Count of Unique Values in Access Field

[vsm]

In the query given below which is done in Access

SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time
days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS
[CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Lead Business], [Q Test].Region;

The field Count([Q Test].[Contract ID]) lists the count of contract ids
including duplicates.

What should I do to let Access Return only count of unique values. For
example, there are 100 records having contract id out of which only 10 are
unique, balance 90 are repetitions.

While currently access returns 100 for the field, I want access to return 10
which is unique values for the field.

Any help would be much appreciated.

 

[Stefan Hoffmann]

hi,

While currently access returns 100 for the field, I want access to return 10
which is unique values for the field.

Take a look at

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx


mfG
--> stefan <--

 

[Jerry Whittle]

SELECT [Q Test].[Contract ID] ,
Count([Q Test].[Contract ID]) AS [CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Contract ID] ;

 

[vsm]

Thanks Stefan. I had earlier bumped upon this, but am not to convert my
query as per the solution proposed there. Thanks for your link once again.

 

[vsm]

Thanks Jerry. However, I need the summary values of other fields as well.
That is Average of Cycle Time and this has to be grouped by Lead business and
region. When I club those fields in this query, it does not work.

SELECT [Q Test].[Contract ID] ,
Count([Q Test].[Contract ID]) AS [CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Contract ID] ;

In the query given below which is done in Access

[quoted text clipped - 15 lines]

Any help would be much appreciated.

 

[KenSheridan via AccessMonster.com]

Try this:

SELECT [Lead Business], Region,
AVG([Cycle time days]) AS [Average Cycle time days],
(SELECT COUNT(*)
FROM
(SELECT DISTINCT [Lead Business], Region, [Contract ID]
FROM [Q Test]) As QT2
WHERE QT2.[Lead Business] = QT1.[Lead Business]
AND QT2.Region = QT1.Region)
AS [Count of Distinct Contract IDs]
FROM [Q Test] As QT1
GROUP BY [Lead Business], Region;

Ken Sheridan
Stafford, England

In the query given below which is done in Access

SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time
days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS
[CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Lead Business], [Q Test].Region;

The field Count([Q Test].[Contract ID]) lists the count of contract ids
including duplicates.

What should I do to let Access Return only count of unique values. For
example, there are 100 records having contract id out of which only 10 are
unique, balance 90 are repetitions.

While currently access returns 100 for the field, I want access to return 10
which is unique values for the field.

Any help would be much appreciated.

 

[vsm via AccessMonster.com]

Thanks for your idea. I get an error message as below

"You tried to execute a query that does not include the specified expression
" as part of an aggregate function"

Regards

Try this:

SELECT [Lead Business], Region,
AVG([Cycle time days]) AS [Average Cycle time days],
(SELECT COUNT(*)
FROM
(SELECT DISTINCT [Lead Business], Region, [Contract ID]
FROM [Q Test]) As QT2
WHERE QT2.[Lead Business] = QT1.[Lead Business]
AND QT2.Region = QT1.Region)
AS [Count of Distinct Contract IDs]
FROM [Q Test] As QT1
GROUP BY [Lead Business], Region;

Ken Sheridan
Stafford, England

In the query given below which is done in Access

[quoted text clipped - 15 lines]

Any help would be much appreciated.

 

[KenSheridan via AccessMonster.com]

The query as I posted it will work with a table Q Test containing the columns
included in the query. Have you added more columns to the query? If so post
back with the SQL of the query.

I'm away for the rest of this week, so won't be able to get back to you again
before Sunday at the earliest.

Thanks for your idea. I get an error message as below

"You tried to execute a query that does not include the specified expression
" as part of an aggregate function"

Regards

Try this:

[quoted text clipped - 18 lines]

 

[vsm via AccessMonster.com]

Ken,

Thanks for your concern. I checked my query and again copy pasting the same.

SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time
days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS
[CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Lead Business], [Q Test].Region;

Since this is in MS Access, I achieved the end result by writing three
queries. Used one query as base for another and in this manner i could
achieve the end result.

But i am sure there is a better way to do this which you intelligent guys
must be having. It is making the dumb machine to do what we want makes the
process very exciting.

Have a fantastic weekend.

Regards

The query as I posted it will work with a table Q Test containing the columns
included in the query. Have you added more columns to the query? If so post
back with the SQL of the query.

I'm away for the rest of this week, so won't be able to get back to you again
before Sunday at the earliest.

Thanks for your idea. I get an error message as below

[quoted text clipped - 8 lines]

 

[KenSheridan via AccessMonster.com]

You are not using any columns other than the ones in the query I posted, do I
don't understand why it raises an error. I have tested it by creating a
table of the same name and with the same columns as those you posted.

I imagine what you've done with the three queries probably woks in much the
same way as my single query with the two subqueries. You might even find
that with a large number of rows in the table yours would perform faster.

Ken Sheridan
Stafford, England

Ken,

Thanks for your concern. I checked my query and again copy pasting the same.

SELECT [Q Test].[Lead Business], [Q Test].Region, Avg([Q Test].[Cycle time
days]) AS [AvgOfCycle time days], Count([Q Test].[Contract ID]) AS
[CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Lead Business], [Q Test].Region;

Since this is in MS Access, I achieved the end result by writing three
queries. Used one query as base for another and in this manner i could
achieve the end result.

But i am sure there is a better way to do this which you intelligent guys
must be having. It is making the dumb machine to do what we want makes the
process very exciting.

Have a fantastic weekend.

Regards

The query as I posted it will work with a table Q Test containing the columns
included in the query. Have you added more columns to the query? If so post

[quoted text clipped - 8 lines]