We help IT Professionals succeed at work.

Access Query that looks at results and gives a yes no

156 Views
Last Modified: 2014-11-12
Ok I have a table that lists Emp_id, and Course_ID, and Course_completed.  Is is possible to generate a query that looks and at all of the courses for that employee and then checks date to see if they have completed all and If they have it will state MEETS REQUIREMENTS else Does not meet requirements.
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<air code>
SELECT Emp_ID, CASE course_not_completed_count WHEN 0 THEN 'MEETS REQUIREMENTS' ELSE 'DOES NOT MEET REQUIREMENTS' END as requirements
FROM (
   SELECT Emp_ID, 
     SUM(CASE WHEN ISNULL(Course_Completed) = 1 THEN 1 ELSE 0 END) as course_not_completed_count
   FROM YourTable
   GROUP BY ID) a

Open in new window


btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, if it helps.
sharris_glascolIT Manager

Author

Commented:
So here is my tables that I will be using.  the dbo_emp_course.trainingcompl is the table that will store the date.  If no Date I need it to store a field that says Does Note Meet Requirements, but if it does Meets requirements.

SELECT dbo_EMP_Train.EmpID,
dbo_Emp_course.Course_name,
dbo_Emp_course.TrainingCompl

FROM
 ((dbo_EMP_Train INNER JOIN dbo_JobGroup ON dbo_EMP_Train.PrimaryPosition = dbo_JobGroup.PositionID)
 INNER JOIN dbo_TrainingCourse ON dbo_JobGroup.CourseID = dbo_TrainingCourse.CourseID)
 INNER JOIN dbo_Emp_course ON dbo_TrainingCourse.CourseName = dbo_Emp_course.Course_name;
sharris_glascolIT Manager

Author

Commented:
I should add that this table could have several rows of courses some with dates and some without dates.
sharris_glascolIT Manager

Author

Commented:
example:  
dbo_emp_train.empid      dbo_emp_course.course_name,      dbo_emp_course.trainingcompl
2321                                                    training a                                                            1/1/00
2321                                                     training b      
2321                                                     training c                                                     1/1/00
2400                                                      training a                                                     2/1/00
2400                                                      training b                                                      2/1/00
2400                                                     training c                                                       2/1/00

So in this example employee 2321 would be a Does not meet requirements and employee 2400 would be Meets requirements.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
It is not good practice to store calculated values.  Once any one of the underlying records is updated or a new one is added, the calculated value becomes obsolete.

Did you try the query Jim posted?  It is T-SQL syntax and therefore must be run as a pass-through query in Access.  If you want something in Access syntax, then the following should do it.

SELECT dbo_EMP_Train.EmpID, IIf(Sum(IIf(IsDate(dbo_Emp_course.TrainingCompl, 1, 0)) = Count(*), "Meets Requirements", "Does Not Meet Requirements")
 FROM
  ((dbo_EMP_Train INNER JOIN dbo_JobGroup ON dbo_EMP_Train.PrimaryPosition = dbo_JobGroup.PositionID)
  INNER JOIN dbo_TrainingCourse ON dbo_JobGroup.CourseID = dbo_TrainingCourse.CourseID)
  INNER JOIN dbo_Emp_course ON dbo_TrainingCourse.CourseName = dbo_Emp_course.Course_name
Group by dbo_EMP_Train.EmpID;

You'll probably want the employee name in addition to the EmpID but you CANNOT add the course names since that would remove the aggregation.
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
sharris_glascolIT Manager

Author

Commented:
THanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.