check box comparison 1

[evr72]

Hello,

I have been asked to build a form, each answer is a yes or no answer and have been asked to have a checkbox for each answer. I have around 81 questions.
I have been given a "rules template" This is the way it works, for example if checkbox number 1 is checked and checkbox number 4 is checked then there is a conflict.

What I am trying to do is to build a form for the users so they can answer the questions. I have this no problem.

Then another page for the admin so he/she can see the answers of all the users, I can also build this.

I plan to have a table with questionid, question

Then a users table with username, userid, email

And then a table for the answers user, questionid, answer, conflictfield

My question would be

for my "conflictfield" would I have to do something like this for the value

if answers.question1=yes and answers.question2=yes then response.write("conflict") end if

Thanks!!

 

[foxbox]

i presume a "conflict" means that the user has entered wrong answers. If each question has only 1 possible conflicting answer then that information could be stored in the question table (not the answers).
More likely it is n:m
Then you need an extra table "Conflicts", which makes it more flexible too, if your fields are (ie)
- QustionId 1
- Answer 1
- Questionid2
- Answer 2

(You may ommit the anwer fields if all your questions are Y/N = checked/uncheck. But what if a conflict is defined as a Yes to question 1 and a No to question 4?)

 

[evr72]

foxbox,

thanks for the advice.
As I was explained each question might have more than one conflicting answer for example
these are questions 1 - 4

1 Initiate Checks for Expenditures
2 Prepare Check
3 Review, Authorize or Sign Checks
4 Mail Check

if the user checks question 1 and 3 then he/she will get the admin report will show

"Review, Authorize or Sign Checks conflicts with Initiate Checks for Expenditures. Checks should be signed by someone who did not initiate or prepare the check, in order to minimize the potential for concealment of fraud."

if the user were to pick 1 and 3 and 4 then the admin report would show the above conflict plus

"Mail Check conflicts with Initiate Checks for Expenditures. Signed checks should be delivered to someone independent of both the preparer and the initiator of the check for prompt mailing, in order to minimize the potential for concealment of fraud."

and also for 3 and 4

Review, Authorize or Sign Checks conflicts with Mail Check. Signed checks should be delivered to someone independent of both the preparer and the initiator of the check for prompt mailing, in order to minimize the potential for concealment of fraud

does this make sence? because honestly I am a bit confused

 

[guitarzan]

I realize that I am not directly answering your question, but...

Is it really necessary to use Checkboxes for all choices? Wouldn't it be better, where possible, to use Radio buttons to prevent the user from even selecting conflicting information in the first place?

At any rate, foxbox is pointed you in the right direction, with a separate Conflicts table that would need to be queried

 

[foxbox]

oke, so the "conflicts" are a little bit more complex then "if question 1 and 4 checked then conflict".

The problem is to design a table structure. With your new info i would probably end up with:

Table Conflicts
ConflictId-Description
1- "Review, Authorize or Sign Checks conflicts with Initiate Checks for Expenditures. Checks ...
2- Mail Check conflicts with Initiate Checks for Expenditures..
3 - "Review, Authorize or Sign Checks conflicts with Mail Check..."
4 - etc


Table ConflictAnswers
ConflictID- AnswerID
1 1
1 3
2 1
2 3
2 4
3 3
3 4

In that last table we check if there is a conflict
select distinct conflictid where answerid in ([checked answers])

eg if the [checked answers] are 3 and 4 then that query will produce conflictid = 3

 

[evr72]

guitarzan,
I thought about using radio buttons, but the auditor would like to see the responsabilities of each individual then make recommenadations depending on the conflicts, hence cannot use radio buttons.

foxbox,

I get what you say, makes sence, so I came up with the "conflictanswers" table.

it has the following fields

questionid1
questionid2
conflictnum
conflictdesc

Then in my answers table I have
questionid
questiondescription
dutydescription
answer
userid
username
conflict

Then in my questions table I have
questionid
questiondescription
dutydescription


so for example user1 answers would look like this

questionid questiondescription answer userid username

1 thisisquestion1 yes 01 user1
2 thisisquestion1 no 01 user1
3 thisisquestion1 yes 01 user1
4 thisisquestion1 yes 01 user1
5 thisisquestion1 no 01 user1

then on my conflictanswers table
questionid questionid2 conflictnum conflictdesc
1 3 1 conflictdescription
3 1 2 conflictdescription
4 1 3 conflictdescription
4 3 4 conflictdescription


I am not sure how I would build the asp code though

if rsanswers(answers)=yes and rsansers(questionid) = rsconflictanswers(answerid1) then response.write("conflict")

 

[evr72]

all right here is what I tried.

I created a query as Foxbox suggested and that worked well, here is what I used

SELECT DISTINCT answers.answer, answers_1.answer, answers.itm, answers_1.itm, Database.Duty1, Database.Duty2, Database.conflictnum, Database.[Conflict Description], IIf(answers.itm=Database.Duty1 And answers_1.itm=Database.Duty2,"Database.conflictnum") AS conflictanswer
FROM answers AS answers_1 INNER JOIN (answers INNER JOIN [Database] ON answers.itm = Database.Duty1) ON answers_1.itm = Database.Duty2
WHERE (((answers.answer)<>"0") AND ((answers_1.answer)<>"0"));