John, that is cleaner.
However, what I'm trying to figure out, is there a way that I can search all the questions and add the yesses the nos, and the na's in one swoop? Plus, is there a way to do this that would capture new questions or deleted ones without having to change the code?
I'm was thinking something like a DCount, however, that counts all the records and adds them together while I need a separate count for each record.
Thanks,
John F
--- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
>
> 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 (3) |
Tidak ada komentar:
Posting Komentar