Hello Experts,

I have a table with four columns and 3000 distinct records.

One of the aforementioned columns is named case_number with many
duplicates in that column.

I created a query to count the number of distinct case_numbers within
the column (something that I do all the time w/ SQL in ORACLE).

E.g.,

select count(distinct(case_number))
from table_1;

Access, however, returns a count of all case_numbers, including
duplicates.
I created this workaround:

SELECT Count(case_number) AS COUNT
FROM [select distinct case_number
FROM table_1]. AS [table_1];

I'm wondering why Access won't conduct a count of distinct records on
a particular row (using the first example). Is there something I'm
doing wrong? Thanks for any help.

alex

See if this helps.

SELECT case_number, Count(case_number) As TheCount
FROM table_1
GROUP BY case_number;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"alex" wrote:

> Hello Experts,
>
> I have a table with four columns and 3000 distinct records.
>
> One of the aforementioned columns is named case_number with many
> duplicates in that column.
>
> I created a query to count the number of distinct case_numbers within
> the column (something that I do all the time w/ SQL in ORACLE).
>
> E.g.,
>
> select count(distinct(case_number))
> from table_1;
>
> Access, however, returns a count of all case_numbers, including
> duplicates.
> I created this workaround:
>
> SELECT Count(case_number) AS COUNT
> FROM [select distinct case_number
> FROM table_1]. AS [table_1];
>
> I'm wondering why Access won't conduct a count of distinct records on
> a particular row (using the first example). Is there something I'm
> doing wrong? Thanks for any help.
>
> alex
>
>

On Thu, 25 Oct 2007 11:18:29 -0700, alex <(E-Mail Removed)> wrote:

>Hello Experts,
>
>I have a table with four columns and 3000 distinct records.
>
>One of the aforementioned columns is named case_number with many
>duplicates in that column.
>
>I created a query to count the number of distinct case_numbers within
>the column (something that I do all the time w/ SQL in ORACLE).

For some reason that I can't understand, JET SQL does not support the very
reasonable Count Distinct syntax. You'll need to base a query on a query:

SELECT Count(*) FROM
(SELECT DISTINCT Case_Number FROM yourtable);

John W. Vinson [MVP]

On Oct 25, 5:33 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 25 Oct 2007 11:18:29 -0700, alex <sql_...@yahoo.com> wrote:
> >Hello Experts,
>
> >I have a table with four columns and 3000 distinct records.
>
> >One of the aforementioned columns is named case_number with many
> >duplicates in that column.
>
> >I created a query to count the number of distinct case_numbers within
> >the column (something that I do all the time w/ SQL in ORACLE).
>
> For some reason that I can't understand, JET SQL does not support the very
> reasonable Count Distinct syntax. You'll need to base a query on a query:
>
> SELECT Count(*) FROM
> (SELECT DISTINCT Case_Number FROM yourtable);
>
> John W. Vinson [MVP]

Thank you gentlemen for your help.

alex