count question

[alex]

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

 

[Guest]

See if this helps.

SELECT case_number, Count(case_number) As TheCount
FROM table_1
GROUP BY case_number;

 

[John W. Vinson]

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]

 

[alex]

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