dear all,
I am developing a web application for a Training Hostel, in my application i need to produce a report for achievements by the Inmates. For individual achievements it is no problem but when it comes to an achievement by a team, i am stucked to display all the team members on the achievement report. All i could display is a list of achievements for a defined period (filtered from a search form) with only one achiever (team leader) in the list. However, i need to show/display all the inmates involved in all the achievements on the list.
the sql i use to display achievements is :
SELECT achievementid, achieveddate, tblcompetitions.competition, complevel, tblachievements.traineeid, tbldiscipline.discipline, tbldiscicats.category, tblevents.event, tbleventtypes.eventtype, tblpositions.positionname, score, tbltrainees.surname, tbltrainees.firstname, tbltrainees.lastname, tbltrainees.gender
FROM tblachievements
LEFT JOIN tblcompetitions ON tblachievements.competitionid = tblcompetitions.competitionid
LEFT JOIN tbltrainees ON tblachievements.traineeid = tbltrainees.traineeid
LEFT JOIN tbldiscipline ON tblachievements.disciplineid = tbldiscipline.disciplineid
LEFT JOIN tbldiscicats ON tblachievements.catid = tbldiscicats.catid
LEFT JOIN tblevents ON tblachievements.eventid = tblevents.eventid
LEFT JOIN tbleventtypes ON tblachievements.eventtypeid = tbleventtypes.eventtypeid
LEFT JOIN tblpositions ON tblachievements.positionid = tblpositions.positionid
WHERE achieveddate >= ['formfield1'] AND achieveddate <= ['formfield2]
ORDER BY tblachievements.disciplineid ASC
i use to record the other team members in the database table :
Table : tblteamdetails
field1: teamdetailsid
field2: achievementid
field3: traineeid
Somebody help me please
Unsolved 
