Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 48
  1. #31
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here's a copy of your database back, probably better to include it because the field names are not the same across your copies.

  2. #32
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    oops here it is:

  3. #33
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    Quote Originally Posted by rpeare View Post
    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;
    I think this is it! I dont understand anything in this statement but it is perfect!

  4. #34
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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

  5. #35
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    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.

  6. #36
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    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.

  7. #37
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    you only have 2 tables in this database.. are the others (training course, EMPTRAIN) not needed?

  8. #38
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  9. #39
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    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.

  10. #40
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  11. #41
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    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.

  12. #42
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  13. #43
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    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"...

  14. #44
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    Quote Originally Posted by rpeare View Post
    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 didnt see this... I will try this.. I think it looks like what I was thinking too.

  15. #45
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I think this got me what I needed. I am going to spot check a few and make sure but it looks good.

Page 3 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  2. Replies: 1
    Last Post: 06-12-2011, 07:08 AM
  3. Criteria help
    By jcaptchaos2 in forum Access
    Replies: 13
    Last Post: 05-08-2011, 06:49 PM
  4. Search By Criteria - Flexible Criteria Fields
    By lilanngel in forum Access
    Replies: 0
    Last Post: 03-16-2011, 06:25 PM
  5. using checkbox as iif criteria
    By TheShabz in forum Queries
    Replies: 3
    Last Post: 01-18-2011, 05:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums