Multiple Yes/No fields + IIF

[Opal]

I am working in Access 2003 and trying to figure out a query from
my table that contains multiple Yes/No fields.

The user can select Yes or No for up to five shop names. I am
trying to run one query with the following IIF statement in the
criteria row of the query for each field name instead of 5 queries
and 5 reports running off each query:

IIf([Forms]![frmSelectShop]![cboShopName]=1,Yes,No)

The User would select the shop from a dialog form that would
run the report based on the query

Now, each record could have more than one Shop selected
as "Yes" or "No". If I want to run to see the number of
records for shop 1, I need to see only those records where
shop 1 was selected, but the records could also have shop
2, 3, 4 or 5 selected (or not). I need to know what to put
in the criteria row alongside the above noted IIf statement
to produce the desired results or if I should be looking at
this from another angle?

 

[Allen Browne]

That kind of query is not going to work out.

For an explanation of how to set up a related table to hold the details of
which shops apply to which customers(?), see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[Opal]

That kind of query is not going to work out.

For an explanation of how to set up a related table to hold the details of
which shops apply to which customers(?), see:
    Don't use Yes/No fields to store preferences
at:
   http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I am working in Access 2003 and trying to figure out a query from
my table that contains multiple Yes/No fields.

The user can select Yes or No for up to five shop names.  I am
trying to run one query with the following IIF statement  in the
criteria row of the query for each field name instead of 5 queries
and 5 reports running off each query:

IIf([Forms]![frmSelectShop]![cboShopName]=1,Yes,No)

The User would select the shop from a dialog form that would
run the report based on the query

Now, each record could have more than one Shop selected
as "Yes" or "No".   If I want to run to see the number of
records for shop 1, I need to see only those records where
shop 1 was selected, but the records could also have shop
2, 3, 4 or 5 selected (or not).  I need to know what to put
in the criteria row alongside the above noted IIf statement
to produce the desired results or if I should be looking at
this from another angle?- Hide quoted text -

- Show quoted text -

Thanks, Allen. As always, you offer the best advice.