Display Managers and Employees Database

[JP SIngh]

Hi All

I am in a very tricky situation wonder if you anyone can help.

We have a table where we store employee information. One of the fields in
the table stores the manager number which is the employee number of the
users manager. Sample data is below

EmpNo Name ManagerNo
2211 Peter 9900
8899 James 9900
9900 Mel 9111
9901 Ian 9111
9111 Simon 9111

I want to able to display all the employees who report to a certain manager
and also all the employees who report to them

Something like

Simon
Ian
Mel
Peter
James

I am sure this can be done but I don't know how to do it recursively. Can
help guys?

[Curt_C [MVP]]

Re: Display Managers and Employees Database

recursive looping.
It's your basic tree building. I have a sample or two on my site but start
with:

https://www.darkfalz.com/1056

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com


"JP SIngh" <none@none.co m> wrote in message
news:edws3Q9cEH A.2236@TK2MSFTN GP10.phx.gbl...[color=blue]
> Hi All
>
> I am in a very tricky situation wonder if you anyone can help.
>
> We have a table where we store employee information. One of the fields in
> the table stores the manager number which is the employee number of the
> users manager. Sample data is below
>
> EmpNo Name ManagerNo
> 2211 Peter 9900
> 8899 James 9900
> 9900 Mel 9111
> 9901 Ian 9111
> 9111 Simon 9111
>
> I want to able to display all the employees who report to a certain[/color]
manager[color=blue]
> and also all the employees who report to them
>
> Something like
>
> Simon
> Ian
> Mel
> Peter
> James
>
> I am sure this can be done but I don't know how to do it recursively. Can
> help guys?
>
>[/color]

[JP SIngh]

Re: Display Managers and Employees Database

Thanks Curt but we don't store the level numbers as in your example of
forums so it is slightly difficult to modify your code to get to our
solution.

WOuld you or anyone else be kind enough to write some code to crack this. We
have been trying to do this for the last 3 months without success.


"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:%23zP4rY9c EHA.3896@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> recursive looping.
> It's your basic tree building. I have a sample or two on my site but start
> with:
> www.darkfalz.com/1056
>
> --
> Curt Christianson
> Owner/Lead Developer, DF-Software
> Site: http://www.Darkfalz.com
> Blog: http://blog.Darkfalz.com
>
>
> "JP SIngh" <none@none.co m> wrote in message
> news:edws3Q9cEH A.2236@TK2MSFTN GP10.phx.gbl...[color=green]
> > Hi All
> >
> > I am in a very tricky situation wonder if you anyone can help.
> >
> > We have a table where we store employee information. One of the fields[/color][/color]
in[color=blue][color=green]
> > the table stores the manager number which is the employee number of the
> > users manager. Sample data is below
> >
> > EmpNo Name ManagerNo
> > 2211 Peter 9900
> > 8899 James 9900
> > 9900 Mel 9111
> > 9901 Ian 9111
> > 9111 Simon 9111
> >
> > I want to able to display all the employees who report to a certain[/color]
> manager[color=green]
> > and also all the employees who report to them
> >
> > Something like
> >
> > Simon
> > Ian
> > Mel
> > Peter
> > James
> >
> > I am sure this can be done but I don't know how to do it recursively.[/color][/color]
Can[color=blue][color=green]
> > help guys?
> >
> >[/color]
>
>[/color]

[Curt_C [MVP]]

Re: Display Managers and Employees Database

The idea is the same, you have a function that passes in a parameter. This
function calls itself from within the loop.
Asking people to write it for you isn't the best way to go about it, trying
and then posting when you get stuck is preferred.

So some searching on Recusion or Recursive calls, and you should find more
on this.

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com

[Jeff Cochran]

Re: Display Managers and Employees Database

On Tue, 27 Jul 2004 13:21:21 +0100, "JP SIngh" <none@none.co m> wrote:
[color=blue]
>Hi All
>
>I am in a very tricky situation wonder if you anyone can help.
>
>We have a table where we store employee information. One of the fields in
>the table stores the manager number which is the employee number of the
>users manager. Sample data is below
>
>EmpNo Name ManagerNo
>2211 Peter 9900
>8899 James 9900
>9900 Mel 9111
>9901 Ian 9111
>9111 Simon 9111
>
>I want to able to display all the employees who report to a certain manager
>and also all the employees who report to them
>
>Something like
>
>Simon
> Ian
> Mel
> Peter
> James
>
>I am sure this can be done but I don't know how to do it recursively. Can
>help guys?[/color]

Start by telling us what database you use. Also, where are you having
the problems, retrieving the data or displaying it?

Besides, a quick Google of the groups probably finds your answer here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=c0d87ec0.0205221050.26339cde%40posting.google.com

Jeff

[Al]

