Only include fields with value 'YES'

[oldHound]

Hi, first post here - may be a simple question, but it's been a bit of a while since I used Access, so my skills are getting rusty...

I have a form with a number of checkboxes writing to Yes/No fields in the underlying table. I then want the data to populate a report via a query. However, in the report, I would like to show only those fields where values equal TRUE/YES. In other words, for each record, only the fields where the user has checked the boxes should be displayed in the report. If possible, this should involve variable height for each record in the report, but this is not a must if too complex. It is, however, imperative not to show fields where the value is FALSE/NO. In other words, if all the fields has to go into each record in the visual layout of the report, is there a way to make the fields with negative values not visible? Any ideas how I can achieve this? I'm not sure if it can be done with just a simple Query, or with the SQL builder, or if it has to be done with VBA scripting (which would perhaps be applied to the report object rather than in the query?).

PS. If you know the answer, but are wondering how much you may need to dumb it down for my benefit, I have used Access a bit in the past, but not professionally. I have never bothered to learn VBA for Access, but have coded in VB and VBA for Excel, though, and know a bit of SQL and Java, which may give you a bit more of an idea what level to pitch it at ).

Thanks for any input!

 

[ThreeCrow]

Well to filter for your Report only the Records with a value of "Yes" in a given Field is very very complex indeed , you might want to sit down , if you are not sitting.

Just funning you , your answer is actually very simple.

Add in the underlying Query of your Report the Field(s) that have the Yes/No values, in the Criteria just under the Yes/No Field(s) put -1 (this is Yes and 0 is No).

This will show only the Records where the Value is Yes.


Hope this Helps

 

[oldHound]

Thanks for the reply!

I didn't really explain my quandry very well, I don't think. I'll try to be a bit clearer...

The thing is, I don't want to filter out any records. I want all records included in the report. However, in the report, I only want to show those fields where the value equals TRUE. I'm making a little thing that accepts bookings of venues, and the fields include a long list of such things as "Microphones?", "Stage lights?", "Tables?", etc. and also "Number of microphones", "Number of tables"...

The person who will use this then wants a report with all the bookings, but for clarity, the fields that haven't been filled out should not be displayed on the report. So if the booking only includes one microphone, and none of the other thirty or so possible items, only microphone should be mentioned on the report.

So far, when experimenting with the criteria, I only end up leaving out records, rather than fields. I'm not quite sure where to go from here...

Thanks!

 

[neileg]

Your problem is down to using the wrong design. You have used a series of fields in the record to hold this data. You should have used a second table to hold this data linked to your main table via the primary key. This second table would only hold a record if the item was needed. Your report would then be very simple to produce.

 

[oldHound]

I think I see what you mean: one table for bookings (date, venue, person placing the booking, etc.), one for resources (microphones, etc.), and then one combination table that links one resource to one booking via their primary keys? E.g. one record in this combination table might contain bookingID 7 and resourceID 5... I think I see how this would solve the problem...

Thanks!