calculating multiple columns

[printsol]

hi can any body help me
i have four tables as shown in image
i want to compare marks in four tables and want to get best 3 marks for every student

thanks

[Minty]

Your design is wrong I'm afraid, have one table with a Semester identifier, not 4 tables.
Then your query becomes very simple.

Fields : SemesterNo , StudentName (or Student_ID), Marks

[printsol]

now its better?
if yes then how now?

[Welshgasman]

No, still not right.
Have a field that holds semester number.

ID
SemesterNum
StudentName
StudentMark

[printsol]

i think now i m close

[Minty]

Yes - That's better.
Now you can start to get your query writing head on. Press the totals button in the query designer, and start pulling in some fields.
This won't be a simple query - but would have been really difficult(impossible) with your original layout.

[printsol]

can u please write sql query ????

[Minty]

I could, but you'll learn more trying to fathom it out for yourself.
I'll give you a hint - group by Student, and select max for the mark. That should get you started.

Like I said this isn't actually a simple query.

[printsol]

ok let me try, hope i will get something

[Micron]

IMO this isn't a simple Totals query because it will require a subquery. I started to post that I could spend 2 hours on it and not get it because subqueries are my nemesis. In fact, had been playing around for about that long when I had that message composed. Decided to try one more thing and BANG! I got it. Because that table should really be a junction table I constructed this (studentID would be the autonumber from tblStudents, not the student name). I guess you would have to swap StudentId in the query for StudentName (should not have spaces or special characters in object names):

StudentMarkID	StudentID	StudentName	Semester	Mark
1	1	Joe	1	20
2	2	John	1	15
3	3	Mary	1	25
4	4	Ellen	1	30
5	1	Joe	2	26
6	2	John	2	31
7	3	Mary	2	35
8	4	Ellen	2	19
9	1	Joe	3	27
10	2	John	3	32
11	3	Mary	3	40
12	4	Ellen	3	16
13	1	joe	4	44
14	2	John	4	45
15	3	Mary	4	46
16	4	Ellen	4	47

This seems to work

SELECT tbl1.StudentID, tbl1.Mark 
FROM tblStudentMarks AS tbl1 
WHERE tbl1.mark IN 
(SELECT TOP 3 Mark FROM tblStudentMarks AS tbl2 WHERE tbl2.StudentID = tbl1.StudentID ORDER BY tbl2.Mark DESC)
 ORDER BY StudentID;

RESULT

StudentID	Mark
1	44
1	27
1	26
2	45
2	32
2	31
3	46
3	40
3	35
4	47
4	19
4	30

EDIT - Just noticed that #4 isn't sorted. To achieve that it seems the outer query requires a sort on Mark as well as the inner query. Change from ORDER BY StudentID to
ORDER BY StudentiD, Mark DESC;

[Welshgasman]

Nice, I was trying Top 3 in main query, and student in subquery.��

[Minty]

I did say it wasn't a simple query

To be fair I thought after I posted about the totals query and half figured it wasn't the right direction to go.
Kudos to Micron for solving it.

I used to be appalling at sub queries until I started using SQL server and SSMS.
It's so simple to write a query, nest it in another one, and then nest another one you can write some nonsense complex stuff without really trying.

[June7]

Original data structure would have required a UNION query to manipulate 4 tables into normalized structure, further complicating the process. The key word here is NORMALIZATION.

[printsol]

ok let me try, hope i will get something

Thank u very much , u make my day

[printsol]

I think in union all tables must have same fields name????