• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

count(distinct) in MS Access

I'm developing an application which is eventually to be ported into Oracle database,
but currently I'm doing debugging with MS Access, since it's easier to setup and verify
what's going on.  The problem I encountered is Access doesn't seem to understand
the count(distinct) in the query.  In particular, my query is

SELECT COUNT(DISTINCT VarMatrixRow) FROM DecVariables

When running this, I'm getting the error

Syntax error (missing operator) in query expression 'COUNT(DISTINCT VarMatrixRow)'

Of course, I can go around it and write a code to count the number of distinct things in the table,
but that's not very efficient, especially since I'll be moving this to Oracle soon.  Any ideas?
0
shchuka
Asked:
shchuka
1 Solution
 
Anita030598Commented:
Yes, that fails in Access. However you can do that using two queries.

SELECT DISTINCT VarMatrixRow FROM DecVariables;
 - Say, you called it as Query1

Then write your next query as
SELECT Count(Query1.VarMatrixRow) AS VMRCount FROM Query1;

But, I don't know how it effects in Oracle.

0
 
shchukaAuthor Commented:
I knew about this one.  My idea is to do it in one query.  I need to do that 12 times in each iteration of a loop; the loop may iterate anywhere between 4 and 10 times, thus increasing the number of queries from potentially 120 to 240 - which is unacceptable in terms of performance.  Moreover, the

select count(x) from (select (distinct VarMatrixRow) x from DecVariables)

also fails in Access, since Access doesn't understand subqueries.

Thanks anyway.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now