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

completely ignorant

Hello, I admit I am completely ignorant of MSDE and SQL in general. Ive
developed many things with php / mysql and a few with access but MS SQL is a
completly new beast for me. Ive been told i need to develop it locally with
MSDE then upload the finished project to the SQL server and just change the
connection string.
Ive installed MSDE (i think) with the SAPWD switch, the installed completed.
Now how do I get access to it? Is there a GUI I can download (like
mysqlAdmin) or does access act as a front end?
Im not even sure that I have it installed right or that it is running. If
there is anyone that would be as kind as to help me though this I would be
most greatful.
hi,
eramus wrote:
> Hello, I admit I am completely ignorant of MSDE and SQL in general.
> Ive developed many things with php / mysql and a few with access but
> MS SQL is a completly new beast for me. Ive been told i need to
> develop it locally with MSDE then upload the finished project to the
> SQL server and just change the connection string.
> Ive installed MSDE (i think) with the SAPWD switch, the installed
> completed. Now how do I get access to it? Is there a GUI I can
> download (like mysqlAdmin) or does access act as a front end?
> Im not even sure that I have it installed right or that it is
> running. If there is anyone that would be as kind as to help me
> though this I would be most greatful.
wow... this deserves a book :D
anyway, you can use Access as front end for MSDE, or you can have a look at
other free and/or commercial tools listed at
http://www.microsoft.com/sql/msde/partners and/or
http://www.aspfaq.com/show.asp?id=2442...
please download and install the SQL Server Books On Line from
http://www.microsoft.com/downloads/d...displaylang=en ,
the best guide ever you need to run and reference for your activity
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" wrote

> wow... this deserves a book :D
;-)

> anyway, you can use Access as front end for MSDE, or you can have a look
> at other free and/or commercial tools
Or use the commandline tool osql.exe (which is included with MSDE)
This gives you the option of a nice crash course in T-SQL
With BOL (Books OnLine) it is possible :-)
http://www.microsoft.com/downloads/d...displaylang=en ,
- Peter
|||Hi,

> Hello, I admit I am completely ignorant of MSDE and SQL in general. Ive
> developed many things with php / mysql and a few with access but MS SQL is
a
> completly new beast for me. Ive been told i need to develop it locally
with
> MSDE then upload the finished project to the SQL server and just change
the
> connection string.
> Ive installed MSDE (i think) with the SAPWD switch, the installed
completed.
> Now how do I get access to it? Is there a GUI I can download (like
> mysqlAdmin) or does access act as a front end?
Try our tool, it works fine with MSDE and its called Database Workbench
www.upscene.com
It also includes MySQL connectivity and schema migration tools.

> Im not even sure that I have it installed right or that it is running. If
> there is anyone that would be as kind as to help me though this I would be
> most greatful.
>
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com