calculate sums in columns 1

[pduncan]

I have a query that queries a couple of tables to create 3 columns. It represents answers to questions (yes/no). The columns are 1.)Question Number 2.)Yes's 3.)No's
My question - I know how to count the total number of rows in the table, but how can I count individual Yes's or No's?
For example, I intend to use these as sub portions of a report that will eventually show something like this:


TOTAL YES NO
Ques 1 120 100 20
Ques 2 120 60 60
Ques 3 120 40 80
... etc

PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

In query design mode, set the query to GROUP. Then, in the yes/no field, set the criteria to yes. Insert the yes/no field and set the critera to no. For both, set it to COUNT instead of GROUP ON.

Jim DeGeorge

 

[pduncan]

thanks for answering - I don't understand something - How do I set a query to GROUP? I went through the help file, but can't find anything
Thanks


PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

Open a query in design mode. Add your table. Select the fields that you want in your query. Click the button on your tool bar that looks like a large backwards "E". That'll turn on GROUPING. Then, do as I said previously.

Jim DeGeorge

 

[pduncan]

OK Jim - I am really not an idiot - and I thought I knew Access fairly well, but I just don't understand what you are telling me to do. When I press the Totals button, I don't see a yes/no field - I do have "total" i.e. group by, "Sort", "Show", and "Criteria".
And I dont understand what you mean by insert the yes/no.

When I set it to count instead of group on, I get a type mismatch error. I assume because I am trying to count "Yes" and "No" instead of numbers.

Here is the SQL I used in my query:

