Simpler alternative to multiple criteria/multiple queries

[ballaterach]

Our customers can opt out of receiving any 3 of 30 items with their weekly vegbox. I need a simple way to find where 'Yes' has been recorded against any of those 30 veg. Clearly there are not 30 lines in the query design to enter Yes into and the only alternative I have come up with (excuse my ignorance) is to have 4 seperate queries covering all 30 veg
I'm guessing there must be a simpler way. Thanks in advance!!

[CJ_London]

because the answer is so straightforward for normalised data, from your description I suspect your data is not normalised - i.e. like a spreadsheet

If it is normalised, then you tables would be something like

tblOrders
OrderPK autonumber
CustomerFK long
OrderDate Date

tblOrdered
OrderPK autonumber
Item text (or perhaps a FK to another table to list your veg)
Include yes/no

and your query would be

SELECT *
FROM tblOrders INNER JOIN tblOrdered ON tblOrders.OrderPK=tblOrdered.OrderFK
WHERE Include=yes

But I suspect you have a table like this

OrderPK autonumber
CustomerFK long
OrderDate Date
Lettuce yes/no
Tomatoes yes/no
Potatoes yes/no
etc

If your table is as I suspect then as you say, you will need to have 30 OR's in your query. with regards

Clearly there are not 30 lines in the query design to enter Yes

in the query gui window, there aren't, but you can view the query in sql view and add as many OR's to the criteria as you like, there may be a limit but it is well over 30.

The problem with your way is if you add or remove a veg, you need to modify the table and all queries, forms and reports. The normalised way - no change required.

[ballaterach]

because the answer is so straightforward for normalised data, from your description I suspect your data is not normalised - i.e. like a spreadsheet

If it is normalised, then you tables would be something like

tblOrders
OrderPK autonumber
CustomerFK long
OrderDate Date

tblOrdered
OrderPK autonumber
Item text (or perhaps a FK to another table to list your veg)
Include yes/no

and your query would be

SELECT *
FROM tblOrders INNER JOIN tblOrdered ON tblOrders.OrderPK=tblOrdered.OrderFK
WHERE Include=yes

But I suspect you have a table like this

OrderPK autonumber
CustomerFK long
OrderDate Date
Lettuce yes/no
Tomatoes yes/no
Potatoes yes/no
etc

If your table is as I suspect then as you say, you will need to have 30 OR's in your query. with regards
in the query gui window, there aren't, but you can view the query in sql view and add as many OR's to the criteria as you like, there may be a limit but it is well over 30.

The problem with your way is if you add or remove a veg, you need to modify the table and all queries, forms and reports. The normalised way - no change required.

You're correct Ajax it is NOT normalised; maybe I can redo the database but many thanks for your quick very helpful reply

[CJ_London]

you will soon see the benefits of normalisation