Create String for Report

[Dave177]

I have created a form for inputing site inspection reports. One tab of the form is for positive comments and the other is for negative comments. The underlying table tblReports has fields such as [PFood] for a positive comment about food and [NFood] for a negative comment about food. There are about 40 other fields including [PSanitation], [NSanitation], [PStaff] and [NStaff] etc. The idea is that the person reviewing a report will tick the appropriate check boxes for an aspect of a particular site. (Some aspects appear in the negative list but not in the positive list, and viceversa).
The user ticks however many checkboxes is necessary. I am trying to create a report that lists the issues (positive and negative) on a site by site basis.
In order to do this I believe a need to create a string (quite new to Access) so thought I should convert -1 (ticked check boxes) to text. To do this I created a query and an IIF Statement which says for example:

 NTFood: IIF(([NFood]) like "-1","Food","")

I have done this for quite a few of the fields with the aim of joining them all up for report and then creating a list of issues for each site. However I can't believe that there isn't an easier way as it means that my query will have about 80 columns in it. Does anyone have any ideas/suggestions?
Thanks for any help.

Dave

 

[Spenney]

Would it not be easier if the underlying table had a single field for each type of 'measure'? IE Instead of a field for positive food comments, and another for negative, why not simply have a field called 'Food' with the ability to record a value to identify whether the comments were positive of negative ?

I think this would make any subsequent reporting much more straightforward than mucking around converting -1's and 0's to text values.

 

[Dave177]

Spenney,
Thanks for the reply.
I agree but sometimes there are both negative and positive comments about food and both of these need to be recorded (odd but necessary).
Dave