Sorry for the non-descriptive subject line.
If you go to http://www.middletree.net/shape.asp, you'll see that I have a
form to fill out.(Ignore the prefilled names and other data; it's just for
testing) When this is filled out, there will be a row in the master table,
called Personal, and there are also static tables for those 4 checkbox areas
you see, called Area, Gift, Ability, and People. Because users can check
more than one box, I resolved the many-to-many by created a union table
(join? composite? not sure what to call that type of table);
Anyway, there are 4 combo tables, PersonalPeople, PersonalGift,
PersonalAbility, and PersonalArea. As the names imply, each only has 2
fields: the PK of the Personal table, and the PK of one of the other 4
tables.
Now, the question: If you go to http://www.middletree.net/list.asp, you'll
see that it displays a list of all users who have filled out the form.
At the bottom, I have started adding a form that lets you refine the search
and give you a similar list, but one which meets the criteria selected. In
other words, if you select Leadership under the Gifts dropdown, and click
the button (which is not there yet), it should show you all people who
chcked the Leadership checkbox, regardless of what else they selected. But
if you select Leadership from that dropdown, and College from the People
Groups dropdown, then it would give you only those who selected Leadership
and College.
Problem is, I am not sure how to code the SELECT statement to do this.
I guess it has to be a join, but not sure how to do it. I have to leave
open the possibility that someone might leave one or more dropdowns
unselected. I have done joins before, but am drawing a blank on this one.
FWIW, this is using Access 2000.