Re: [MS_AccessPros] Recursive query question

 

Hi Jim,

If you are still having problems, could you share a database with sample data and the query so we can see what isn't working right, and experiment?

by the way, it is not a good idea to have special characters such as & in names -- and all capital letters is hard to read -- better to mix the case. You can change the case of the table names without affecting anything, and that would make the SQL easier to read. When using aliases, don't use space. For instance, as DeptHead. Personally, I'd use aliases like "e1", "e2", ... to focus more on the hierarchy

~ crystal

On 11/5/2018 12:08 PM, luvmymelody@yahoo.com [MS_Access_Professionals] wrote:

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;

Could someone point me in the correct direction on possibly the joins or something else.

Thank You

Jim Wagner

__._,_.___

---

Posted by: crystal 8 <strive4peace2008@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

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.

---

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___