COUNT(DISTINCT column) syntax for access

Select count(*) from (Select distinct fieldname from table)

SELECT COUNT(*)
FROM
 (SELECT DISTINCT column
  FROM tbl
  WHERE column IS NOT NULL
 )  AS t;

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

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

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!

ok ... this is how you would Count just FIELD3:

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

mx

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.

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!

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?

Yes that is exactly the result I need from the query.

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

Select allOpen in new window

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!
 

Did you try my query?

dportas,

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

Thanks

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

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!

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.

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!

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

Thank you!