Union Query Duplicate Results

[monkeymagic2222]

Hi. I have been asked to query the amount of sickness for our company over the last 12 months. I have created a query to do this which seems to be working. However I also need to display user details for those that have not had any sickness in the last 12 months (but prior to this they may have done). I have created 2 seperate queries, both only slightly different and done a Union to merge the results together. The problem is if a person has had sickness in the last 12 months and prior to this they will be displayed twice. In this instance I only want to display the result from the first query. Does anyone have any ideas how to get around this without having to put the results into a new table and regroup them?

SELECT EMPLOYEE.Surname, EMPLOYEE.Firstname, EMPLOYEE.Hierarchy, EMPLOYEE.dateemploy,
EMPLOYEE.WDMonday, EMPLOYEE.WDTuesday, EMPLOYEE.WDWednesday, EMPLOYEE.WDThursday,
EMPLOYEE.WDSaturday, EMPLOYEE.WDFriday, SUM( EMPLOYEEABSENCE.noofdays ) AS SUM_OF_noofdays
FROM EMPLOYEE JOIN EMPLOYEEABSENCE ON ( EMPLOYEE.empid = EMPLOYEEABSENCE.empid) WHERE
((EMPLOYEEABSENCE.datefrom >= EMPLOYEE.dateemploy AND EMPLOYEE.

= '0')) AND EMPLOYEEABSENCE.AbsCat = 'Sickness'
GROUP BY EMPLOYEE.Surname, EMPLOYEE.Firstname, EMPLOYEE.dateemploy, EMPLOYEE.Hierarchy, EMPLOYEE.WDMonday,
EMPLOYEE.WDTuesday, EMPLOYEE.WDWednesday, EMPLOYEE.WDThursday, EMPLOYEE.WDSaturday, EMPLOYEE.WDFriday
UNION
SELECT EMPLOYEE.Surname, EMPLOYEE.Firstname, EMPLOYEE.Hierarchy, EMPLOYEE.dateemploy,
EMPLOYEE.WDMonday, EMPLOYEE.WDTuesday, EMPLOYEE.WDWednesday, EMPLOYEE.WDThursday,
EMPLOYEE.WDSaturday, EMPLOYEE.WDFriday, '0' AS SUM_OF_noofdays FROM EMPLOYEE LEFT OUTER JOIN
EMPLOYEEABSENCE ON ( EMPLOYEE.empid = EMPLOYEEABSENCE.empid) WHERE ((EMPLOYEE.

= '0' AND
EMPLOYEEABSENCE.datefrom IS NULL)) GROUP BY EMPLOYEE.Surname, EMPLOYEE.Firstname, EMPLOYEE.dateemploy, EMPLOYEE.Hierarchy, EMPLOYEE.WDMonday, EMPLOYEE.WDTuesday, EMPLOYEE.WDWednesday, EMPLOYEE.WDThursday, EMPLOYEE.WDSaturday,
EMPLOYEE.WDFriday ORDER BY EMPLOYEE.Surname​

 

[monkeymagic2222]

Oops, my apologies I actually posted an older version of the query. The second query actually says "WHERE ((EMPLOYEE.

= '0' AND EMPLOYEEABSENCE.datefrom < '2003/11/02' OR EMPLOYEE.

= '0' AND EMPLOYEEABSENCE.datefrom IS NULL))​