Querying multiple tables

[dom24]

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.

 

[lespaul]

you need to join another instance of the STAFF table to itself joining the ManagerNo to the StaffNo. Here's an example to get you started:

SELECT S1.ManagerNo, S1.FName, S1.LName, S2.StaffNo, S2.FName, S2.LName
FROM STAFF S2
INNER JOIN Staff S1 ON S2.StaffNo = S1.ManagerNo



Leslie

 

[PHV]

I assumed a self-join:
SELECT S.ManagerNo, M.FName, M.LName, V.StaffNo, C.CourseName, V.CourseDate
FROM (dbo_Validation V INNER JOIN dbo_Course C ON V.CourseKey = C.CourseKey)
INNER JOIN (dbo_Staff S INNER JOIN dbo_Staff M ON S.ManagerNo = M.StaffNo) ON V.StaffNo = S.StaffNo
WHERE V.CourseDate <= DateAdd("m",-3,Date())
ORDER BY S.ManagerNo


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

Got an error in my join expression now.

SELECT dbo_Staff.ManagerNo, dbo_Staff.FName, dbo_Staff.LName, dbo_Validation.StaffNo, dbo_Validation.Staffno as MStaffNo, dbo_Staff.LName as MLName, dbo_Staff.FName as MFName, 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 Staff.ManagerNo = MStaffNo)) ON dbo_Staff.StaffNo = dbo_Validation.StaffNo
WHERE (((dbo_Validation.CourseDate)<=DateAdd("m",-3,Date())))
ORDER BY dbo_Staff.ManagerNo;

????????

 

[PHV]

Why not simply try what we suggested you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dom24]

Done it! Lucky that Access practically write the SQL for you!

SELECT dbo_Staff.ManagerNo, dbo_Staff.FName, dbo_Staff.LName, dbo_Validation.StaffNo, dbo_Course.CourseName, dbo_Validation.CourseDate, dbo_Staff_1.LName, dbo_Staff_1.FName
FROM dbo_Staff AS dbo_Staff_1 INNER JOIN (dbo_Staff INNER JOIN (dbo_Validation INNER JOIN dbo_Course ON dbo_Validation.CourseKey = dbo_Course.CourseKey) ON dbo_Staff.StaffNo = dbo_Validation.StaffNo) ON dbo_Staff_1.StaffNo = dbo_Staff.ManagerNo
WHERE (((dbo_Validation.CourseDate)<=DateAdd("m",-3,Date())))
ORDER BY dbo_Staff.ManagerNo;

 

[dom24]

PHV - I don't understand what you suggested to me that's why I didn't try it. Don't get where the C, V M etc come in in the join expression.

 

[PHV]

In the JetSQL help file take a look at alias
You understand dbo_Staff_1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244