Access Query that looks at results and gives a yes no

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.
sharris_glascolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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.
0
sharris_glascolAuthor 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;
0
sharris_glascolAuthor Commented:
I should add that this table could have several rows of courses some with dates and some without dates.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sharris_glascolAuthor 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.
0
PatHartmanCommented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharris_glascolAuthor Commented:
THanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.