Counting Yes and No for beginners

[Monty]

I am a self taught access user and now struggling with expressions. I have an access form which is filled in with a Yes/No/Don't know response to 4 questions. On the same form a 1,2,3,4 response is given to 8 standards. On the form I need to sum the no. of Yesses and the 1-4 answers. This is then displayed on the form. I can total the 1-4 answers but cannot work out an expression to sum the number of yesses.The y/n/dk is a combo box with the source a table. Hope someone can help

 

[Geoff Codd]

Here is some sample sql which i use to count the number of jobs per survey (Number of times the surveyors name appears in the orders table), you should be able to play with this to meet your needs

SELECT [GCH Order].[Surv Full Name], Count([GCH Order].[Surv Full Name]) AS [CountOfSurv Full Name]
FROM [GCH Order]
GROUP BY [GCH Order].[Surv Full Name];

 

[Rich]

Sum(Iif([MyField]=True,1,0))

 

[Monty]

Thanks Geoff and Rich

The first of has not worked probably due to my inexperience. The second suggestion gets the response True or 7 when I change the format to general number. This should be impossible as I only want the text box to look for Yes in 4 fields and count 1 for each. I will keep on trying but any further suggestions welcome.

 

[Rich]

Sorry thought it was a check box,
Sum(Iif([MyField]="Yes",1,0))

 

[Monty]

I have tried the suggested

Sum(Iif([MyField]="Yes",1,0))

This returns all the yes responses on the table rather than just those for 1 form (or row from the table). This may work out ok but is it possible to sum for the entries on 1 row?

Another problem: I have tried the following to sum from multiple fields to my text box but have got it wrong somewhere. Any more ideas?

=Sum(IIf([Meal Recieved]="Yes",(IIf([Snacks recieved]="Yes",1,0))))

Thank you for any further help.

 

[David R]

Sum the four fields separately, then add them in your query?

It's not pretty but it works:

SELECT Table1.PK, DCount("*","Table1","[Field1] = " & 1 & " AND PK = " & [Table1].[PK]) AS F1, DCount("*","Table1","[Field2] = " & 1 & " AND PK = " & [Table1].[PK]) AS F2, DCount("*","Table1","[Field3] = " & 1 & " AND PK = " & [Table1].[PK]) AS F3, DCount("*","Table1","[Field4] = " & 1 & " AND PK = " & [Table1].[PK]) AS F4, CInt([F1])+CInt([f2])+CInt([f3])+CInt([f4]) AS Summation
FROM Table1;

However, you may want to look into a different way of structuring your data. Try this topic for starters on how to properly structure a questionnaire database (I never can spell that (*^*^$^$@%^ word): http://www.access-programmers.co.uk/ubb/Forum7/HTML/002866.html


HTH,
David R

 

[Monty]

I have tried the following based on David R advice.

SELECT Better Hospital Food.ID No, DCount("Yes","Better Hospital Food","[Meal Recieved] = " & 1 & " AND ID No = " & [Better Hospital Food].[ID No]) AS F1, DCount("Yes","Better Hospital Food","[Snacks Recieved] = " & 1 & " AND ID No = " & [Better Hospital Food].[ID No]) AS F2, DCount("Yes","Better Hospital Food","[Correct meal being made] = " & 1 & " AND ID No = " & [Better Hospital Food].[ID No]) AS F3, DCount("Yes","Better Hospital Food","[Alternative offered] = " & 1 & " AND ID No = " & [Better Hospital Food].[ID No]) AS F4, CInt([Meal Recieved])+CInt([Snacks Recieved])+CInt([Correct meal being made])+CInt([Alternative offered]) AS Summation
FROM Better Hospital Food;

Better Hospital Food is the table name and the fields are , Snacks recieved, meals recieved, Correct Meal being made and alternative offerred. The Primary Key is ID No

This leads to an error in the syntax which I cannot find. I know what you're thinking Hospital Food cannot possibly be better an this will never work.

 

[Steven McEwan]

Monty,

I think you need square brackets at the beginning:

SELECT [Better Hospital Food].[ID No] ...

instead of

SELECT Better Hospital Food.ID No ...


Steve