>I am trying to produce a vb datareport using the following hierchacy
>Department
> -Employee
> -Request
>
>What I want to do is have it show only the employees who actually have requests. My current SQL statement which I use to filter the appropriate records is as follows
>
>SHAPE {SELECT * FROM `Department(Lookup)` WHERE ID = 2} AS ReportByDepartment APPEND (( SHAPE {SELECT ID, FirstName, LastName, Department FROM Employees} AS ReportByEmployee APPEND ({Select * FROM ReportRequests WHERE Status = 'Requested' OR Status = 'Approved' OR Status = 'Disapproved' OR Status = 'Scheduled' OR Status = 'Confirmed' OR Status = 'Cancelled' AND SessionDate > #7/14/99# AND SessionDate < #7/13/00# } AS ReportEmployeeTraining RELATE 'ID' TO 'EmpID') AS ReportEmployeeTraining) AS ReportByEmployee RELATE 'ID' TO 'Department') AS ReportByEmployee
>
>You might want to copy and paste that so you can read it. (It makes alot more sense on one line.
Using the query builder, you can use the aggregate tab, count the number of request at the employees level and filter on count > 0.
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com