Solved

COUNT(DISTINCT column) syntax for access

Posted on 2008-10-23
20
1,586 Views
Last Modified: 2012-05-05
Hi,

It seems like the syntax COUNT(DISTINCT column) is not working in Access.

I simply need to count distinct (unique) values in a column. How can I do that in Access?

Thanks!

0
Comment
Question by:Bear2
  • 10
  • 4
  • 3
  • +2
20 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22785484
Select count(*) from (Select distinct fieldname from table)
0
 
LVL 22

Expert Comment

by:dportas
ID: 22785714
SELECT COUNT(*)
FROM
 (SELECT DISTINCT column
  FROM tbl
  WHERE column IS NOT NULL
 )  AS t;
0
 

Author Comment

by:Bear2
ID: 22785779
Well that works, but my query is a little bit more complex and I'm not sure how to use that syntax in my case.

I need a syntax for:

SELECT Sum(columnA) AS column1 , column2,  COUNT (DISTINCT columnC) AS column3
FROM TableA
GROUP BY column2;

Can you show how to use your syntax for that?

Thanks
0
 
LVL 75
ID: 22787501
How about this:

SELECT Table1.FIELD2, Sum(Table1.FIELD1) AS SumOfFIELD1, First(Table1.FIELD2) AS FirstOfFIELD2, Count(Table1.FIELD3) AS CountOfFIELD3
FROM Table1
GROUP BY Table1.FIELD2;


mx
0
 

Author Comment

by:Bear2
ID: 22794734
Unfortunetly that solution don't work because it is just using a simple count() which means that it counts all field3 but what I need is a function only counting distinct values just like COUNT(DISTINCT column) function that is not working in access.

Hope someone can come up with a solution for this one!
0
 
LVL 75
ID: 22797184
ok ... this is how you would Count just FIELD3:

SELECT Table1.FIELD3, Count(Table1.FIELD3) AS F3Count
FROM Table1
GROUP BY Table1.FIELD3;

mx
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22813088
Bear2,

Please post some sample data and the desired result.  Some pretty sharp guys are missing what you're saying ... so an example would help.
0
 

Author Comment

by:Bear2
ID: 22813325
Ok let me explain this again.

What I need a solution for is:

SELECT Sum(columnA) AS column1 , column2,  COUNT (DISTINCT columnC) AS column3
FROM TableA
GROUP BY column2;

In this problem columnA is a dollar amount, column2 is vehicle version and columnC is registration number. Each row in table A is a invoice.
A registration number may occur more than once because a vehicle can have more than one invoice.
I want to group the invoices on vehicle versions (column2).

I do not want to count the number of times a registration number is billed.

I do want to sum the invoices (column A) grouped by vehicle version (column2) and count the number of vehicles. Because I need to count the number of vehicles in each vehicle version I only want to count the registration number ONCE. Therefore I want to use the COUNT (DISTINCT columnC) that unfortunately is not working in access.

I really appreciate if someone has a solution for this one in access.

Thanks!




0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22813404
OK, taking a stab at sample data ...

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345


And the result would look like  ...
75       SLT           2
75       TRK           1

Or ... am I missing something?
0
 

Author Comment

by:Bear2
ID: 22813434
Yes that is exactly the result I need from the query.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22813568
If this doesn't get it, please show example data for which it fails.  For the example I provided it will work.

Select sum(ColumnA), ColumnB, Count(ColumnC)

From MyTable

Group by ColumnB

Open in new window

0
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 22813585
COUNT(DISTINCT) is going to be rather painful to do in Access because there is no direct equivalent and Access has trouble with correlated subqueries. Try the following:

SELECT t1.column1, t1.column2, NZ(t2.column3,0) AS column3
FROM (SELECT SUM(columnA) AS column1, column2
   FROM TableA
   GROUP BY column2
   )  AS t1 LEFT JOIN (SELECT column2, COUNT(*) AS column3
   FROM
   (SELECT DISTINCT column2, columnC
    FROM TableA
    WHERE columnC IS NOT NULL)t
   GROUP BY column2
   )  AS t2 ON t1.column2 = t2.column2;

BTW, I note that the result suggested by Daniel's sample data is actually COUNT(*) and NOT COUNT(DISTINCT). Hope that's clear.
0
 

Author Comment

by:Bear2
ID: 22813702
No I'm really sorry!

Your example:

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345


And the result would look like  ...
75       SLT           2
75       TRK           1

Or ... am I missing something?

Yes the result should look like:

75   SLT  1
75 TRK    1

That is because both rows with SLT has the same registration number 1F123.

To extend your example:

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345
40           SLT            4D325
80           TRK            2D345
70            TRK           7C765
50             TRK           8C567

The result should be:

115    SLT    2
275    TRK    3

Makes sense? I only want to count unique calues of colC!
 
0
 
LVL 22

Expert Comment

by:dportas
ID: 22814709
Did you try my query?
0
 

Author Comment

by:Bear2
ID: 22820101
dportas,

I haven't tried your query yet but I will today. I let you know about the result.

Thanks
0
 

Author Comment

by:Bear2
ID: 22820764
dportas,

Your solution is working, but the problem is that when trying to save this query in Access the database crashes so I am not able to save it. I am trying to find a solution for that but no success so far. Any suggestion? Can you save that query in Access?

Regards
0
 

Author Comment

by:Bear2
ID: 22829175
dportas,

I have tried to modify your query but have not found a solution.

As I wrote your query is giving me the expected result, but I can not save that query because Access gets some kind of performance issue and closes down. Therefore I can not use the query.
I appreciate if you have another solution for this problem or a way to modify your query.

Thanks in advance!
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22830541
Before putting in dportas' code, try doing a Compact & Repair on your DB.

Then put his code in & see if you can save it w/o the crash.
0
 

Author Comment

by:Bear2
ID: 22841771
It seem like there is no perfect code for this in Access, but I have got dportas code to work without having my database crashing when saving.

This is what I did:

I deleted all rows in my huge table that the query is running towards except for a few rows.
That made the query to run easier and I was able to save it without a crash.
After that I could import my huge table and run my query with success and also use it as a subquery for other queries.

Thanks for your assistance!
0
 

Author Closing Comment

by:Bear2
ID: 31509167
Thanks for a good solution. Unfortunately ACCESS has performance issues with this types of solutions but I got it to work finally.

Thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now