List Team members in Achievements report

[bobbymoir]

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