You might want to review the table structure, reports, forms, etc for At Your Survey http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html. You can create whole surveys without adding a field or control.
Duane Hookom MVP
MS Access
----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: JohnV@msn.com
> Date: Fri, 1 Mar 2013 20:42:01 +0100
> Subject: RE: [MS_AccessPros] Calculating an audit score
>
> 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]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar