
/// markquery ///////////////////////////////////////////////////////////////
TRANSFORM First(cust_evo_marks.marks) AS FirstOfmarks
SELECT cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks, Sum(cust_evo_marks.marks) AS Total, Format(((Sum(cust_evo_marks.marks)*100)/Sum(cust_evo_test_setup.max_marks)),'Standard') AS Per, Sum(IIf(cust_evo_marks.marks<[MarksheetTestTemp.passing_marks],1,0)) AS result, Sum(IIf(cust_evo_marks.marks>=[MarksheetTestTemp.passing_marks],cust_evo_marks.marks,-400)) AS RankTotal, Format(((Sum(cust_evo_marks.marks)*100)/Sum(MarksheetTestTemp.max_marks)),'Standard') AS Expr1, Sum(MarksheetTestTemp.max_marks) AS TotalOfMaxMarks
FROM cust_evo_marks INNER JOIN MarksheetTestTemp ON cust_evo_marks.test_id = MarksheetTestTemp.test_id
WHERE (((cust_evo_marks.test_id) In (SELECT c.test_id FROM TestDetailForMarksheet as c)) AND ((cust_evo_marks.student_id) In (SELECT temp_stud_ID.student_id FROM temp_stud_ID)))
GROUP BY cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks
ORDER BY cust_evo_marks.test_id
PIVOT cust_evo_marks.test_id;






////////////////////////// MarksheetTempRank ///////////////////////
SELECT DISTINCT fr.RankTotal
FROM markquery AS fr
ORDER BY fr.RankTotal DESC;


//////////////////////////// MarksheetTempRank1 ////////////////////////

SELECT tbl1.RankTotal, count(*) AS Rank
FROM MarksheetTempRank AS tbl1 INNER JOIN MarksheetTempRank AS tbl2 ON tbl1.RankTotal <= tbl2.RankTotal
GROUP BY tbl1.RankTotal;


/////////////// RankMarks ///////////////////////////////////////////

SELECT m.student_id, m.RankTotal, b.Rank
FROM markquery AS m LEFT JOIN MarksheetTempRank1 AS b ON m.RankTotal=b.RankTotal
ORDER BY m.RankTotal DESC;


//////////////////// MarksheetForApp ////////////////////////////////////////

SELECT markquery.*, RankMarks.rank FROM markquery INNER JOIN RankMarks ON markquery.student_id = RankMarks.student_id ORDER BY RankMarks.rank;




/////////////////////////////////////////// markquery ///////// with is absent quary////////
TRANSFORM First(cust_evo_marks.marks) AS FirstOfmarks
SELECT cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks, Sum(cust_evo_marks.marks) AS Total, Format(((Sum([cust_evo_marks].[marks])*100)/Sum(IIf([cust_evo_marks].[is_absent]=True,null,[cust_evo_test_setup].[max_marks]))),'Standard') AS Per, Sum(IIf(cust_evo_marks.marks<[MarksheetTestTemp.passing_marks],1,0)) AS result, ((Sum([cust_evo_marks].[marks])*100)/Sum(IIf([cust_evo_marks].[is_absent]=True,null,[cust_evo_test_setup].[max_marks]))) AS RankTotal, Format(((Sum(cust_evo_marks.marks)*100)/Sum(MarksheetTestTemp.max_marks)),'Standard') AS Expr1, Sum(MarksheetTestTemp.max_marks) AS TotalOfMaxMarks
FROM cust_evo_marks INNER JOIN MarksheetTestTemp ON cust_evo_marks.test_id = MarksheetTestTemp.test_id
WHERE (((cust_evo_marks.test_id) In (SELECT c.test_id FROM TestDetailForMarksheet as c)) AND ((cust_evo_marks.student_id) In (SELECT temp_stud_ID.student_id FROM temp_stud_ID)))
GROUP BY cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks
ORDER BY cust_evo_marks.test_id
PIVOT cust_evo_marks.test_id;


///////////////  ////////////

TRANSFORM First(cust_evo_marks.marks) AS FirstOfmarks
SELECT cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks, Sum(cust_evo_marks.marks) AS Total, Format(((Sum([cust_evo_marks].[marks])*100)/Sum(IIf([cust_evo_marks].[is_absent]=True,null,[cust_evo_test_setup].[max_marks]))),'Standard') AS Per, Sum(IIf(cust_evo_marks.marks<[MarksheetTestTemp.passing_marks] and [cust_evo_marks].[is_absent]=False,1,0)) AS result, ((Sum([cust_evo_marks].[marks])*100)/Sum(IIf([cust_evo_marks].[is_absent]=True,null,[cust_evo_test_setup].[max_marks]))) AS RankTotal, Format(((Sum(cust_evo_marks.marks)*100)/Sum(MarksheetTestTemp.max_marks)),'Standard') AS Expr1, Sum(MarksheetTestTemp.max_marks) AS TotalOfMaxMarks
FROM cust_evo_marks INNER JOIN MarksheetTestTemp ON cust_evo_marks.test_id = MarksheetTestTemp.test_id
WHERE (((cust_evo_marks.test_id) In (SELECT c.test_id FROM TestDetailForMarksheet as c)) AND ((cust_evo_marks.student_id) In (SELECT temp_stud_ID.student_id FROM temp_stud_ID)))
GROUP BY cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks
ORDER BY cust_evo_marks.test_id
PIVOT cust_evo_marks.test_id;

///////////////////////// markquery // with percentage pass fail result //////////////////

TRANSFORM First(cust_evo_marks.marks) AS FirstOfmarks
SELECT cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks, Sum(cust_evo_marks.marks) AS Total, Format(((Sum(cust_evo_marks.marks)*100)/Sum(cust_evo_test_setup.max_marks)),'Standard') AS Per, Sum(IIf(((cust_evo_marks.marks*100)/cust_evo_test_setup.max_marks)<[MarksheetTestTemp.passing_marks],1,0)) AS result, Sum(IIf(((cust_evo_marks.marks*100)/cust_evo_test_setup.max_marks)<[MarksheetTestTemp.passing_marks],-400,cust_evo_marks.marks)) AS RankTotal, Format(((Sum(cust_evo_marks.marks)*100)/Sum(MarksheetTestTemp.max_marks)),'Standard') AS Expr1, Sum(MarksheetTestTemp.max_marks) AS TotalOfMaxMarks
FROM cust_evo_marks INNER JOIN MarksheetTestTemp ON cust_evo_marks.test_id = MarksheetTestTemp.test_id
WHERE (((cust_evo_marks.test_id) In (SELECT c.test_id FROM TestDetailForMarksheet as c)) AND ((cust_evo_marks.student_id) In (SELECT temp_stud_ID.student_id FROM temp_stud_ID)))
GROUP BY cust_evo_marks.student_id, MarksheetTestTemp.FromDate, MarksheetTestTemp.ToDate, MarksheetTestTemp.passing_marks
ORDER BY cust_evo_marks.test_id
PIVOT cust_evo_marks.test_id;


