Creating a History Report and using Date Last function 1

[kmccoy1970]

Hi there:
I'm trying to create a history report where I show all of the classes a user has taken. There will be times when they will duplicate the training because we have to take some classes every X months.

When someone has taken 2 or more of the same class, on the LAST class that they took, I want to reflect the NEXT date the class is required by. So if a class has a 24mth frequency and they took the class in 3/25/09, I want to show a future class of 3/25/11 (i don't care about weekends at this point!)But I still want to retain the other classes that they've taken in the past.

I've created a history query:
SELECT qryAllEmployeeHistory.CategoryName, qryAllEmployeeHistory.[Employee ID], qryAllEmployeeHistory.Employee, qryAllEmployeeHistory.TrainingName, qryAllEmployeeHistory.CompletedDate, qryAllEmployeeHistory.TrainingFrequency, qryAllEmployeeHistory.IsMandatory
FROM qryFindLastTrainingDate INNER JOIN qryAllEmployeeHistory ON qryFindLastTrainingDate.TrainingID = qryAllEmployeeHistory.TrainingID
WHERE (((qryAllEmployeeHistory.Employee)=[forms]![frmTraining by Employee]![Employee]));


and I've create a query that will show me the LAST date a class was taken:
SELECT Last(qryAllEmployeeHistory.CompletedDate) AS Maxdate, qryAllEmployeeHistory.[Employee ID], qryAllEmployeeHistory.Employee, qryAllEmployeeHistory.TrainingName, qryAllEmployeeHistory.TrainingID
FROM qryAllEmployeeHistory
GROUP BY qryAllEmployeeHistory.[Employee ID], qryAllEmployeeHistory.Employee, qryAllEmployeeHistory.TrainingName, qryAllEmployeeHistory.TrainingID;


and I can't get it to populate a text field that I have set aside in the report to house the future date.

Frustrating, because separately, these queries work off the same parent query, but when I combine the two, it's FUBAR...grrrr

Anyone with some insight as to how I tackle this?

Thanks a bunch...

Katy

 

[Remou]

How about something on the lines of:

SELECT qa.CategoryName, qa.[Employee ID], qa.Employee, qa.TrainingName, 
qa.CompletedDate, qa.TrainingFrequency, qa.IsMandatory, qt.MaxDate, qt.TrainingName
FROM qryAllEmployeeHistory qa 

LEFT JOIN (
    SELECT Max(qa.CompletedDate) AS Maxdate, qa.[Employee ID], qa.TrainingName
    FROM qryAllEmployeeHistory qa
    GROUP BY qa.[Employee ID], qa.TrainingID, qa.TrainingName) As qt

ON qt.TrainingID = qa.TrainingID
WHERE (((qa.Employee)=[forms]![frmTraining by Employee]![Employee]));

It would be better to match the Employee ID rather than Employee on the form, if you have it. That is what IDs are for.

http://lessthandot.com

 

[kmccoy1970]

Well, I thank you very much, but it's not working..I've tried modifying the code but I end up with the same result.(That banging sound is my head hitting my computer screen! )

I'll go back to the drawing board and figure out if there is another way to accomplish this.
I appreciate your help..

Katy

 

[Remou]

When you say not woking, what do you mean? The query is not running? It is not returning the result you want? Any error messages, and so on.

http://lessthandot.com

 

[kmccoy1970]

The query for compiles all employee history is made up of several linked tables and goes like this:
SELECT tblTrainingCat.CategoryName, tblEmployees.[Employee ID], qryEmployees.Employee, tblTrainingDesc.TrainingName, tblTrainingHistory.CompletedDate, tblTrainingDesc.TrainingFrequency, tblTrainingDesc.IsMandatory, tblTrainingHistory.TrainingHistoryID
FROM (tblTrainingCat INNER JOIN tblTrainingDesc ON tblTrainingCat.CategoryName=tblTrainingDesc.CategoryName) INNER JOIN ((tblEmployees INNER JOIN qryEmployees ON tblEmployees.[Employee ID]=qryEmployees.[Employee ID]) INNER JOIN tblTrainingHistory ON tblEmployees.[Employee ID]=tblTrainingHistory.EmployeeID) ON tblTrainingDesc.TrainingID=tblTrainingHistory.TrainingID;

Returns a record for every training every employee took.

The query that I ran that can find the LAST training date for each employee for each training class looks like this:

SELECT Last(qryAllEmployeeHistory.CompletedDate) AS [Last Date], qryAllEmployeeHistory.[Employee ID], qryAllEmployeeHistory.Employee, qryAllEmployeeHistory.TrainingName
FROM qryAllEmployeeHistory
GROUP BY qryAllEmployeeHistory.[Employee ID], qryAllEmployeeHistory.Employee, qryAllEmployeeHistory.TrainingName;

Each query works by themselves and returns the records I want.

Perhaps a better way to do this is to say what I want, and maybe you can tell me if I'm going at this the wrong way.

Here is a view of the End Result I am looking for:
CategoryName Employee ID Employee Training Name CompletedDate Frequency IsMandatory TrainingHistoryID
Safety 17485 Katy McCoy Hand Carrying Liquid Measures (G) 1/20/2006 24 No 1
Safety 17485 Katy McCoy Hand Carrying Liquid Measures (G) 2/21/2008 24 No 7

If the completed date = the LAST Completed Date, I want a field (text?) to populate with the next due date, in this case 2/21/2010.

Is this even possible? I suppose I could use the .visible function if CompletedDate<> Last(completedDate).

As you can probably tell, I know just enough to be dangerous, but not enough to quit my day job!!

 

[PHV]

SELECT C.CategoryName, E.[Employee ID], N.Employee, D.TrainingName
, H.CompletedDate, D.TrainingFrequency, D.IsMandatory, H.TrainingHistoryID
FROM (((tblTrainingCat AS C
INNER JOIN tblTrainingDesc AS D ON C.CategoryName=D.CategoryName)
INNER JOIN tblTrainingHistory AS H ON D.TrainingID=H.TrainingID)
INNER JOIN tblEmployees AS E ON ON H.EmployeeID=E.[Employee ID])
INNER JOIN qryEmployees AS N ON E.[Employee ID]=N.[Employee ID]

SELECT Max(CompletedDate) AS LastDate, [Employee ID], TrainingName
FROM qryAllEmployeeHistory
GROUP BY [Employee ID], TrainingName

SELECT A.CategoryName, A.[Employee ID], A.Employee, A.TrainingName
, A.CompletedDate, A.TrainingFrequency, A.IsMandatory, A.TrainingHistoryID
, IIf(L.LasteDate Is Null,Null,DateAdd('m',A.TrainingFrequency,L.LasteDate)) AS NextDueDate
FROM qryAllEmployeeHistory AS A LEFT JOIN qryFindLastTrainingDate AS L
ON A.[Employee ID]=L.[Employee ID] AND A.TrainingName=L.TrainingName AND A.CompletedDate=L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kmccoy1970]

Worked like a charm!

It's official...I am madly in love with all of you who helped!!

Thanks soooo much!

Katy

p.s. now I have to study the code to see HOW YOU DID IT!!