Yes I did, what were your desired results?
Yes I did, what were your desired results?
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.
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.
I re-created the data.
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.
I'm using adobe acrobat 5.0 if you could save your pdf in that format I'll have a look
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?
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.
**Correction**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.
The finished product will have 133 records, so I should have at least 133 records.
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).
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.
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?
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;