Access Query that looks at results and gives a yes no

sharris_glascol
sharris_glascol used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
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.

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;

Author

Commented:
I should add that this table could have several rows of courses some with dates and some without dates.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
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
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Actually, the code I posted was for SQL Server, as that one of the zones, and the question title says Access.
Substitute IIf for CASE, and it looks like Pat is on the right track..

Sharris - Let me know if this is a pure Access question, and I'll remove SQL Server as a zone to avoid confusion.

Author

Commented:
THanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial