Calculation/point system

[Mary10k]

Hello,
I have 3 fields in a table (field 1, field 2, field 3) which are all combo boxes with a value of yes, no & N/A.
I need to assign 1 point to each field if they are a "yes".

I would like to display the sum of all points for each row within a form. Where and how would I query for a "yes" and how would I assign 1 point to each row?

Thank you.

 

[mik18]

You can do this a couple of different ways. The first that comes to mind is to use IIF statements.

SELECT IIF([field 1]="yes",1,0)As One, IIF([field 2]="yes",1,0)As Two, IIF([field 3]="yes",1,0) As Three, One+Two+Three As Total FROM [i]tablename[/i];

mike

 

[PHV]

SELECT Abs(([field 1]="yes")+([field 2]="yes")+([field 3]="yes")) As Points

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Mary10k]

Hello,
I actually changed the criteria of the fields. They are text boxes and when I type the following syntax it acts asks for the parameter. I would still like to search through the data and find the values I am looking for and count 1 point if the value is true. Is this possible to loop through the table wihout creating a parameter box?

SELECT EMPI AS Expr1, IIf(Visits="0",1,0) AS one, IIf(smkStatus_PR="SMOKING",1,0) AS two, one+two AS Total;

 

[MichaelRed]

Recent versions of Ms. A, have a 'tripple state' check box (Yes; No; Null?) which would seem like a beter choice for the control / field for the exercise. If this were used, there is a "basRowSum" amidst these (Tek-Tips) fora which would seem to work nicely to return the total.





MichaelRed