查询有意义的报告

Question

样本查询结果

 Employee_Last   Employee_First   RoleName   CourseName   CourseStart   CourseEnd   Attended  
 Ables           Christopher      ServiceMGR OTC Training 12/1/11       12/1/11     Yes  
 Ables           Christopher      ServiceMGR                                        No
 Ables           Christopher      ServiceMGR OTC Part 1   12/5/11       12/5/11     Yes  
 Ables           Christopher      AssetShipper                                      No  
 Ables           Christopher      AssetShipper                                      No  
 Ables           Christopher      AssetShipper                                      No  
 Ables           Christopher      AssetShipper                                      No  

这些是我从查询中得到的结果，有很多查询是从表中提取来获得这些数据的。数据库已规范化
我想要创建的报告是在Attended上创建的。

 If Yes 
          Show all of the line item from the query 
 Else If   
          If RoleName is listed previously for a specific employee and attended =  
             yes on that line item(ie: Ables | Christopher | ServiceMGR | No -- should not be seen)  
             Don't Show 
          Else If RoleName is listed previously for a specific employee and Attended = No  
             Only Show the item once(ie: Should only see Ables | Christopher | AssetShipper | No)  
          End If  
 End IF

因此，它可能需要另一个查询来过滤这个查询。我从样本数据中寻找的报告应该是这样的：

 Employee_Last   Employee_First   RoleName   CourseName   CourseStart   CourseEnd   Attended  
 Ables           Christopher      ServiceMGR OTC Training 12/1/11       12/1/11     Yes  
 Ables           Christopher      ServiceMGR OTC Part 1   12/5/11       12/5/11     Yes  
 Ables           Christopher      AssetShipper                                      No  

希望你能明白我在说什么。我对如何隐藏和显示记录做了一些研究，但我对Access不够熟悉。因此，基本上我需要过滤Attended=No，以便对最终用户有意义。

Answer 1

也许这几行有什么东西：

SELECT employee_last,
       employee_first,
       rolename,
       coursename,
       coursestart,
       courseend,
       attended
FROM   attend
WHERE  attended = "Yes"
UNION
SELECT DISTINCT b.employee_last,
                b.employee_first,
                b.rolename,
                NULL AS coursename,
                NULL AS coursestart,
                NULL AS courseend,
                b.attended
FROM   (SELECT employee_last,
               employee_first,
               rolename
        FROM   attend
        WHERE  attended = "Yes") AS a
       RIGHT JOIN (SELECT employee_last,
                          employee_first,
                          rolename,
                          attended
                   FROM   attend
                   WHERE  attended = "No") AS b
         ON ( a.rolename = b.rolename )
            AND ( a.employee_first = b.employee_first )
            AND ( a.employee_last = b.employee_last )
WHERE  (( ( a.employee_last ) IS NULL ))