programming advice requested

[middletree]

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.

[Chris Hohmann]

Re: programming advice requested

"middletree " <middletree@hto mail.com> wrote in message
news:%23TFrJWX0 DHA.2288@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Sorry for the non-descriptive subject line.
>
> If you go to http://www.middletree.net/shape.asp, you'll see that I[/color]
have a[color=blue]
> form to fill out.(Ignore the prefilled names and other data; it's just[/color]
for[color=blue]
> testing) When this is filled out, there will be a row in the master[/color]
table,[color=blue]
> called Personal, and there are also static tables for those 4 checkbox[/color]
areas[color=blue]
> you see, called Area, Gift, Ability, and People. Because users can[/color]
check[color=blue]
> more than one box, I resolved the many-to-many by created a union[/color]
table[color=blue]
> (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,[/color]
you'll[color=blue]
> 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[/color]
search[color=blue]
> and give you a similar list, but one which meets the criteria[/color]
selected. In[color=blue]
> other words, if you select Leadership under the Gifts dropdown, and[/color]
click[color=blue]
> the button (which is not there yet), it should show you all people who
> chcked the Leadership checkbox, regardless of what else they selected.[/color]
But[color=blue]
> if you select Leadership from that dropdown, and College from the[/color]
People[color=blue]
> Groups dropdown, then it would give you only those who selected[/color]
Leadership[color=blue]
> 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[/color]
leave[color=blue]
> open the possibility that someone might leave one or more dropdowns
> unselected. I have done joins before, but am drawing a blank on this[/color]
one.[color=blue]
>
> FWIW, this is using Access 2000.[/color]

[uspPersonalSear ch]
PARAMETERS
prmAbilityID Long,
prmAreaID Long,
prmGiftID Long,
prmPeopleID Long
;
SELECT
P.PersonalID,
P.PersonalName
FROM
Personal AS P LEFT JOIN
(
SELECT
PersonalID
FROM
(
SELECT PersonalID FROM PersonalAbility WHERE AbilityID = prmAbilityID
UNION ALL
SELECT PersonalID FROM PersonalArea WHERE AreaID = prmAreaID UNION ALL
SELECT PersonalID FROM PersonalGift WHERE GiftID = prmGiftID UNION ALL
SELECT PersonalID FROM PersonalPeople WHERE PeopleID = prmPeopleID
) AS U
GROUP BY
PersonalID
HAVING
COUNT(*)=ABS((p rmAbilityID<>0) +(prmAreaID<>0) +(prmGiftID<>0) +(prmPeopleI
D<>0))
) AS F
ON
P.PersonalID = F.PersonalID
WHERE
F.PersonalID IS NOT NULL OR
prmAbilityID + prmAreaID + prmGiftID + prmPeopleID = 0


Notes:
1. You will need to specify a value of 0 (zero) for "All" selections in
your ASP/HTML code
2. You will need to modify the above to correspond to your own naming
conventions
3. In the future, please provide sufficient DDL (CREATE TABLE,CREATE
INDEX, etc...) to reproduce you database environment

HTH
-Chris Hohmann

[middletree]

Re: programming advice requested

Ok, thanks. I'll try it out.


"Chris Hohmann" <nospam@thankyo u.com> wrote in message
[color=blue]
> [uspPersonalSear ch]
> PARAMETERS
> prmAbilityID Long,
> prmAreaID Long,
> prmGiftID Long,
> prmPeopleID Long
> ;
> SELECT
> P.PersonalID,
> P.PersonalName
> FROM
> Personal AS P LEFT JOIN
> (
> SELECT
> PersonalID
> FROM
> (
> SELECT PersonalID FROM PersonalAbility WHERE AbilityID = prmAbilityID
> UNION ALL
> SELECT PersonalID FROM PersonalArea WHERE AreaID = prmAreaID UNION ALL
> SELECT PersonalID FROM PersonalGift WHERE GiftID = prmGiftID UNION ALL
> SELECT PersonalID FROM PersonalPeople WHERE PeopleID = prmPeopleID
> ) AS U
> GROUP BY
> PersonalID
> HAVING
> COUNT(*)=ABS((p rmAbilityID<>0) +(prmAreaID<>0) +(prmGiftID<>0) +(prmPeopleI
> D<>0))
> ) AS F
> ON
> P.PersonalID = F.PersonalID
> WHERE
> F.PersonalID IS NOT NULL OR
> prmAbilityID + prmAreaID + prmGiftID + prmPeopleID = 0
>
>
> Notes:
> 1. You will need to specify a value of 0 (zero) for "All" selections in
> your ASP/HTML code
> 2. You will need to modify the above to correspond to your own naming
> conventions
> 3. In the future, please provide sufficient DDL (CREATE TABLE,CREATE
> INDEX, etc...) to reproduce you database environment
>
> HTH
> -Chris Hohmann
>
>[/color]