Union Query returning extra rows

[Guest]

I have a union query which is detailed below. The query runs fine execpt it
returns 3 rows for each person for the different categories of leave instead
of one for each person. The database is set up to record people's leave from
work. Annual leave, sick leave, and flexi leave. Unfortuntely these are all
stored in seperate tables and this is what I have to work with.

SELECT [team 1].PayNo, [team 1].Surname, [team 1].FirstName, Sum([sickness
absence details].ALDuration) AS SumOfALDuration, Sum([sickness absence
details].Duration) AS SumOfDuration, Count([sickness absence
details].FlexiIncidentNo) AS CountOfFlexiIncidentNo
FROM [team 1] INNER JOIN [sickness absence details] ON [team 1].PayNo =
[sickness absence details].[Pay No]
GROUP BY [team 1].PayNo, [team 1].Surname, [team 1].FirstName
ORDER BY [team 1].Surname

UNION ALL

SELECT [team 1].PayNo, [team 1].Surname, [team 1].FirstName, Sum([flexi
absence details].ALDuration) AS SumOfALDuration, Sum([flexi absence
details].Duration) AS SumOfDuration, Count([flexi absence
details].FlexiIncidentNo) AS CountOfFlexiIncidentNo
FROM [team 1] INNER JOIN [flexi absence details] ON [team 1].PayNo = [flexi
absence details].PayNo
GROUP BY [team 1].PayNo, [team 1].Surname, [team 1].FirstName
ORDER BY [team 1].Surname

UNION ALL SELECT [team 1].PayNo, [team 1].Surname, [team 1].FirstName,
Sum([annual leave details].ALDuration) AS SumOfALDuration, Sum([annual leave
details].Duration) AS SumOfDuration, Count([annual leave
details].FlexiIncidentNo) AS CountOfFlexiIncidentNo
FROM [team 1] INNER JOIN [annual leave details] ON [team 1].PayNo = [annual
leave details].[Pay No]
GROUP BY [team 1].PayNo, [team 1].Surname, [team 1].FirstName
ORDER BY [team 1].Surname;

 

[Guest]

Each SELECT of the Unions is going to return one row. To limit that, you can
add critieria to the whole thing. Like:

SELECT a.f1, a.f2, a.f3 From
(Select... union... Select... union... select... union) as a
WHERE a.field = 'Value'