If I am following you correctly, here is a possibility. I am assuming that
you are using a checkbox that returns multiple values and you want your
query to use these values. Since coldfusion passes these variables into a
comma delimited list, the following code should work.
<cfquery datasource="trial" ...>
SELECT (necessary criteria)
FROM tblTrial, tblResults
WHERE tblTrial.tri_id = tblResults.tri_id
<CFIF Form.States IS NOT "">
AND tblResults.States IN (#form.States#)
</CFIF>
<CFIF Form.Year IS NOT "">
AND tblResults.sdYear IN (#form.Year#)
</CFIF>
GROUP BY tblResults.standage
</CFQUERY>
I am sure that I probably missed something, but I hope this gets you pointed
in the right direction.
David Henry
HPER Technology Services
School of Health, Physical Education and Recreation
Indiana University
(812)855-4270
[EMAIL PROTECTED]
-----Original Message-----
From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 25, 2000 3:07 PM
To: [EMAIL PROTECTED]
Subject: help with approach to complicated query
Okay, I give up. I'm asking for help on this one. I have a form, where
people can select 0-1-many states and years. The codes for those are passed
to an action page, where varieties of alfalfa that were tested in those
states and/or years are displayed. The user can choose two varieties (and
should be able to eventually choose more).
On to the next page, where yield in tons/acre, mean yield of the trial, and
percentage of the mean are calculated for each variety by "age of stand".
Okay, got all that working.
Now, the client wants me to determine which trials both varieties were in
(i.e. side by side tests) and determine the percent of variety 1 by variety
two by stand age, as well as how many trials they were both in by stand age.
Seems like this shouldn't be as hard as it's become, and I think I just need
a good swift kick in the butt to get me going in the right direction.
The tables are like so:
RESULT TABLE: tri_id (trial id), var_id (variety id), yield_ta (yield in
tons/acre)
TRIAL TABLE: tri_id, state, seedyr (year), standage, myield (mean yield of
the trial)
How would you conduct a query to pull the sets of rows that meet all the
criteria, (state, sdyear, var_id) grouped by standage.
Do ya follow?
Thanks!
Brain dead from listening to the jackhammer across the street for the last
two weeks.
-Deanna
************************************************************
Deanna Schneider
Interactive Media Developer
UWEX Cooperative Extension Electronic Publishing Group
103 Extension Bldg
432 N. Lake Street
Madison, WI 53706
(608) 265-7923
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
