February 26, 2003 at 5:25 pm
Hello,
I'm not very certain as to how to clearly present a particular issue that I am having with a query, so I will break it down as follows:
1. A sample of the data returned by the query with an explanation of what the data means.
2. The code for the query.
3. An explanation for the other task that I need the query to do.
First, a sample of the data with explanation (field names have been changed slightly from the actual code):
**********************************************************************
School | Test | Grade Lvl | Teacher | Average_Score | Max | #ofStudents
368 ALS 00 Susan Blake 55.000000 100.0 2
**********************************************************************
This record indicates that two students took a Test called 'ALS' at a school with a number designation of '368'. The students were both in grade '00' and their teacher was 'Susan Blake'. The average score for this test was '55.000000', and the maximum score that can be achieved on this test is '100.0' (yes, these students need help).
Next, The query that achieves this output is as follows:
************************************************************
SELECT TOP 100 PERCENT SD.SchoolNum,TT.TestDesc, SD.Grade,
TD.FirstName + ' ' + ' ' + TD.LastName AS TeacherName,
ROUND(AVG(CAST(TS.TestScore AS DECIMAL(8,2))),0)AS Average_Score,
TT.MaxPossible,
COUNT(SD.Permnum) AS Num_of_Students
FROM tblTests TT
Inner join tblMMStudentTestScores TS On TT.TestShortName=TS.TestShortName
Inner join Student_Data_Main SD On SD.Permnum=TS.Permnum
Inner join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID
WHERE TS.TestScore IS NOT NULL AND SD.Schoolnum = '368'
GROUP BY SD.SchoolNum, TT.TestDesc, TT.Maxpossible, SD.Grade, TD.LastName, TD.FirstName
ORDER BY SD.Grade, TD.LastName
**************************************************************
The other thing that I need for this query to do is to indicate something called a 'Rank' that is based on the 'Average_Score' output from the query.
The code for that might look something like this:
****************************
"Rank" = Case
When Average_Score > TT.maxpossible then 'Cant Exceede Max'
When Average_Score < TT.belowbasic then 'Far Below Basic'
When Average_Score < TT.basic then 'Below Basic'
When Average_Score < TT.proficient then 'Basic'
When Average_Score < TT.advanced then 'Proficient'
Else 'Advanced'
END)
****************************
'Average_Score' is CAST to type 'Decimal', and the 'TT.(x)' fields are all type 'Real'. The 'TT.(x)' fields that hold the numbers for the records in 'tblTests' (TT), can hold numbers that range from -3 to 100.
I can't use 'Average_Score' in the CASE statement because it is an Alias and not a field. How can I code the query so that I can get the appropriate 'Rank' expression based on the 'Average_Score'?
I hope I have explained this well enough, please let me know if you have questions.
Thanks for your help!
CSDunn
February 26, 2003 at 5:52 pm
Dear Competitor (yes, I am also working with schools, teachers, classes and scores in my DB):
I think you should take results of your first query, put it into a temp table, then run a second query against it to add the desired Rank. Of course, you need to have your query in a stored procedure.
Otherwise you are risking to complicate the query further unnecessarily.
Michael
February 27, 2003 at 9:57 am
The other option I was considering was just to create a calculated field in the MS Access Project (ADP) report that uses this particular query by implementing 'IIF' statements in an unbound text box control. The rules described in the case statement are not likely to change, so this approach will not likely result in an administrative issue.
Thanks again for your help!
CSDunn
quote:
Dear Competitor (yes, I am also working with schools, teachers, classes and scores in my DB):I think you should take results of your first query, put it into a temp table, then run a second query against it to add the desired Rank. Of course, you need to have your query in a stored procedure.
Otherwise you are risking to complicate the query further unnecessarily.
Michael
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply