New Query, Old User

[tom.hansen]

I have just been asked to submit a report for which I will have to use a database that I have not worked with in over a year. My skills are very rusty. I need to query a list of policies that underwent revision during the time I was working with them. The Table has a policy # field, and five additional "yes/no" fields for five different time periods. I've been trying unsuccessfully to create a query that will list the policies that have a check mark indicating they were revised during any of the five time periods. I already know how to do this the wrong way, creating five queries, one for each time period, but am interested in learning the correct way to accomplish this. Any input would be greatly appreciated.

Thanks,

Tom

 

[dcx693]

Here's an idea. Just use one query and create a calculated field in that query. If your five revision yes/no fields have a check in any of them, then the sum of those five fields will not be equal to 0. Access stores "TRUE" internally as -1, so the sum would be less than 0.

Let's assume they're called [yesno1]....[yesno5], use this type of expression in your criteria:
[yesno1]+[yesno2]+[yesno3]+[yesno4]+[yesno5]<0

That critieria can be under any field in the table.

 

[tom.hansen]

Perfect. Thank you so much for your time and input. TH

 

[dcx693]

No problem. This solution relies on a little mathematical trick. I suppose the "proper" way to do it would be using an expression like this:
[yesno1] Or [yesno2] Or [yesno3] Or [yesno4] Or [yesno5]

 

[tom.hansen]

In the second equation, do you still use the <1 or would you use Is "Yes"?

 

[dcx693]

You actually don't need to use anything. The equation itself (though I haven't tried it myself) should evaluate to True or False for each record. I guess you could write:
([yesno1] Or [yesno2] Or [yesno3] Or [yesno4] Or [yesno5])=True
but it's redundant.