John-
This is a bit simpler:
NumberOfYes: Abs(([Q3CPSharedWithSettingIn1Day]="Yes") +
([Q3bCPSharedForPlnd]="Yes") + ([Q4PCPNotificationIn2Days]="Yes") +
([Q5ContactIn5Days]="Yes") + ([Q6ConsistentPersonIn5Days]="Yes") +
([Q7ChgsCommunicatedIn2Days]="Yes") + ([Q8MedReview]="Yes"))
Basically, ([Q3CPSharedWithSettingIn1Day]="Yes") will return "True" (-1) or
"False" (0). Adding the results will get you a negative count of the "Yes"
answers, and using Abs returns the postive integer.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jfakes.rm
Sent: Thursday, February 28, 2013 3:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Calculating an audit score
Ok, I have a user that wants to calculate the results of an audit. The user
brings up a form, and selects yes, no, or na on numerous questions. I ended
up putting the following in a query:
NumberOfYes:
(Count(IIf([Q3CPSharedWithSettingIn1Day]="Yes",0))+Count(IIf([Q3bCPSharedFor
Plnd]="Yes",0))+Count(IIf([Q4PCPNotificationIn2Days]="Yes",0))+Count(IIf([Q5
ContactIn5Days]="Yes",0))+Count(IIf([Q6ConsistentPersonIn5Days]="Yes",0))+Co
unt(IIf([Q7ChgsCommunicatedIn2Days]="Yes",0))+Count(IIf([Q8MedReview]="Yes",
0)))
I also have the same code but for number of Nos.
NumberOfNo:
(Count(IIf([Q3CPSharedWithSettingIn1Day]="No",0))+Count(IIf([Q3bCPSharedForP
lnd]="No",0))+Count(IIf([Q4PCPNotificationIn2Days]="No",0))+Count(IIf([Q5Con
tactIn5Days]="No",0))+Count(IIf([Q6ConsistentPersonIn5Days]="No",0))+Count(I
If([Q7ChgsCommunicatedIn2Days]="No",0))+Count(IIf([Q8MedReview]="No",0)))
Basically, the query looks at each field and counts if its a yes or no, then
I add the NumberOfYes and the NumberOfNo then divide NumberOfYes to get the
score. My question is, I'm sure there has to be a more elegant way of doing
this, plus, if a new question is ever added, the code would have to be
updated. Is there a better way to search all the questions and count the
yeses and no's?
John F
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar