Solved

COUNT(DISTINCT column) syntax for access

Posted on 2008-10-23
20
1,581 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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

19 Experts available now in Live!

Get 1:1 Help Now