Reverse Engineer This Query - Design View & SQL

[bethanysmith]

Hello, I have an Access database that I've inherited that I am now tasked with replacing. I'm supposed to determine what this database is doing and replace it - I'm stuck on a particular query that just doesn't seem to make sense to me. I've attached both the query view and the design view. It's possible that the person who built this had a flaw in their logic, but it's also possible that I just don't get it. Any insight would be appreciated!





SQL View (line and paragraph breaks added for ease of reading):

SELECT
tblsupvtoemp.POSN,
IIf(IsNull([PWD_EXECRPT_PAF_2].[EMPLOYEE_ID]),[PWD_EXECRPT_PAF_1].[EMPLOYEE_ID],[PWD_EXECRPT_PAF_2].[EMPLOYEE_ID]) AS Supervisor_Employee_ID,
IIf(IsNull([PWD_EXECRPT_PAF_2].[LAST_NAME]),[PWD_EXECRPT_PAF_1].[LAST_NAME],[PWD_EXECRPT_PAF_2].[LAST_NAME]) AS Supervisor_Last_Name,
IIf(IsNull([PWD_EXECRPT_PAF_2].[First_NAME]),[PWD_EXECRPT_PAF_1].[First_NAME],[PWD_EXECRPT_PAF_2].[First_NAME]) AS Supervisor_First_Name

FROM
((tblsupvtoemp LEFT JOIN tblsupvtoemp AS tblsupvtoemp_1 ON tblsupvtoemp.[POSN reported to] = tblsupvtoemp_1.POSN) LEFT JOIN PWD_EXECRPT_PAF AS PWD_EXECRPT_PAF_1 ON tblsupvtoemp_1.[POSN reported to] = PWD_EXECRPT_PAF_1.POSN)

LEFT JOIN PWD_EXECRPT_PAF AS PWD_EXECRPT_PAF_2 ON tblsupvtoemp.[POSN reported to] = PWD_EXECRPT_PAF_2.POSN

WHERE (((tblsupvtoemp.[POSN reported to]) Is Not Null) AND ((tblsupvtoemp_1.[POSN reported to]) Is Not Null) AND ((PWD_EXECRPT_PAF_2.EMPLOYEE_ID) Is Null));

[June7]

See if this helps http://allenbrowne.com/ser-06.html

[bethanysmith]

Thank you for your response. I understand the concept of self-joins, but it is some of the deeper logic I don't understand.

I feel like the query is getting the employee information, the supervisor of that employeee's information, and then the next supervisor up as well. At least the joins seem to be going up that next level. But there are some criteria that I don't follow the logic of - the is null and is not null - and then why the query is pulling what seems to be the same supervisor's first and last name with the way the IIF statements are being used.

If anyone is game to totally reverse engineer this query with me, I'm appreciative!

[June7]

Remove the criteria and see what the dataset looks like.

[Rawb]

From what I can see, this query returns:
The position of an employee and who they report to.
The position of that employee (the one the first employee reports to) and who THEY report too.

Employee -> Employee's Supervisor -> Supervisor's Supervisor

[June7]

And what IIF statements?

[Rawb]

It looks like those IIf statements just tell the Query that if the Supervisor's Supervisor can't be found, just use the Employee's Supervisor.

For example: I'm listed as bethanysmith's Supervisor, June7 is listed as my Supervisor, and nobody is listed as June7's supervisor (June7 is The Boss^TM).

You'd see the following

Employee	Supervisor	Supervisor's Supervisor
bethanysmith	Rawb	June7
Rawb	June7	June7