Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will