John-
Regarding your question about adding questions - you have a fundamental
table design problem. These questions and responses should be in another
table with one row per question response. It would be easy then to sum or
count responses per question. What you have in the current table is a
repeating group of questions and answers. There's no way to add a question
without affecting the table design and all your code.
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: Friday, March 01, 2013 3:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Calculating an audit score
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]
>
[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 (4) |
Tidak ada komentar:
Posting Komentar