Query counts the blank field value

[aman]

Hi Guys

The following query works fine but if the reason is blank then it also shows a record with blank reason and count=0

I don't want the blank reason to be displayed in the query.

SELECT tbl_RMS_Paperless.[1stRefuseReason], Count(tbl_RMS_Paperless.[1stRefuseReason]) AS Times
FROM tbl_RMS_Paperless
GROUP BY tbl_RMS_Paperless.[1stRefuseReason];

Any help will be much appreciated.

Thanks

 

[Ranman256]

Make Q1 to pull all data NO BLANK.
then make Q2 to count Q1.

 

[Minty]

Add a Where to exclude the blanks;

SELECT tbl_RMS_Paperless.[1stRefuseReason], Count(tbl_RMS_Paperless.[1stRefuseReason]) AS Times
FROM tbl_RMS_Paperless
WHERE 1stRefuseReason is not null AND 1stRefuseReason NOT = ""  
GROUP BY tbl_RMS_Paperless.[1stRefuseReason];

 

[aman]

Thanks Minty, Another question is I have another field named "2ndRefuseReason" which also stores reasons. Is there anyway I can combine the "1stRefuseReason" and "2ndRefuseReason" so that answer of the query will be something like below where Reasons A,B,C,D come from two fields "1stRefuseReason" and "2ndRefuseReason" but the reasons must not be duplicate.

Reason Times (Count)
A 10
B 20
C 30
D 15

 

[Minty]

Ouch - this is a poor data storage method, which is why you know are struggling to combine the data that should be in one table vertically.

You should have a separate reasons table- then you can have as many reasons as you like per top level entity.

Investigate a union query.

 

[aman]

My first query is giving me following result:
1stRefuseReason Times
In dispute with the score / grade 9
Sent in error 9
Disagree with the content 20
Missing Content 11
The query is

SELECT tbl_RMS_Paperless.[1stRefuseReason], Count(tbl_RMS_Paperless.[1stRefuseReason]) AS Times
FROM tbl_RMS_Paperless
GROUP BY tbl_RMS_Paperless.[1stRefuseReason]
HAVING (((tbl_RMS_Paperless.[1stRefuseReason]) Is Not Null));

My 2nd query is giving me following result:
qry_RMS_ReturnReasons_2
2ndRefuseReason Times
In dispute with the score / grade 1
Sent in error 1
Disagree with the content 2
Refuse to sign 18

The query is

SELECT tbl_RMS_Paperless.[2ndRefuseReason], Count(tbl_RMS_Paperless.[2ndRefuseReason]) AS Times
FROM tbl_RMS_Paperless
GROUP BY tbl_RMS_Paperless.[2ndRefuseReason]
HAVING (((tbl_RMS_Paperless.[2ndRefuseReason]) Is Not Null));

But I want the following result:

In dispute with the score / grade 10
Sent in error 10
Disagree with the content 22
Missing Content 11
Refuse to sign 18

Also I have lookup table that actually stores all reasons.
tblReasonNotSign
ID Reason
1 Disagree with the content
2 Missing Content
3 In dispute with the score / grade
4 Sent in error
5 Refuse to sign

 

[Minty]

Yup - I understand that - so if you union those 2 queries , save the union then group and add up the results you will have the desired result, untested but the first stage would be

SELECT * from Query1
Union
SELECT * from Query2