Queries incorporating check box

[Pigeon Pair]

I have created a database using check box fields to capture the days of the
week volunteers are available.

I wish to run a query that will provide only CHECKED check boxes indicating
volunteer availability - how can I achieve this result?

 

[Allen Browne]

If one volunteer may be available on many weekdays, this suggests a
one-to-many relation between volunteers and weekdays. You would model this
not with yes/no fields in the volunteers table, but with a related table
where you can record the days for each person.

Let's assume your volunteer table has an autonumber field named VolunteerID
as primary key. You need another table with fields like this:
VolunteerID number tells which person this is
DayOfWeek number a value between 1 (sun) and 7 (sat.)

now if volunteer 32 is available on Mon, Wed, or Fri, they would appear 3
times in this table, like this:
32 2
32 4
32 6
This makes it really easy to report the days they are availalble. Ultimately
you can determine who is available for specific dates by using the Weekday()
function with a subquery. It will send up something like this:
WHERE Weekday([WorkDate]) IN
(SELECT DayOfWeek
FROM Table2
WHERE Table2.VolunteerID = Table1.VolunteerID)
More info on subqueries:
http://allenbrowne.com/subquery-01.html

If you really don't want to do it the way it should be done in a relational
database, there is an alternative by summing the values as powers of 2.
Assuming yes/no fields named Sun, Mon, Tue, Wed, etc, the expression would
be like this:
IIf([Sun], 1, 0) + IIf([Mon], 2, 0) + IIf([Tue], 4, 0) + IIf([Wed], 8,
0) + ...
You can then use a logical AND operator to determine whether a particular
day applies.

But that's not the right design. For more info, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

 

[John W. Vinson]

I have created a database using check box fields to capture the days of the
week volunteers are available.

Well, as you're finding, that isn't a properly normalized database design.

I wish to run a query that will provide only CHECKED check boxes indicating
volunteer availability - how can I achieve this result?

A better table structure would be a one to many relationship from a table of
volunteers to a table of days available. With your current design you'll need
to use a fairly complicated UNION query. You don't actually describe your
table, so I may be misunderstanding, but if you have a table with a
volunteerID and seven yes/no fields Sunday, Monday, Tuesday etc. you could
use:

SELECT VolunteerID, "Monday" As Available
FROM yourtable
WHERE [Monday]
UNION ALL
SELECT VolunteerID, "Tuesday" As Available
FROM yourtable
WHERE [Tuesday]
UNION ALL
SELECT VolunteerID, "Wednesday" As Available
FROM yourtable
WHERE [Wednesday]
UNION ALL
<etc>

If that's not your table structure please explain.