Hi,
I have 2 tables, staff and validation.
Staff table has the fields STaffNo, LName, FName & ManagerNo.
Validation has the fields StaffNo, CourseDate, etc....
I want to create query so that it displays the managerno, their LName and FName based on the staffno in the validation table, for all records where the coursedate is less than or equal to today's date minus 3 months.
So far i've got this:
SELECT dbo_Staff.ManagerNo, dbo_Staff.FName, dbo_Staff.LName, dbo_Validation.StaffNo, dbo_Course.CourseName, dbo_Validation.CourseDate
FROM dbo_Staff INNER JOIN (dbo_Validation INNER JOIN dbo_Course ON dbo_Validation.CourseKey = dbo_Course.CourseKey) ON dbo_Staff.StaffNo = dbo_Validation.StaffNo
WHERE (((dbo_Validation.CourseDate)<=DateAdd("m",-3,Date())))
ORDER BY dbo_Staff.ManagerNo;
This works well with regards to the dates, but instead of pulling back the Manager's LName and FName it pulls back the staff's LName and FName. The manager number and staff number is there, but I don't want to see the staff names, I want the manager names.
any ideas???
By the way, all the managerno's are also staffno's.
Thanks.
I have 2 tables, staff and validation.
Staff table has the fields STaffNo, LName, FName & ManagerNo.
Validation has the fields StaffNo, CourseDate, etc....
I want to create query so that it displays the managerno, their LName and FName based on the staffno in the validation table, for all records where the coursedate is less than or equal to today's date minus 3 months.
So far i've got this:
SELECT dbo_Staff.ManagerNo, dbo_Staff.FName, dbo_Staff.LName, dbo_Validation.StaffNo, dbo_Course.CourseName, dbo_Validation.CourseDate
FROM dbo_Staff INNER JOIN (dbo_Validation INNER JOIN dbo_Course ON dbo_Validation.CourseKey = dbo_Course.CourseKey) ON dbo_Staff.StaffNo = dbo_Validation.StaffNo
WHERE (((dbo_Validation.CourseDate)<=DateAdd("m",-3,Date())))
ORDER BY dbo_Staff.ManagerNo;
This works well with regards to the dates, but instead of pulling back the Manager's LName and FName it pulls back the staff's LName and FName. The manager number and staff number is there, but I don't want to see the staff names, I want the manager names.
any ideas???
By the way, all the managerno's are also staffno's.
Thanks.