One set is doubled

[Thecherub]

I have report with two subreports. The report is for tracking time off the Type of Time off is Value list "Vacation Approved, Vacation Denied, LOA Approved, LOA Denied, and LOW" my subreports count the History and the current and subtotal the information. One of the employees is being listed twice. He id the only one with Different Time off types in Current and History. The Main reports code is below. Can someone please tell me how to get this report to print right. Each person only once. I thought i had it.

SELECT DISTINCT [Employee List].Department, [Employee List].[Last Name], [Employee List].[First Name], [Employee List].[Seniority Date], [Employee List].[Vacation Elegibility], Count([Time off Booked].[Time Off Booked]) AS [CountOfTime Off Booked], [Time off Booked].[Time Off Booked]
FROM [Employee List] INNER JOIN [Time off Booked] ON [Employee List].[Employee Number] = [Time off Booked].[Employee Number]
GROUP BY [Employee List].Department, [Employee List].[Last Name], [Employee List].[First Name], [Employee List].[Seniority Date], [Employee List].[Vacation Elegibility], [Time off Booked].[Time Off Booked]
HAVING ((([Time off Booked].[Time Off Booked])="Vacation Approved" Or ([Time off Booked].[Time Off Booked])="Vacation Denied" Or ([Time off Booked].[Time Off Booked])="LOA Approved" Or ([Time off Booked].[Time Off Booked])="LOA Denied" Or ([Time off Booked].[Time Off Booked])="LOW"));

 

[Kraj]

Well since sometimes we overlook the obvious, is it possible that employee has two entries in the employee table?

 

[Thecherub]

I wish it was that easy

Well since sometimes we overlook the obvious, is it possible that employee has two entries in the employee table?

First thing I checked. I thinks the problem is with the fact that the person in question has a LOA approved in History and Vacation Approveds in current so the main reports notes 2 sets of data and displays both nor relizing it's the same data.

I'm grasping at straws here.

 

[Thecherub]

Problem Narrowed

First thing I checked. I thinks the problem is with the fact that the person in question has a LOA approved in History and Vacation Approveds in current so the main reports notes 2 sets of data and displays both nor relizing it's the same data.

I'm grasping at straws here.

The problem is occuring with everyone who has more that one type of Absence. It displays them as many times as they have items.

It some one has both LOA Approved and Vacation Approved it will list them twice if the have LOA App,Vacation App and Vacation Denied it will show 3 times id they have only one type it will only show once.

 

[Thecherub]

Fixed

The problem is occuring with everyone who has more that one type of Absence. It displays them as many times as they have items.

It some one has both LOA Approved and Vacation Approved it will list them twice if the have LOA App,Vacation App and Vacation Denied it will show 3 times id they have only one type it will only show once.[/QUOTE

boy was I off the wall how i fixed it was to create another subreport (there are now 5 of them) to calculate a value that was based on the count of the types of time off. and was causing the problem. because the Types was in the main report it was relisting for each type. Once i eliminated the type from the main the error went bye bye.