Eliminating doubles with NO manual intervention

[Myriad_Rocker]

Okay, this one has been a pill for me. I just plainly am sick of trying to work with it because every theory I have is a dead end road.

I have a table with employees on it. They are identified by an employee code. BUT, there is also what is called a dependent code on it. Each employee can have multiple dependent codes. But here's the real kicker...there exists multiple "plan types" for each employee. So, a record might have the following...

EmpID, DepCode, Plan Type

So, let's say that an Emp has plan types 10, 11, and 14. So, that employee would turn up 3 times in the table with the same dependent code (let's say depcode = 1).

0001 1 10
0001 1 11
0001 1 14
0002 1 11
0002 1 14

*Note* - As you can see, an Emp does NOT have to have all plan types.

I can't do "DISTINCT" on anything because I will eliminate rows that shouldn't be eliminated.

I'm wanting to pull information for a report but I'm getting multiple reports for one person and I don't want that.

 

[Myriad_Rocker]

The situation does not allow a redesign of the database.

 

[Myriad_Rocker]

Sorry I didn't post this yesterday but I figured out how to get it done. I used the "DISTINCT" feature after all and since there were multiple groupings, it worked out great.