J
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?
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?