Here's a copy of your database back, probably better to include it because the field names are not the same across your copies.
Here's a copy of your database back, probably better to include it because the field names are not the same across your copies.
oops here it is:
I think this is it! I dont understand anything in this statement but it is perfect!Try this query and see if it's what you want
Code:SELECT HUMANIC_CPO_EMP_ALL.ENUMBER, HUMANIC_CPO_EMP_ALL.SSN, HUMANIC_CPO_EMP_ALL.LASTNAME, HUMANIC_CPO_EMP_ALL.FIRSTNAME, HUMANIC_CPO_EMP_ALL.JOBTITLE, Max(IIf([course_no]='LD520',[status_Date],Null)) AS LBS, Max(IIf([course_no]='LD519',[status_Date],Null)) AS PCD, Max(IIf([course_no]='LD521',[status_Date],Null)) AS PCL, Max(IIf([course_no]='LD518',[status_Date],Null)) AS Wharton FROM HUMANIC_CPO_EMP_ALL LEFT JOIN HUMANIC_EMPTRAIN_MASTER ON HUMANIC_CPO_EMP_ALL.SSN = HUMANIC_EMPTRAIN_MASTER.[SSN ] GROUP BY HUMANIC_CPO_EMP_ALL.ENUMBER, HUMANIC_CPO_EMP_ALL.SSN, HUMANIC_CPO_EMP_ALL.LASTNAME, HUMANIC_CPO_EMP_ALL.FIRSTNAME, HUMANIC_CPO_EMP_ALL.JOBTITLE ORDER BY HUMANIC_CPO_EMP_ALL.SSN;
The formulas in the last four fields are simple:
LBS: Max(IIf([course_no]='LD520',[status_Date],Null))
This is saying that if the course number is LD520 put in the status date, otherwise make it blank, the MAX part takes the maximum value (most recent date) the person completed the course and displays it. The max statement ensures there's only one record per person
ok i understand.. simple if-then statement.
I have another db if you want to help with that one too... it is much easier but has to do with courses again.. I need a list of employees that has not completed a certain course. there is only 2 tables with this data.
i really appreciate your help on this.. I am trying to move to simplier ways of extracting data. My boss still uses dbase and I dont want to go anywhere near that database.
you only have 2 tables in this database.. are the others (training course, EMPTRAIN) not needed?
I don't know what you're asking
If you wanted to find someone who had NOT taken a specific course the query would be similar (using the same tables you have now) you'd just add the COURSENO field and put the course number criteria in the field (take off the summary lines I put in your query) and make the criteria ISNULL, this will pick out anyone that doesn't have a date associated with the given course.
I may have an employee that has taken 30 seperate courses but not "PD198". I need just one record for the employee that has not taken the course. The only tables I use is the CPO_EMP_ALL and EMPTRAIN_MASTER.
In the past I had to run a query of those who had taken PD198 and compare it to an active employee list.
That's about what you have to do, I think there are ways to do it in one query but when I write queries I write them one step at a time so that anyone who comes in after me that may not understand Access very well it's easier to follow what a query does.
So i can create a query of those with PD198 and a query of active employees... how do i compare them in Access? My end result is hopefully something I can create a report with.
As long as both queries have an employee number create a query with both of your sub queries, link the tables on the employee number (this also assumes you have *ONE* record per employee). You want to link the two queries FROM your employee table TO your query that is looking for the most recent date.
So in query one (Employees) you could have the following
EmployeeID
FirstName
LastName
(other fields)
In query two (classes) you could have the following
EmployeeID
Classno (criteria would be the class number you're looking for)
Link the two queries on the EMPLOYEEID but point the arrow connecting them FROM query one TO query two. Then in the CLASSNO field put 'is null' it should list all people missing the class you put in your criteria in query two.
The courseno query will have more than one record for each employee due to employees taking more than one course, unless I ask for those that have only taken PD198... I need to subtract data pretty much an "If not in course query, then show the employee"...
I didnt see this... I will try this.. I think it looks like what I was thinking too.As long as both queries have an employee number create a query with both of your sub queries, link the tables on the employee number (this also assumes you have *ONE* record per employee). You want to link the two queries FROM your employee table TO your query that is looking for the most recent date.
So in query one (Employees) you could have the following
EmployeeID
FirstName
LastName
(other fields)
In query two (classes) you could have the following
EmployeeID
Classno (criteria would be the class number you're looking for)
Link the two queries on the EMPLOYEEID but point the arrow connecting them FROM query one TO query two. Then in the CLASSNO field put 'is null' it should list all people missing the class you put in your criteria in query two.
I think this got me what I needed. I am going to spot check a few and make sure but it looks good.