Use the Second Max Value in a Formula????

[Joseph]

I have looked thru the forum to find this answer and all I can see is how to
pull more that one amount of max values to be displayed in a report. I need
to actually use the top two values(dates) from one table to pull the records
from another table. This is what I currently have:

SELECT TrainingCourses.TrnCrsID, TrainingAttending.Staff_Id,
TrainingTopics.Topic, TrainingCourses.DTGoCourse, TrainingCourses.TrnTpId
FROM ([Staff Query] INNER JOIN TrainingReportStaffDatesCrosstab ON [Staff
Query].StaffId = TrainingReportStaffDatesCrosstab.StaffId) INNER JOIN
(TrngInstructors INNER JOIN ((TrainingCourses INNER JOIN TrainingAttending ON
TrainingCourses.TrnCrsID = TrainingAttending.TrnCrsID) INNER JOIN
TrainingTopics ON TrainingCourses.TrnTpId = TrainingTopics.TrnTpId) ON
TrngInstructors.TrnInstruId = TrainingCourses.CrsInstru) ON [Staff
Query].StaffId = TrainingAttending.Staff_Id
WHERE (((TrainingCourses.DTGoCourse) Between
[TrainingReportStaffDatesCrosstab]![TJPC Certification] and
DateAdd("d",730,[TrainingReportStaffDatesCrosstab]![TJPC Certification]))
ORDER BY TrainingAttending.Staff_Id;


Yes, it did work when I was getting the day that the certification started
and then I could add two years to the date. The policy has changed and now I
need to be able to pull the training records from the previous expiration
date to the current expiration date. Which messed up the "Where" clause.

Let me explain the tables/queries:

TrainingReportStaffDatesCrosstab pulls dates from the Table StaffDates that
pivot the types from the DateType table(Hire Date, Termination Date,
Certification Date, etc...).
Don't worry about the TrainingInstructors table, it just has a list of all
the training instructors.
TrainingCourses is as it says with information about the course to include
the training topic id (from table TrainingTopic (Topics that have to deal
with Certification that are repeated several times)), date of the course,
instructor id (from table TrainingInstructor(same topic, different instructor
and date)), along with some other info.
TrainingAttending is the table that has the StaffID (from table Staff),
TrCrID (from table TrainingCourses), and a memo.

How can I actually use the previous expiration date and the current
expiration date?

 

[Joseph]

Sorry I forgot to ask:

Would I need to make two separate queries use the values?

 

[John Spencer]

Generically,
SELECT SomeOtherField, Max(SomeDate) as Previous2
FROM SomeTable
WHERE SomeDate<
(SELECT Max(SomeDate) as Previous1
FROM SomeTable as TEMP
WHERE Temp.SomeOtherField = SomeTable.SomeOtherField)
GROUP BY SomeTable.SomeOtherfield



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Joseph]

Ok I will give it a try.

 

[Joseph]

I got it modifying your query as annotated. Thanks.

SELECT StaffDates.StaffId, Max(StaffDates.DTG) AS MaxOfDTG
FROM TrainingReportStaffDatesCrosstab INNER JOIN StaffDates ON
TrainingReportStaffDatesCrosstab.StaffId = StaffDates.StaffId
WHERE (((StaffDates.DTG)<[trainingReportstaffdatescrosstab].[tjpc cert
expires]))
GROUP BY StaffDates.StaffId;