Ray:
Thanks. Unfortunately, right now I don't have those pages uploaded to the Web. I'll upload them later and maybe we can work on this tomorrow. In the meantime, can you tell why the Min(date) and Max(date) functions work well in case No.1 but not in case No.2?
Case No.1:
SELECT oxadmain.name , oxadmain.year , oxadmain.photoID , oxadmain.thumbs , stuattend.ID , stuattend.studid , min(stuattend.date) as fromdate , max(stuattend.date) as todate , stuattend.teacher, stuattend.subject , stuattend.attendance, Count(*) AS RowCount, SUM(if(stuattend.attendance = 'Absent', 1, 0)) AS absents
FROM stuattend INNER JOIN oxadmain ON stuattend.studid=oxadmain.stuid
GROUP BY studid
ORDER BY name ASC
Case No.2:
SELECT oxadmain.name , oxadmain.year , oxadmain.photoID , oxadmain.thumbs , perfgroups1to4.ID , perfgroups1to4.studid , perfgroups1to4.subject , perfgroups1to4.teacher , perfgroups1to4.cw , perfgroups1to4.con , perfgroups1to4.att , perfgroups1to4.attPunct , perfgroups1to4.part , perfgroups1to4.disc , perfgroups1to4.proj , perfgroups1to4.exAct , MIN(perfgroups1to4.date) AS fromDate , MAX(perfgroups1to4.date) AS toDate , SUM(cw) AS cwSum, SUM(con) AS conSum, SUM(att) AS attSum, SUM(attPunct) AS attPunctSum, SUM(part) AS partSum, SUM(disc) AS discSum, SUM(proj) AS projSum, SUM(exAct) AS exActSum, ROUND((AVG(cw)+AVG(con)+AVG(att)+AVG(attPunct)+AVG(part)+AVG(disc)+AVG(proj)+AVG(exAct))/5, 1) AS Avg, cw+con+att+attPunct+part AS ClPerfTot, SUM(disc) AS discTot, cw+con+att+attPunct+part+disc AS Total
FROM perfgroups1to4 INNER JOIN oxadmain ON perfgroups1to4.studid=oxadmain.stuid
GROUP BY studid
ORDER BY name ASC
In case No.2 both Min/Max functions return the date the individual records were entered.
