Count of Unique Values in Access Field
in [Queries]
|
From: KenSheridan via AccessMonster.com on 10 Mar 2010 14:05 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 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: vsm via AccessMonster.com on 11 Mar 2010 03:10 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 KenSheridan wrote: >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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: KenSheridan via AccessMonster.com on 11 Mar 2010 06:52 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. vsm wrote: >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] >>> >>>Any help would be much appreciated. -- Message posted via http://www.accessmonster.com
From: vsm via AccessMonster.com on 11 Mar 2010 07:16 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 KenSheridan wrote: >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] >>>> >>>>Any help would be much appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: KenSheridan via AccessMonster.com on 14 Mar 2010 12:08 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 vsm wrote: >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] >>>>> >>>>>Any help would be much appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
First
|
Prev
|
Pages: 1 2 Prev: Is it possible and how can it be done? Next: Column for line numbers... |