Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255

    criteria

    I am trying to run a query which has employee classifications and course names. I need to get the employees that have completed the 4 courses as well as the ones that has not completed the course. In the builder I have "In ('LD518','LD519','LD520','LD521')" and put "Is Null" in the or section but all i get back is the LD courses and not the employees with no data... what am i doing wrong?

  2. #2
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    here is my SQL:

    SELECT HUMANIC_EJOBSAL.ENUMBER, HUMANIC_EJOBSAL.FIRSTNAME, HUMANIC_EJOBSAL.LASTNAME, HUMANIC_EJOBSAL.PROMOSTAT, HUMANIC_CPO_EMP_ALL.GRADE, HUMANIC_EMPTRAIN_MASTER.STATUS_DATE, HUMANIC_TRAINING_COURSE.COURSE_NO, HUMANIC_TRAINING_COURSE.COURSE_NAME, [HRP DATA].HRP, HUMANIC_CPO_EMP_ALL.JOBTITLE, HUMANIC_CPO_EMP_ALL.DIV_1_DESC
    FROM [HRP DATA] INNER JOIN ((HUMANIC_EJOBSAL INNER JOIN HUMANIC_CPO_EMP_ALL ON HUMANIC_EJOBSAL.SSN = HUMANIC_CPO_EMP_ALL.SSN) INNER JOIN (HUMANIC_TRAINING_COURSE INNER JOIN HUMANIC_EMPTRAIN_MASTER ON HUMANIC_TRAINING_COURSE.COURSE_NO = HUMANIC_EMPTRAIN_MASTER.COURSE_NO) ON HUMANIC_EJOBSAL.SSN = HUMANIC_EMPTRAIN_MASTER.SSN) ON [HRP DATA].SUPERVISOR = HUMANIC_EJOBSAL.SUPERVISOR
    WHERE (((HUMANIC_EJOBSAL.PROMOSTAT) In ('EMT','D10')) AND ((HUMANIC_TRAINING_COURSE.COURSE_NO) In ('LD518','LD519','LD520','LD521'))) OR (((HUMANIC_TRAINING_COURSE.COURSE_NO) Is Null));

    Even with the "IS NULL" i am getting the same data as i would without the statement. Im sure it is a join issue but im not totally familiar with Access yet. In query builder i could join 3 tables any way i need too to get the data i want.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    try

    ... OR isnull(HUMANIC_TRAINING_COURSE.COURSE_NO)

  4. #4
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I changed my SQL to SELECT HUMANIC_EJOBSAL.ENUMBER, HUMANIC_EJOBSAL.FIRSTNAME, HUMANIC_EJOBSAL.LASTNAME, HUMANIC_EJOBSAL.PROMOSTAT, HUMANIC_CPO_EMP_ALL.GRADE, HUMANIC_EMPTRAIN_MASTER.STATUS_DATE, HUMANIC_TRAINING_COURSE.COURSE_NO, HUMANIC_TRAINING_COURSE.COURSE_NAME, [HRP DATA].HRP, HUMANIC_CPO_EMP_ALL.JOBTITLE, HUMANIC_CPO_EMP_ALL.DIV_1_DESC
    FROM [HRP DATA] INNER JOIN ((HUMANIC_EJOBSAL INNER JOIN HUMANIC_CPO_EMP_ALL ON HUMANIC_EJOBSAL.SSN = HUMANIC_CPO_EMP_ALL.SSN) INNER JOIN (HUMANIC_TRAINING_COURSE INNER JOIN HUMANIC_EMPTRAIN_MASTER ON HUMANIC_TRAINING_COURSE.COURSE_NO = HUMANIC_EMPTRAIN_MASTER.COURSE_NO) ON HUMANIC_EJOBSAL.SSN = HUMANIC_EMPTRAIN_MASTER.SSN) ON [HRP DATA].SUPERVISOR = HUMANIC_EJOBSAL.SUPERVISOR
    WHERE (((HUMANIC_EJOBSAL.PROMOSTAT) In ('EMT','D10')) AND ((HUMANIC_TRAINING_COURSE.COURSE_NO) In ('LD518','LD519','LD520','LD521'))) OR isnull(HUMANIC_TRAINING_COURSE.COURSE_NO);

    Still a no go.. anymore ideas? Is it a join issue?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you post a sample database? Or at the very least show some example data and what you want to see when you run your query (just as simple example will do, it doesn't have to have all the fields you're looking at, just the fields you're applying criteria to)

  6. #6
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I have some sensitive data in the database and my tables pull from ODBC tables. Would you even be able to see anything if i posted it? I have 4 tables and need the criteria that has the values i need and also the blanks. For example, employee 0001 has taken 1 of 4 classes. I need to see the classes taken (with the date) and the ones not taken. My "training course" table will consist of the 4 classes I need but each class will need to be included in the header. In an excel pivot table I would put this field in the "column lables" to break out my one field. Beside each name the date of completion would be in the box under the appropriate course. hope this helps some

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You can make tables with the same names as your linked tables and remove the linked tables, then put bogus data in the newer (example) tables.

  8. #8
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    ok ill try that and get repost

  9. #9
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I have my headers as

    employee number
    employee first name
    "" last name
    promostat
    grade
    PCD - FROM COURSE NAME FIELD
    PCL - FROM COURSE NAME FIELD
    WHARTON - FROM COURSE NAME FIELD
    FINANCE - FROM COURSE NAME FIELD
    JOB TITLE
    DIV1

    Like I said earlier, with a excel pivot table I could put course name in column labels and have it split out each section...

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Is there any chance you can post a 2003 version, I do not have access to 2007 all the time.

  11. #11
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    yeah ill do that shortly..

  12. #12
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    here is the 03 version

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Example is missing the table HRP Data and missing a lot of the fields you're using in your original query. There are also field names that are not spelled the same in the query as they appear on the table (primarily using _ as a space in the query where there is only a space on the table.

    Again hard to tell what's going wrong because even though I put in your fields that are incorrectly typed or missing on your tables I have nothing in them for comparison, I assume supervisor is coming from your Humanic_CPO_Emp_All table and I'm also assuming that the table driving your query is your Humanic_ejobsal and you are trying to find related information in the other tables that relates to this table? If so you will likely have to use right join/left join statements like this

    Code:
    SELECT HUMANIC_EJOBSAL.ENUMBER, HUMANIC_EJOBSAL.FIRSTNAME, HUMANIC_EJOBSAL.LASTNAME, HUMANIC_EJOBSAL.PROMOSTAT, HUMANIC_CPO_EMP_ALL.GRADE, HUMANIC_EMPTRAIN_MASTER.STATUS_DATE, HUMANIC_TRAINING_COURSE.[COURSE NO], HUMANIC_TRAINING_COURSE.[COURSE NAME], [HRP DATA].HRP, HUMANIC_CPO_EMP_ALL.[JOB TITLE], HUMANIC_CPO_EMP_ALL.DIV_1_DESC
    FROM [HRP DATA] RIGHT JOIN ((HUMANIC_EJOBSAL LEFT JOIN HUMANIC_CPO_EMP_ALL ON HUMANIC_EJOBSAL.SSN = HUMANIC_CPO_EMP_ALL.SSN) LEFT JOIN (HUMANIC_TRAINING_COURSE RIGHT JOIN HUMANIC_EMPTRAIN_MASTER ON HUMANIC_TRAINING_COURSE.[COURSE NO] = HUMANIC_EMPTRAIN_MASTER.[COURSE NO]) ON HUMANIC_EJOBSAL.SSN = HUMANIC_EMPTRAIN_MASTER.SSN) ON [HRP DATA].SUPERVISOR = HUMANIC_EJOBSAL.SUPERVISOR
    WHERE (((HUMANIC_EJOBSAL.PROMOSTAT) In ('EMT','D10')) AND ((HUMANIC_TRAINING_COURSE.[COURSE NO]) In ('LD518','LD519','LD520','LD521'))) OR (((HUMANIC_EJOBSAL.PROMOSTAT) In ('EMT','D10')) AND ((HUMANIC_TRAINING_COURSE.[COURSE NO]) Is Null));

  14. #14
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    i thought i could just add those fields later.. they are sensitive data.

    Yes, the EJOBSAL is the driver as it determines D10, EMT... Do i need to redo it to account for all the data? I will try your code and see if it works.

  15. #15
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    In your query execution did you see all 5 records in your data, 4 with completions and 1 without any?

Page 1 of 4 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