Re: Display Managers and Employees Database

Group it as followed.

Dim iOldManagerNo as integer
Dim oEmpRs as RecordSet

Set oEmpRs = oConnection.Ope nRecordSet("SEL ECT * FROM From Employee Order By
ManagerNo")


IF not oEmpRs.EOF

'- First Group.
Print ManagerNo Group

Do While oEmpRs.EOF

if oEmpRs!ManagerN o <> iOldManagerNo then
iOldManagerNo = oEmpRs!ManagerN o
Print ManagerNo Group
End if
Print EmpNo, Name

Loop

End if
oEmpRs.Close
Set oEmpRs = Nothing

Good Luck..!

"JP SIngh" <none@none.co m> wrote in message
news:edws3Q9cEH A.2236@TK2MSFTN GP10.phx.gbl...[color=blue]
> Hi All
>
> I am in a very tricky situation wonder if you anyone can help.
>
> We have a table where we store employee information. One of the fields in
> the table stores the manager number which is the employee number of the
> users manager. Sample data is below
>
> EmpNo Name ManagerNo
> 2211 Peter 9900
> 8899 James 9900
> 9900 Mel 9111
> 9901 Ian 9111
> 9111 Simon 9111
>
> I want to able to display all the employees who report to a certain[/color]
manager[color=blue]
> and also all the employees who report to them
>
> Something like
>
> Simon
> Ian
> Mel
> Peter
> James
>
> I am sure this can be done but I don't know how to do it recursively. Can
> help guys?
>
>[/color]

[JP SIngh]

Re: Display Managers and Employees Database

Guys

I have tried something but not sure if this is right.

It gives me this error

Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'dbRS.Open'
/admin/man.asp, line 20
Someone please help

Regards

Jas

<%
set conn = Server.Createob ject("ADODB.Con nection")
conn.Provider = "Microsoft.Jet. OLEDB.4.0"
conn.Open "D:\Application s\Holidays12004 \Includes\holid ays.mdb"
Getparents()

Function GetParents()
Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
' ADO code to load all the posts with a null parent
dbRS.Open "SELECT * FROM empprofile order by managername", conn
Do While Not dbRS.EOF
GetReplies(dbRS .Fields("empnam e"))
dbRS.MoveNext
Loop
End Function

Function GetReplies(pare ntID)
Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
dbRS.Open "SELECT * FROM empprofile WHERE managername = '" & parentid &
"'", conn
Do While Not dbRS.EOF
Response.write "mangername = " & dbRS.Fields("ma nagername") & "
empname = " & dbRS.Fields("em pname") & "<br>"
' <-- Your recursive call. Walks down each tree branch until
' it hits the last reply in a tree (dbRS returns no rows), then backs
out.
GetReplies(dbRS .Fields("empnam e"))
dbRS.MoveNext
Loop
End Function
%>


"Al" <jodizco@msn.co m> wrote in message
news:OETS9BAdEH A.712@TK2MSFTNG P09.phx.gbl...[color=blue]
> Group it as followed.
>
> Dim iOldManagerNo as integer
> Dim oEmpRs as RecordSet
>
> Set oEmpRs = oConnection.Ope nRecordSet("SEL ECT * FROM From Employee Order[/color]
By[color=blue]
> ManagerNo")
>
>
> IF not oEmpRs.EOF
>
> '- First Group.
> Print ManagerNo Group
>
> Do While oEmpRs.EOF
>
> if oEmpRs!ManagerN o <> iOldManagerNo then
> iOldManagerNo = oEmpRs!ManagerN o
> Print ManagerNo Group
> End if
> Print EmpNo, Name
>
> Loop
>
> End if
> oEmpRs.Close
> Set oEmpRs = Nothing
>
> Good Luck..!
>
> "JP SIngh" <none@none.co m> wrote in message
> news:edws3Q9cEH A.2236@TK2MSFTN GP10.phx.gbl...[color=green]
> > Hi All
> >
> > I am in a very tricky situation wonder if you anyone can help.
> >
> > We have a table where we store employee information. One of the fields[/color][/color]
in[color=blue][color=green]
> > the table stores the manager number which is the employee number of the
> > users manager. Sample data is below
> >
> > EmpNo Name ManagerNo
> > 2211 Peter 9900
> > 8899 James 9900
> > 9900 Mel 9111
> > 9901 Ian 9111
> > 9111 Simon 9111
> >
> > I want to able to display all the employees who report to a certain[/color]
> manager[color=green]
> > and also all the employees who report to them
> >
> > Something like
> >
> > Simon
> > Ian
> > Mel
> > Peter
> > James
> >
> > I am sure this can be done but I don't know how to do it recursively.[/color][/color]
Can[color=blue][color=green]
> > help guys?
> >
> >[/color]
>
>[/color]