Solved

Access Query that looks at results and gives a yes no

Posted on 2014-11-07
7
130 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.
0
Comment
Question by:sharris_glascol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40428677
<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
 

Author Comment

by:sharris_glascol
ID: 40428720
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
 

Author Comment

by:sharris_glascol
ID: 40428723
I should add that this table could have several rows of courses some with dates and some without dates.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:sharris_glascol
ID: 40428805
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 40429165
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40429180
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
 

Author Closing Comment

by:sharris_glascol
ID: 40437899
THanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Not Equal To not working as expected 11 28
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
View SQL Count Records 3 32
Creating Scalar Function 3 19
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question