By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,882 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,882 IT Pros & Developers. It's quick & easy.

Help creating a query to count Unique (Distinct) Values

newnewbie
P: 54
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
May 17 '07 #1
Share this Question
Share on Google+
1 Reply


P: 5
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
)
May 21 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.