Help creating a query to count Unique (Distinct) Values

[newnewbie]

Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our system and thinks I am omnipotent now and can extract any data out of it in the form he wants….The truth is, though I know SOME Access, I am not a programmer…and many queries that he wants me to do have a potential of being monstrous towers of queries built upon queries, etc. ( I do not know SQL or VBA)… HELP!

Table Name: 31 Table

Fields:
caseno after_step HistEntryDate HeadEntryDate
1568170 1 08/15/2005 15-Sep-06
1568170 99 09/06/2005 15-Sep-06
1568170 99 09/07/2005 15-Sep-06
1445110 1 09/21/2005 15-Sep-06
1445111 2 09/21/2005 15-Sep-06
1445110 1 09/22/2005 15-Sep-06
1445111 2 09/23/2005 15-Sep-06

End result of a query (with explanation what the code has to do in Bold)

Column 1: TOTAL CLAIMS: COUNT ALL DISTINCT (UNIQUE) VALUES IN caseno. Result (based on the above table extract): 3

Column 2: TOTAL OPEN CLAIMS: COUNT ALL DISTINCT VALUES IN caseno THAT DO NOT HAVE “99” in after_step Result: 3

Column 3: TOTAL CLOSED CLAIMS: COUNT ALL DISTINCT VALUES IN caseno THAT HAVE “99” in after_step
Means that if I have two 1568170 caseno with the same after_step 99 it needs to be counted only once. Result: 1


HELP!

Thank you.

Lena

[bonzi1405]

Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our system and thinks I am omnipotent now and can extract any data out of it in the form he wants….The truth is, though I know SOME Access, I am not a programmer…and many queries that he wants me to do have a potential of being monstrous towers of queries built upon queries, etc. ( I do not know SQL or VBA)… HELP!

Table Name: 31 Table

Fields:
caseno after_step HistEntryDate HeadEntryDate
1568170 1 08/15/2005 15-Sep-06
1568170 99 09/06/2005 15-Sep-06
1568170 99 09/07/2005 15-Sep-06
1445110 1 09/21/2005 15-Sep-06
1445111 2 09/21/2005 15-Sep-06
1445110 1 09/22/2005 15-Sep-06
1445111 2 09/23/2005 15-Sep-06

End result of a query (with explanation what the code has to do in Bold)

Column 1: TOTAL CLAIMS: COUNT ALL DISTINCT (UNIQUE) VALUES IN caseno. Result (based on the above table extract): 3

Column 2: TOTAL OPEN CLAIMS: COUNT ALL DISTINCT VALUES IN caseno THAT DO NOT HAVE “99” in after_step Result: 3

Column 3: TOTAL CLOSED CLAIMS: COUNT ALL DISTINCT VALUES IN caseno THAT HAVE “99” in after_step
Means that if I have two 1568170 caseno with the same after_step 99 it needs to be counted only once. Result: 1


HELP!

Thank you.

Lena

As per my understanding to your question, below is the script
Try this out.
select count(col1),count(col2),count(col3)
from(
select distinct caseno col1,
( select distinct caseno from 31_Table where after_step <> 99) Col2,
( select distinct caseno from 31_Table where after_step = 99) Col3,
from 31_Table
)