Count Checkboxes 1

[NewCMISGrad]

Example:
Room Complaints Checkbox
Comments Memo
Food Complaints Checkbox
Comments Memo
Nursing Complaint Checkbox
Comments Memo
General Complaints Checkbox
Complaint Dept Lookup box
Comments Memo

I want to run a report that will count: all room complaints and assign that number to Environmental Services, all food complaints and assign that number to dietary, all Nursing complaints to nursing and then all general complaints to the dept specified in the lookup box, for a report. How can I do this?


Thank You so Much,
NewCMISGRad

 

[dhookom]

How about providing some sample records and how you would expect this to display in a report? Your use of "assign that number to Environmental Services" has no meaning (or many meanings) in Access.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[NewCMISGrad]

I hope I can pick up this thread agian.

Sample data:
Survey ID: [highlight]1[/highlight] Name: [highlight]Clinton, Bill[/highlight] Unit: [highlight]CCU[/highlight]
Date of interview: [highlight]10/5/2004[/highlight]
Food Taste [highlight]Satisfied[/highlight]
Food Temp [highlight]satisfied[/highlight]
Food Variety [highlight]Unsatisfied[/highlight]
Food Complaint: [highlight]Checked[/highlight]
Food comment: [highlight]There is not many choices other than beef broth or chicken broth. I know there are more flavors than lime for jello.[/highlight]


Since the food complaint check box is checked, I want to count the checks and place the count and comment in a report under the grouping of dietary services(Which is in a table named Department). The same with room complaints. I would want it to be counted and then show up on the report grouped under Environmental services. Each complaint checked would be grouped on the department that generated the complaint. Is this possible.

Thank You so Much,
NewCMISGRad

 

[GingerR]

Getting the count in a report is easy once the report is created. First you have to get the data in a good format for the report. I am imaging a report grouped by DEPARTMENT which will be a HEADER, with the detail of the report listing the complaint comments. In the HEADER you can put the count of the complaints.

So start with the RecordSource of the report. Create a UNION query that's something like below. The name of my table is "TEST; you'll have to tweak the code with your own table and field names:

SELECT "Dietary Services" AS Department, Test.FoodComplaint as Complaint, Test.FoodComment as Comment FROM Test WHERE (((Test.FoodComplaint)=True)) UNION SELECT "Environmental Services" AS Department, Test.RoomComplaint, Test.RoomComment FROM Test WHERE (((Test.RoomComplaint)=True)) UNION SELECT "Nursing Dept" AS Department, Test.NursingComplaint, Test.NursingComment FROM Test WHERE (((Test.NursingComplaint)=True)) UNION SELECT Test.GeneralDept AS Department, Test.GeneralComplaint, Test.GeneralComment FROM Test WHERE (((Test.GeneralComplaint)=True));

You have to make sure to put each dept name exactly as it is in your table into the code above, i.e spell "Environmental Services" the exact same way it is in your table.

Using this code, my results look like this:

Department Complaint Comment
Dietary Services -1 bad food
Dietary Services -1 bad larry
Dietary Services -1 yuck steve
Environmental Services -1 bad room john
Environmental Services -1 too small
Nursing Dept -1 bad nursing
Nursing Dept -1 larry nursing

Then in the report's Sorting and Grouping, I grouped by DEPARTMENT and made it have a header. Put the field DEPARTMENT in the header and the COMMENT in the detail section of the report. Then in the header, add a text box with this formula:

=count(comment)

and it will count the number of comments.

Issues:
1) you have to have the Deptartment that people put in for a "General Complaint" come from a combo box based on your Department table, otherwise if they type in anything they want, it won't group up.
2) you must make the Complaint Comments be REQUIRED, i.e. if someone has a complaint they MUST fill in the Comment field, otherwise blank comments will be counted on your report but the comment will be blank, which kinda defeats your purpose.

Hope this helps. Let us know how it goes.

 

[dhookom]

Do you have tables for dietary, room, environmental,... ? Is there any way that you could normalize your table structure?

If you can't change your structure, you can count the number of Yes values in Yes/No fields using an expression like:
Sum(Abs([Food Complaint]))
Do you have any idea how you would handle comments from multiple records?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[NewCMISGrad]

That is what I am finding is a problem. Some people may have a complaint and comment about the food and the room. If I just count these and report the counts, I think that would work. But if administration wants to see the comments with the counts, I think I have screwed up. Am I right? [Ponder]

Thank You so Much,
NewCMISGRad

 

[GingerR]

Hi Duane. I curbed my usual normalization pep talk...

Anyhow, NewGrad, are you saying that you only have ONE comment field? I guess I figured you multiple comment fields: "FoodComment", "RoomComment", etc.

Did you try the sql i wrote for you?

If Mary complains about food AND Room and Nursing, they show up three times, each with the correct department. But I guess if you have only ONE comment field, it's over...

The best thing is to normalize it: create a new table with fields:

"SurveyID" integer
"Complaint" Yes/No
"Department" (text, feeds from a Department List)
"Comment"
then use joins to count and report whatever you want.

Short of that, if you can add multiple comment fields to your existing table, that would be second-best. The code i wrote for you would work fine then.

g

 

[dhookom]

I would recommend creating something similar to "At Your Survey" at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You should be able to modify this to fit your needs.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[NewCMISGrad]

I do have a comment field for each section: Room Comment, Food Comment, Nursing Comment, etc. I have tried your code and it does seem to be working. I still need to check the report for accuracy. Thanks to GingerR for your help.

I have quickly looked at Duane's website. I haven't picked it apart yet. Thank you for the reference and I appreciate all the help.


Thank You so Much,
NewCMISGRad