SELECT dbo_tblAssessmentControl.ControlRank, IIf([dbo_tblAssessmentControl]![BooleanValue]=1,"Yes","&quot AS PermissionDescri1, IIf([dbo_tblAssessmentControl]![BooleanValue]=1,"","No&quot AS PermissionDescri2
FROM dbo_tblAssessment INNER JOIN dbo_tblAssessmentControl ON dbo_tblAssessment.AssessmentID = dbo_tblAssessmentControl.AssessmentID
WHERE (((dbo_tblAssessmentControl.ControlRank)=11) AND ((dbo_tblAssessment.AssessmentName) Like "Terminix*&quot AND ((dbo_tblAssessmentControl.Status)="complete&quot);

Thanks for trying to help me


PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

That's a whopper of a SQL statment.

What are the fields that you want in your query? Of those, which would give you your TOTAL and which would you count for YES and NO?

Jim DeGeorge

 

[pduncan]

I have 3 columns -
the first is "controlrank" - this is the question number. I am using a different query for each question, so every field in this column is a "1" for this query. I was using this as a means to determine the total number of responses to question 1


The second column represents the Yes answers I am using this to populate the fields -
YES: IIf([dbo_tblAssessmentControl]![BooleanValue]=1,"Yes","&quot

So this column has a bunch of Yes's and blanks

The third column is for No answers - I use this to populate the fields -
NO: IIf([dbo_tblAssessmentControl]![BooleanValue]=1,"NO","&quot

so this column has a bunch of no's and blanks
example
CONTROLRANK YES NO
----------------------------------------------
1 yes
1 no
1 yes
1 no

what I am tring to get to is to be able to say:
Question 1 : Yes=2 No=2
It is just the portion above that I was going to use as a sub report. I was thinking my main report would have a sub like the one above for each question.

PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

One more question before I try to figure this one out. If controlrank is your question number, why is it always "1" in your example?

Is your query's output more like this right now?

[tt]
CONTROLRANK YES NO
1 yes
2 yes
3 no
3 yes
3 no
4 yes
4 yes
5 no
6 no

and your desired result is:

CONTROLRANK YES NO
1 1
2 1
3 1 2
4 2
5 1
6 1

If so, let me know.

Jim DeGeorge

 

[pduncan]

Jim -
the querry is like the example I prvided in my last response. I have also tried it where I place 1's and 0's instead of Yes and No
Like this
CONTROLRANK YES NO
----------------------------------------------
1 1 0
1 0 1
1 1 0
1 0 1

I am using a seperate query for each question


PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

Is "booleanvalue" the name of the yes/no field in tblAssessmentControl?

Jim DeGeorge

 

[pduncan]

yes - if booleanvalue = 1 then it's a yes.
If booleanvalue = 0 then it is a no.
If booleanvalue is null it's an N/A.

PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

pduncan

Okay. I created a table (tblAssessmentControl) with fields ControlRank, BooleanValue.

I then created a query with this SQL:

[tt]SELECT tblAssessmentControl.controlrank AS Question, Sum(IIf([tblAssessmentControl]![BooleanValue]=0,1,0)) AS Yes, Sum(IIf([tblAssessmentControl]![BooleanValue]=-1,1,0)) AS [No]
FROM tblAssessmentControl
GROUP BY tblAssessmentControl.controlrank;[/tt]

The table had these values:

[tt]
ControlRank BooleanValue
controlrank booleanvalue
1 No
1 Yes
1 Yes
1 No
1 No
1 No
1 Yes
4 Yes
2 No
2 Yes
3 No
3 No
3 Yes
4 No
4 Yes[/tt]

The booleanvalue for YES = 0, for NO is -1.

The result is this:

[tt]
Question Yes No
1 4 3
2 1 1
3 2 1
4 1 2

Does this help?

Jim DeGeorge

 

[jdegeorge]

Hit SUBMIT too soon.

This way, you can count everything in just one query instead of having a query for each question. It's concise and accurate.

I didn't bother to create your other tabel for the WHERE condition, but that should fit in nicely.

I noticed that some of your data sources started with "dbo" so I'm assuming that they were the individual question queries. You shouldn't need them now.

Jim DeGeorge

 

[pduncan]

Jim -
thanks - this does make more sense this way, but the numbers still arenet adding up correctly. Can I send you a copy of my tblAssessmentControl table? I really appreciate all the time you are spending trying to help me.. I have 3 managers breathing down my back waiting on this report...

PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

Send it to:

jim.degeorge@fmr.com

I'll take a look at it.

Jim DeGeorge

 

[pduncan]

Jim - it's on the way - the zip file is 3.75 MB unziped it is 25 MB - I just included the 1 table.
Thanks

PDUNCAN - MEMPHIS, TN

"I'm living so far beyond my income that we may almost be said to be living apart."
- e e cummings

 

[jdegeorge]

I'll take a look at it.

Jim DeGeorge

 

[jdegeorge]

Patrick

I created the same query in your database and it ran just fine. Create a blank query and insert this as your SQL:

SELECT tblAssessmentControl.controlrank AS Question, Sum(IIf([tblAssessmentControl]![BooleanValue]=0,1,0)) AS Yes, Sum(IIf([tblAssessmentControl]![BooleanValue]=-1,1,0)) AS [No]
FROM tblAssessmentControl
GROUP BY tblAssessmentControl.controlrank;

Now, I have no way of knowing if the totals are correct, but it's doing what I told it to do.

What do you mean by "the numbers aren't adding up correctly"?

Jim DeGeorge

 

[pduncan]

When I run the query, nothing shows up in the No column.
I think it is because of this:

if booleanvalue = 1 then it's a yes.
If booleanvalue = 0 then it is a no.
If booleanvalue is null it's an N/A.

Your query is set for the booleanvalue for YES = 0, for NO is -1.

also - take question 5 for example: it counts the Yes's as having 10. But if I run a query like the one below, I get 10 no's and 366 Yes's.

SELECT tblAssessmentControl.BooleanValue, tblAssessmentControl.ControlRank
FROM tblAssessmentControl
WHERE (((tblAssessmentControl.BooleanValue) Is Not Null) AND ((tblAssessmentControl.ControlRank)=5));



PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[jdegeorge]

Okay. I changed my query to:

SELECT tblAssessmentControl.ControlRank AS Question, Sum(IIf([tblAssessmentControl]![BooleanValue]=1,1,0)) AS Yes, Sum(IIf([tblAssessmentControl]![BooleanValue]=0,1,0)) AS [No]
FROM tblAssessmentControl
GROUP BY tblAssessmentControl.ControlRank;

For Question# 5 there are 366 YES and 10 NO.

Hope this works. And, thanks for the star!

Jim DeGeorge