CODEWITHSUNDEEP
ms-accessreportingms-access-2007ms-access-2010
Piercy
Piercy

Reputation: 51

Query to a Meaningful Report

Sample Query Results 

 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

These are the results I'm getting from the Query, there are many Queries which are pulling from tables to get this data. The Database is Normalized.
The report I'm looking to create is something that looks at 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

So it is probably going to require another Query that filters this query. The report I'm looking for from the sample data should look like this: 

 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

Hopefully you see what I'm talking about. I've done some research on how to hide and display records but I'm not familiar enough with Access. So basically I need to filter the Attended = No's to be meaningful to the end user.

Upvotes: 1

Views: 88

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

Perhaps something on these lines:

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 ))

Upvotes: 2

Related Questions