Hello all,
I have spent 2 hours on friday and now 3 hours today on trying to figure out how to get an organizational query working.
We have a current employee table that has the following fields
Pos Cd
Person Id
Person Nm
Reports To
Reports To ID
Reports Name
The need is to have the Reports to employees on the form. There are 4 controls on the form that at the moment are being manually entered with the information. But recently the data has been added to the daily download table. Now we would like to use the data in the form.
The 4 controls are below and are the order of Reports hierarchy with Dept Head being at the top.
Dept Head
Asoc/Asst Dir
Supervisor/Manager
Suerpvisor/Asst Sup/Lead
Not all employees have all 4 fields with a reports to. So some employees only report to the Executive VP and some have only 3 reports to. and some only have 2 reports to.
My research led me to a query called a recursive relationship query. So I had joined recurring table to each other but the results seem to be correct for some but the employees with only 1 or 2 reports to have repeated Reports to.
Here is my query at this point. It is mostly correct but there are some anomalies that are strange. Like on some employees the Dept head, Asoc Asst Dir and Supervisor-manager is repeated. Is there a way to put <Not Applicable> if it is not populated?
SELECT [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES_5].ReportsToName, [R&D-CURRENTEMPLOYEES_1].ReportsToName AS [Supervisor-Asst-Sup-Lead], [R&D-CURRENTEMPLOYEES_2].ReportsToName AS [Supervisor-Manager], [R&D-CURRENTEMPLOYEES_3].ReportsToName AS [Asoc-Asst Dir], [Dept Head].ReportsToName AS [Dept Head]
FROM [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_5] INNER JOIN (((([R&D-CURRENTEMPLOYEES] LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_1] ON [R&D-CURRENTEMPLOYEES].ReportsToID = [R&D-CURRENTEMPLOYEES_1].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_2] ON [R&D-CURRENTEMPLOYEES_1].ReportsToID = [R&D-CURRENTEMPLOYEES_2].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_3] ON [R&D-CURRENTEMPLOYEES_2].ReportsToID = [R&D-CURRENTEMPLOYEES_3].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [Dept Head] ON [R&D-CURRENTEMPLOYEES_3].ReportsToID = [Dept Head].[Person Id]) ON [R&D-CURRENTEMPLOYEES_5].ReportsToID = [R&D-CURRENTEMPLOYEES].ReportsToID
GROUP BY [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES_5].ReportsToName, [R&D-CURRENTEMPLOYEES_1].ReportsToName, [R&D-CURRENTEMPLOYEES_2].ReportsToName, [R&D-CURRENTEMPLOYEES_3].ReportsToName, [Dept Head].ReportsToName;
FROM [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_5] INNER JOIN (((([R&D-CURRENTEMPLOYEES] LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_1] ON [R&D-CURRENTEMPLOYEES].ReportsToID = [R&D-CURRENTEMPLOYEES_1].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_2] ON [R&D-CURRENTEMPLOYEES_1].ReportsToID = [R&D-CURRENTEMPLOYEES_2].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [R&D-CURRENTEMPLOYEES_3] ON [R&D-CURRENTEMPLOYEES_2].ReportsToID = [R&D-CURRENTEMPLOYEES_3].[Person Id]) LEFT JOIN [R&D-CURRENTEMPLOYEES] AS [Dept Head] ON [R&D-CURRENTEMPLOYEES_3].ReportsToID = [Dept Head].[Person Id]) ON [R&D-CURRENTEMPLOYEES_5].ReportsToID = [R&D-CURRENTEMPLOYEES].ReportsToID
GROUP BY [R&D-CURRENTEMPLOYEES].[Person Nm], [R&D-CURRENTEMPLOYEES_5].ReportsToName, [R&D-CURRENTEMPLOYEES_1].ReportsToName, [R&D-CURRENTEMPLOYEES_2].ReportsToName, [R&D-CURRENTEMPLOYEES_3].ReportsToName, [Dept Head].ReportsToName;
Could someone point me in the correct direction on possibly the joins or something else.
Thank You
Jim Wagner
__._,_.___
Posted by: luvmymelody@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar