473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

programming advice requested

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.


Jul 19 '05 #1
2 1283
"middletree" <mi********@htomail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.


[uspPersonalSearch]
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((prmAbilityID<>0)+(prmAreaID<>0)+(prm GiftID<>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
Jul 19 '05 #2
Ok, thanks. I'll try it out.
"Chris Hohmann" <no****@thankyou.com> wrote in message
[uspPersonalSearch]
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((prmAbilityID<>0)+(prmAreaID<>0)+(prm GiftID<>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

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Marta | last post by:
Hi all! I would to study a web programming language to create a PHP script (output.php) that, given the following input page (input.htm) where the user select the weight and volume of a package...
3
by: ssaeed1973 | last post by:
I am trying to write a program in python (brand new to Python) that would create a database of posts made to binary groups so the user can search for a certain file and have a nzb file returned. I...
9
by: obhayes | last post by:
Hi, I have two tables Table A and B, below with some dummy data... Table A (contains specific unique settings that can be requested) Id, SettingName 1, weight 2, length Table B (contains...
2
by: Brian | last post by:
NOTE ALSO POSTED IN microsoft.public.dotnet.framework.aspnet.buildingcontrols I have solved most of my Server Control Collection property issues. I wrote an HTML page that describes all of the...
4
by: GUSTAVO | last post by:
Hello, my name is Gustavo Vásquez and I want to learn how to program computers What one needs to be a good programmer of computers Which your advice are to be a good programmer How is the logic...
4
by: Java Challenge | last post by:
I am trying to work hard to become a programmer and eventually get a job as a programmer, I have a low paying job at the moment as technical support and a family to maintain. 1 - I started to...
7
by: Robert Seacord | last post by:
The CERT/CC has just deployed a new web site dedicated to developing secure coding standards for the C programming language, C++, and eventually other programming language. We have already...
318
by: jacob navia | last post by:
Rcently I posted code in this group, to help a user that asked to know how he could find out the size of a block allocated with malloc. As always when I post something, the same group of people...
6
by: DanielJohnson | last post by:
I have done some regular C programming and still learning. I was wondering if you could guide me through good resources for thread programming and socket programming in C. I have done something in...
9
by: Smithers | last post by:
Please consider this humble method: public void ResetCounters() { m_TotalExceptionsDetected = 0; m_TotalMessagesSent = 0; } Given no further information, would you wrap those two lines in a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.