force all names of list into report 1

[MrRocky]

I would like to auto email injury records to people in a email table. This people only take care certain teams of plant floor. So they only need to see the injury records related to their teams based on a start date and end date.

Problem is that I have 20 teams and as in example above John Doe takes care of Team 1, Team 2, Team 3 and Team 4 but Team 2 and Team 3 did not have an injury for the month of Jan and I still want to show all for teams with a message in the report that there was no injuries for Team 2.
Any ideas?????

 

[balor]

Do you have the people, teams and injurys in different tables? Then you can inner join the people and the teams but outer join the injurys. Then you always get all teams for the person and you can check if the injurys are NULL in any record.

Something like this:

SELECT
People.Person,
Teams.Team,
Iff(IsNull(Injurys.Injury),"No injurys",Injurys.Injury) AS Injurys
FROM
People
INNER JOIN
Teams
ON (maby it's a relation table here?)
People.ID = Teams.TeamleaderID
LEFT OUTER JOIN
Injurys
ON
Teams.ID = Injurys.TeamID
Where
People.ID = ?