IIf Then Statement for Check Box

[mkuethe]

I have a form that lists 8 audit questions that are scored 1, 0, -1 (Yes, No, N/A) using combo boxes. What I need is a query that will count the "1" scores and mark a check box as true if the result is 3 or greater - excluding the No's and N/A's.

Can anyone assist???

 

[hneal98]

I take this is being counted from a table? How are you grouping? Is this per question?

You have not told enough about what the form is based on. Is it based on an individual taking a survey? Is it a summery of all audit questions?

 

[mkuethe]

Yes, there is a table (tbl_score) that the question scoring combo boxes pull from. We want it scored per audit.

We have auditors that review others' work and answer 8 questions (MI1, MI2, MI3, etc.) Based on the work, the questions are either "1" (Yes) if that indicator was met, "0" (No) if the indicator was not met, and "-1" (N/A) if the question isn't relevant to that particular audit. We will consider a minimum of 3 of the 8 questions answered with "1" as a pass. When then need a check box on the form that will have a label reading "Audit Passed". If the query found a count of 3 or more, the check box will be marked. If the query finds a count of 2 or less, the check box will remain blank.

I have the questions already built into the query, I just need a way to count the "1"s and mark the check box.

Does this help or do you still need more information?

 

[hneal98]

It sounds like your query is based on detail. You need to do the counts in the query. Then put that result in a dataset and based on that summery, you should then be able to check your checkbox based on the value for each question.

Let me know if that does not make sense. Here is an example of the Detail:

MI1 MI2 MI3 MI4 MI5 MI6 MI7 MI8
1 0 1 -1 1 0 0 1
1 1 0 1 0 -1 1 1
0 0 0 0 1 1 0 1

The summery would be like this based on what you wrote above:

1st Audit 3
2nd Audit 4
3rd Audit 3

Is that correct? If so, you would set up your query to give the summery and then populate your check box based on the values.

 

[hneal98]

Actually, my bad... You wanted counts, not sums. The summery would actually look like this:

Yes NO N/A
1st Audit 4 3 1
2nd Audit 5 2 1
3rd Audit 3 5 0

 

[mkuethe]

Yes, that's what I need. Can you walk me through setting up the summary and the check box command?

 

[hneal98]

Post the query you have so far.

 

[mkuethe]

SELECT Tbl_Main.ContactID, Tbl_Main.[Consultant Name], Tbl_Main.[Company Name], Tbl_Main.[Date of Service], Tbl_Main.[Date Audit Completed], Tbl_Main.Auditor, Tbl_Main.CFMI1, Tbl_Main.CFMI2, Tbl_Main.CFMI3, Tbl_Main.CFMI4, Tbl_Main.CFMI5, Tbl_Main.CFMI6, Tbl_Main.CFMI7, Tbl_Main.CFMI8
FROM Tbl_Main INNER JOIN tbl_Consultants ON Tbl_Main.[Consultant Name] = tbl_Consultants.Consultants;

 

[hneal98]

You need to do 8 crosstab queries and one final query that adds them all up.

In your cross tab queries you will have as the row, Contractid, Company name, consultant name, Date of Service, Date Audit completed, Main Auditor.

For the Column, you will put in the first query, Tbl_Main.CFMI1. And for the count, you will also put Tbl_Main.CFMI1.

For the next query, you will set it up the same way, but for the column and count, instead of CFMI1, you will put CFMI2.

You will do this same thing for all 8 queries and then for the final query, you will create a query that has all the info in the 2nd paragraph above and then add all 8 queries to this one and total up the counts fields from each of the crosstabs.

Let me know if that does not make sense to you.