Prev: Is it possible and how can it be done?
Next: Column for line numbers...
From: vsm on 10 Mar 2010 07:14
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.

From: Stefan Hoffmann on 10 Mar 2010 08:07
hi,

On 10.03.2010 13:14, vsm wrote:
> 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 <--
From: Jerry Whittle on 10 Mar 2010 08:38
SELECT [Q Test].[Contract ID] ,
Count([Q Test].[Contract ID]) AS [CountOfContract ID]
FROM [Q Test]
GROUP BY [Q Test].[Contract ID] ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"vsm" wrote:

> 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.
From: vsm on 10 Mar 2010 08:55
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.

Stefan Hoffmann wrote:
>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 <--

From: vsm on 10 Mar 2010 09:32
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.

Jerry Whittle wrote:
>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.

 |  Next  |  Last
Pages: 1 2
Prev: Is it possible and how can it be done?
Next: Column for line numbers...