Handling yes/no fields

[DavidS]

How can I auto check a yes/no field when one or more other yes/no fields are checked?

[June7]

This is saving calculated data (data based on other data) and is redundant. Saving calculated data requires code. The value can be calculated whenever needed.

[DavidS]

Thank you June7. I believe I do understand your answer and I have no desire to learn code. That's why I'm using Access. There has got to be an easy way to do what I want to do. Here's what I'm doing.
The way that I decided to tackle our volunteer Fire Departments Disaster Resources Database was to create one very wide table that covers most of the known resources possibly needed. I arrange them in "categories" set up as "yes/no" type fields. The paper input form which mimics the data entry form on the computer (And will probably have a web-based counterpart as well) has check boxes for the "yes/no" options for the various fields. For example category HeavyEquipment is a "yes/no" type field as is Bulldozer, FrontLoader, Backhoe, etc. That the category is also a "yes/no" field allows me to tame the fat table into category report printouts easily. From my perspective everything works great but at an officers meeting folks had a problem, claiming confusion with checking a category box as well as the individual pieces of equipment boxes. So I remove the "category" "yes/no" field from the hardcopy input form and replace it with a text box designating the category name. I suppose the easy fix is to let the data entry person then check the "yes/no" box for category on the computer entry form but it seems like it should be so simple to tell the darn computer to make the category box "yes" when any of the individual resource boxes are checked. Does this make sense? The query that forms the basis for The HeavyEquipment printout only has to return those records with the HeavyEquipment category box checked - so easy. Otherwise I guess I have to build a query that returns this piece of equipment, OR that piece of equipment, OR that other piece of equipment, etc. Some of the categories have a lot of choices.
Anyway, thanks again for any and all help.
DavidS

[June7]

Non-normalized data structure will give you a lot of frustration. One of which is filtering on multiple Yes/No fields. My previous information still pertinent. Saving calculated result requires code, no way around it.

An expression in checkbox ControlSource property can dynamically set to Yes/No. This could be a very long IIf() depending on how many fields must be looked at.

=IIf([Bulldozer] = Yes Or [FrontLoade]r = Yes Or [Backhoe] = Yes, Yes, No)

Do this calc when needed.

[Z1nkstar]

Using VBA is the easiest way I believe that you could get your answer
an If statement would make this possible

saying that this is the statement that I would use
Remember Im not an expert there may be other ways that are better then this but this is my opinion.

If Me.Yes1 = True Then
Me.Yes8 = True
End If
If Me.Yes2 = True Then
Me.Yes7 = True
End If
If Me.Yes3 = True Then
Me.Yes6 = True
End If
If Me.Yes4 = True Then
Me.Yes5 = True
End If
If Me.Yes8 = True Then
Me.Yes3 = True
End If

Make sure when using this its On Click Event and it has to be on each Yes/No.
You can do Multiple If statements to get your desired result make sure to change the Me.Yes(1-8) to your field names Such as Me.Backhoe

[DavidS]

OK, So we've been spending the past month ?perfecting? just what particular resources we want to track, the input form and deciding just what Reports the Incident Commander might want to have already printed out and ready to go in the event of a situation. I'm still balking at the code but thank you Zinkstar, I just might stick my toe in that water at some point.
Two questions:
1. When creating an up-date query to do what I wanted to do above I seem to be limited in the number of Criteria Or rows available so that I can't Or all of the HeavyEquipment possibilities. Is there a way to get more?
2. In the reports the boss doesn't want the unchecked Yes/No fields to show. This might be the filtering problems suggested by June7 but I can't figure a way to not print unchecked fields. Does anybody know how?
Thanks for any help, David

[June7]

1. none that I know of, bad design is root of the issues

2. A control occupies space, it's not going away just because there is no data for a record or has the appearance of no data.
Even if checkbox is not checked, the yes/no field still has data - it's 0. Checked (Yes) is -1. Instead of a checkbox control, use a textbox and set the Format property to Yes/No. This way every record will show something.

[DavidS]

I'm starting to think that maybe "novice" is an exaggeration. Now I am starting to have trouble navigating this site. Might be that I'm running XP and Access on my Mac using Virtual Box. There is no scroll bar and no way to reread the posts in this thread. I would like to talk about a better way to build the database than the way I'm doing it.

Hmm!

David

[June7]

Maybe this site will help you http://www.rogersaccesslibrary.com/