Re: Re: [MS_AccessPros] Specific Grouping quesion for a report

 

Jim,

That is not what I would have expected from a normalized table structure. I typically have just a ReportsTo field. In your sample data, Jones reports to the emplid of Bird, J.  Smith reports to the emplid of Franks, Art and Franks, Art reports to the emplid of Ballard, M. 

Ballard, M and Bird, J would have a position title of "Dept Head" and Franks, Art would have a position title of "Ast Director". 

However you should be able to use something like the following expression to get the immediate manager/supervisor:

ImmedSup: Nz([Ast sup/lead], Nz([Supervisor],Nz([Ast Director], Nz([Dept Head],"Top Dog"))))

Duane Hookom

(normalization snob)

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, September 5, 2017 5:22 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: Re: [MS_AccessPros] Specific Grouping quesion for a report

 

Duane,

You are correct about the tables description

The position no is employees position no.

the Reports To is the immediate manager, but this should be removed. In the Supervisor table the reports to is hierarchy in this order below. But some fields are not populated. because some empl. do not have all someone to report to in all positions. for example the top boss does not report to a supervisor, or any assistants just the dept head. and some empl.have a dept head and maybe Ast sup/lead.

DeptId    dept name    emplid    sick    comp    vac    last name    first name    Dept head    Ast director    Supervisor    Ast sup/lead

D0828 FACMAN     11111111    305.75    0.0    76.07    Smith    Joseph    Ballard, M    Franks, Art                

D0825    FACMAN    2222222    25.00    10.0    52.05    Jones    Chuck    Bird, J    

Jim Wagner

---

On ‎Tuesday‎, ‎September‎ ‎05‎, ‎2017‎ ‎02‎:‎46‎:‎05‎ ‎PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim,

I was hoping to see something like

[SupervisorTable]  table that stores the organization hierarchy

[Position No] this is the employee position number

[Reports To]   this is their immediate supervisor/manager

Both of the fields should link back to some other table with employee data and position title. Can we assume that an employee can be both a [Position No] and [Reports To]?

Do you have a few records of sample data and desired results?

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, September 5, 2017 1:19 PM
To: luvmymelody@yahoo.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Specific Grouping quesion for a report

 

Duane,

The report is an accruals report, but the users wanted to send the reports to the lowest Reports to for each employee. So I added the table to the query, that we use to show employee Reports to data. The SupervisorTable has the Reports To information. such as ,Dept Head, Asc/Ast Dir, Supervisor, Ast Sup/Lead. The AccrualsForReport has the Accruals accrued YTD. and has ReportsToName is the name, Reports To is the Reports to Position No, ReportsToID is the id of the Reports To

Jim Wagner

---

On ‎Tuesday‎, ‎September‎ ‎05‎, ‎2017‎ ‎10‎:‎14‎:‎09‎ ‎AM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Hello all,

I am trying to create a report that shows the Reports to for Accruals of vac, sick. I have a table that has all of the Reports to from the following

Dept Head

Asc/Ast Dir

Supervisor

Ast Sup/Lead

The problem is that some employees only have a Dept Head and one only has a supervisor. the users would like to have the lowest person on the report to be the grouping header.

Currently I have the following sql statement

SELECT SupervisorTable.[Person Nm], SupervisorTable.[Person Id], AccrualsForReport.sick, AccrualsForReport.comp, AccrualsForReport.vac, AccrualsForReport.[Accrual Proc Dt], AccrualsForReport.[Last Name], AccrualsForReport.[First Name], SupervisorTable.[Ast Sup/Lead], SupervisorTable.Supervisor, SupervisorTable.[Asc/Ast Dir], SupervisorTable.[Dept Head]
FROM SupervisorTable INNER JOIN AccrualsForReport ON SupervisorTable.[Person Id] = AccrualsForReport.Emplid
WHERE (((SupervisorTable.[Person Id]) Not Like "1208509350") AND ((SupervisorTable.[Dept Head]) Is Not Null));

I do not see a clear way of getting the lowest Reports to with so many different variables.

Thank You for any advice

Jim Wagner

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

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

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___