I had a table with services information for each person. The table has a
member or ID number and several check box fields (yes/no) to show what
services this person has. Then there is a query to show this information by
ranges with the Between [date1] And [date2].
Now I need a query like that by dates, showing only the persons with marked
check boxes
Thank you all, my friends
That design is not the best: storing data - services - in fieldnames
is not properly normalized and will cause problems down the road. What
if you need to add two new services? You'll need to restructure your
Table, and all the Forms, Queries and Reports which reference the
table!
If you have a many (people) to many (services) relationship, consider
instead using THREE tables: People, Services, and ServicesUsed. This
latter table would have the PersonID, ServiceID, and possibly other
fields concerning this person's use of this service.
But to answer your question - if you want to retrieve only those
records for which there is at least one True value for any service,
use OR logic. In the query grid put True under each checkbox, *on a
different line* for each; you'll also need to copy the BETWEEN
criterion onto every one of the lines that you use. The SQL view would
be
.... WHERE tablename.datefield BETWEEN [date1] AND [date2] AND
(Service1 OR Service2 OR Service3 OR Service4 OR Service5)
which will in fact be more efficient than the mess you'll get from the
query grid using my suggestion above!
John W. Vinson[MVP]
