A question about Checkbox Queries. 1

[Shoag]

Hi
I have a table for volunteers with the days they are available on checked. I want to create a query that would allow the user to enter the day and the query will give the names and contact information of volunteers that are available in that particular day. I attached a screenshot of my table. I hope you could help me.

 

[MajP]

Your data is not normalized. The more correct design would be a volunteer table and then a second table for days available, thus creating a 1 to many

so this table would look like

VolunteerID DayAvailable
1 Monday
3 Monday
3 Tuesday
4 Wednesday
4 Thursday

Then making a query from the proper design is trivial.

Now you are stuck with a simple but long normalizing query using a Union query. This will take you data and normalize it into the form above.

Select
Volunteer_ID,
Select Monday,
Select "Monday" as DayAvailable
From
Volunteer
Where
Monday = True
Union
Select
Volunteer_ID,
Select Tuesday,
Select "Tuesday" as AvailableDay
From Volunteer
Where
Tuesday = True
.......

 

[Shoag]

Hi
Thanks for your reply. I was wondering if I need to create 3 tables to better normalize my table. The three tables are VOLUNTEERS, DAYS, and AVAILABILITY. What do you think of this structure? How do I link them?

 

[MajP]

Based on what you have shown so far, not sure what you would do with a days table. Unless it is simply a lookup table for a combobox so that you could stick the day value into the person availability table.

In my suggestion you could have main form with a subform to select which day of the week the volunteer is available. The link is on volunteerID.