<div>
I knew about this one. &nbsp;My idea is to do it in one query. &nbsp;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. &nbsp;Moreover, the<br />
<br />
select count(x) from (select (distinct VarMatrixRow) x from DecVariables)<br />
<br />
also fails in Access, since Access doesn't understand subqueries.<br />
<br />
Thanks anyway.<br />

</div>


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.


<div>
<div class="content wysiwyg-content">
I'm developing an application which is eventually to be ported into Oracle database,<br />
but currently I'm doing debugging with MS Access, since it's easier to setup and verify<br />
what's going on. &nbsp;The problem I encountered is Access doesn't seem to understand<br />
the count(distinct) in the query. &nbsp;In particular, my query is<br />
<br />
SELECT COUNT(DISTINCT VarMatrixRow) FROM DecVariables<br />
<br />
When running this, I'm getting the error<br />
<br />
Syntax error (missing operator) in query expression 'COUNT(DISTINCT VarMatrixRow)'<br />
<br />
Of course, I can go around it and write a code to count the number of distinct things in the table,<br />
but that's not very efficient, especially since I'll be moving this to Oracle soon. &nbsp;Any ideas?
</div>
</div>


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?

count(distinct) in MS Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.