Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes I did, what were your desired results?

  2. #17
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    that was my desired result. I am still not getting that though.. did you run a simple query?

    I will redo the example to account for all fields and i will make sure the names match.

  3. #18
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I ran the sql statement I had in my previous post, if you cut and paste it into a query SQL window it should run (assuming your tables have spaces in their names and not _ as they appear in the query you attached. Here is a copy of your database back.

  4. #19
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    I re-created the data.

  5. #20
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    i think we almost have it.. I have 2 with no data and 49 records but i should have 133 records.. A record for each employee. Attached is what my finished data looks like when I go the long way to capture the data.. I am needing this to capture my data so i can export to excel and create my pivot to get the classes separate and at the top of the page.

  6. #21
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm using adobe acrobat 5.0 if you could save your pdf in that format I'll have a look

  7. #22
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    One other question, are you looking to basically create a matrix of every possible person and every possible training class and have an indicator on that report of which ones they had completed or not?

  8. #23
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    Quote Originally Posted by rpeare View Post
    One other question, are you looking to basically create a matrix of every possible person and every possible training class and have an indicator on that report of which ones they had completed or not?
    yeah pretty much I guess..

    Not sure how to save a PDF into a previous version.. How about excel in 2003?

  9. #24
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    in this example, are the four fields at the end your classes?

    LBS, PCD, PCL, Wharton?

    Because your CLASSNO in your table HUMANIC_EMPTRAIN_MASTER has classnos that do not correspond to anything in your HUMANIC_TRAINING_COURSE field, and the query doesn't work because you don't have field names that correspond to the field names in your tables.

  10. #25
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    Quote Originally Posted by kwooten View Post
    i think we almost have it.. I have 2 with no data and 49 records but i should have 133 records.. A record for each employee. Attached is what my finished data looks like when I go the long way to capture the data.. I am needing this to capture my data so i can export to excel and create my pivot to get the classes separate and at the top of the page.
    **Correction**

    The finished product will have 133 records, so I should have at least 133 records.

  11. #26
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    Quote Originally Posted by rpeare View Post
    in this example, are the four fields at the end your classes?

    LBS, PCD, PCL, Wharton?
    Yes, that is what is indicated by LD518, LD519, LD520 and LD521

  12. #27
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I am guessing at things now because you're posting a database where the field names of the query and the tables don't match and when I do get the query working by changing field names the linking between tables is broken (or in serious error).

  13. #28
    kwooten is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    255
    i redid the entire database with new tables that would match. Post #19.. dont worry about HRP DATA I will add that later, it is just the names to the supervisor values.

  14. #29
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Neither post 19 or nor post 23 have a query that has fully correct field names and links. If you open your sample databases and try to run the query don't you get prompted for field names? I do, and when I examine the design there are references to fields that do not exist in your database. This is causing (I think) a breakdown in your links that I can't fathom. If you could fix one of your sample databases so that I can just open it and view the design of the query with your intended links intact that would be helpful

    It also looks like a person can take the same course multiple times, are you just trying to pull their most recent STATUS_DATE from the HUMANIC_EMPTRAIN_MASTER table?

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

Page 2 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