Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

COUNT(DISTINCT column) syntax for access

Posted on 2008-10-23
20
Medium Priority
?
1,634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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 1500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

664 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