Highest and Lowest Numbner

[Nur Muhammad]

Dear Friend:
I want the highest and lowest number for every student from following chart:


StudentName Query
StID StName TestID TestName Chemistry Bioloy Math
1 Jamal 1 Test 1 70 85 74
1 Jamal 2 Test 2 95 75 98
1 Jamal 3 Test 3 44 74 84
2 Mijan 4 Test 1 88 74 95
2 Mijan 5 Test 2 84 71 69
2 Mijan 6 Test 3 67 95 79
3 Kamal 7 Test 1 74 88 69
3 Kamal 8 Test 2 84 59 67


Let say Jamal attend three exam like Test 1, Test 2, Test 3. From that his
highest number is 98 and lowest number is 70

Pls give me a solution to do that

Thanks
Nur Muhammad

 

[Rick Brandt]

Dear Friend:
I want the highest and lowest number for every student from following
chart:


StudentName Query
StID StName TestID TestName Chemistry Bioloy Math
1 Jamal 1 Test 1 70 85 74
1 Jamal 2 Test 2 95 75 98
1 Jamal 3 Test 3 44 74 84
2 Mijan 4 Test 1 88 74 95
2 Mijan 5 Test 2 84 71 69
2 Mijan 6 Test 3 67 95 79
3 Kamal 7 Test 1 74 88 69
3 Kamal 8 Test 2 84 59 67


Let say Jamal attend three exam like Test 1, Test 2, Test 3. From
that his highest number is 98 and lowest number is 70

Pls give me a solution to do that

Thanks
Nur Muhammad

Your difficulty comes from an improper table design. The information that a
test is for Chemistry, Biology, or Math should be on separate ROWs not
separate COLUMNS. In other words, Jamal should have 9 records in your table
instead of 3.

StID StName TestID TestName TestSubject TestScore
1 Jamal 1 Test 1 Chemistry
70
1 Jamal 2 Test 1 Biology
85
1 Jamal 3 Test 1 Math
74
1 Jamal 4 Test 2 Chemistry
95
1 Jamal 5 Test 2 Biology
75
1 Jamal 6 Test 2 Math
98
1 Jamal 7 Test 3 Chemistry
44
1 Jamal 8 Test 3 Biology
74
1 Jamal 9 Test 3 Math
84
etc..

With the table structure above, a very simple totals query returns his high
and low score...

SELECT StID, StName, Max(TestScore), Min(TestScore)
FROM TableName
GROUP BY StID, StName

 

[Nur Muhammad]

Dear Friend:
Many many thnks for solving my problem
Know i want to get another help for that query.
From above query i get highest and lowest score. If lowest score is 0 then i
don't want 0. i want Second lowest score
Example as follow:
74, 44, 81, 0, 11
from that example i don't want number 0, i want 11
How is ti possible?
Pls solve my proble


Thanks
Nur Muhammad

 

[Rick Brandt]

Dear Friend:
Many many thnks for solving my problem
Know i want to get another help for that query.
From above query i get highest and lowest score. If lowest score is 0
then i don't want 0. i want Second lowest score
Example as follow:
74, 44, 81, 0, 11
from that example i don't want number 0, i want 11
How is ti possible?
Pls solve my proble

Just add a criteria of >0 to your query for the score field.