473,287 Members | 3,286 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Display Managers and Employees Database

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?
Jul 19 '05 #1
6 1463
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" <no**@none.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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?

Jul 19 '05 #2
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_darkfalz.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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" <no**@none.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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?


Jul 19 '05 #3
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

Jul 19 '05 #4
On Tue, 27 Jul 2004 13:21:21 +0100, "JP SIngh" <no**@none.com> wrote:
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?


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=e...ing.google.com

Jeff
Jul 19 '05 #5
Al
Group it as followed.

Dim iOldManagerNo as integer
Dim oEmpRs as RecordSet

Set oEmpRs = oConnection.OpenRecordSet("SELECT * FROM From Employee Order By
ManagerNo")
IF not oEmpRs.EOF

'- First Group.
Print ManagerNo Group

Do While oEmpRs.EOF

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

Loop

End if
oEmpRs.Close
Set oEmpRs = Nothing

Good Luck..!

"JP SIngh" <no**@none.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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?

Jul 19 '05 #6
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.Createobject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "D:\Applications\Holidays12004\Includes\holidays.m db"
Getparents()

Function GetParents()
Set dbRS = Server.CreateObject("ADODB.Recordset")
' 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("empname"))
dbRS.MoveNext
Loop
End Function

Function GetReplies(parentID)
Set dbRS = Server.CreateObject("ADODB.Recordset")
dbRS.Open "SELECT * FROM empprofile WHERE managername = '" & parentid &
"'", conn
Do While Not dbRS.EOF
Response.write "mangername = " & dbRS.Fields("managername") & "
empname = " & dbRS.Fields("empname") & "<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("empname"))
dbRS.MoveNext
Loop
End Function
%>
"Al" <jo*****@msn.com> wrote in message
news:OE*************@TK2MSFTNGP09.phx.gbl...
Group it as followed.

Dim iOldManagerNo as integer
Dim oEmpRs as RecordSet

Set oEmpRs = oConnection.OpenRecordSet("SELECT * FROM From Employee Order By ManagerNo")
IF not oEmpRs.EOF

'- First Group.
Print ManagerNo Group

Do While oEmpRs.EOF

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

Loop

End if
oEmpRs.Close
Set oEmpRs = Nothing

Good Luck..!

"JP SIngh" <no**@none.com> wrote in message
news:ed**************@TK2MSFTNGP10.phx.gbl...
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?


Jul 19 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

32
by: Rich | last post by:
I'm sure it sounds kinda nutty to display 200 columns and 500,000 rows of data. But I have been pulling data from a Lotus Notes database into Sql Server for a while now, but Lotus Notes is...
1
by: yma | last post by:
Hi, I tried to use a listbox to display a column in MS Access 2000 nwind.mdb by using ole DataAdapter, Connection and dataset controls. But I got "It is already opened exclusively by another...
2
by: Cindy | last post by:
Hello. For some reason, I am able to "connect" to the Northwind database whenever I use the wizard; however, when I run the application, my datagrid does not display. Here are the exact steps I...
6
by: broken arrow | last post by:
Hello, I'm facing with a problem though it may look very simple for many of u it is a bit complicated for me So kindly please help me out i have a table emp with employee id,name and mgrid ...
4
by: news.microsoft.com | last post by:
Hi all, Assume we have two entity class. Class1: Name: House Property: ID:int Name:String Desktops:Desktop
5
by: PHPBABY3 | last post by:
Hi, 1. I have two SQL tables. I will call them employees and departments: EMP: LAST_NAME, FIRST_NAME, DEPTNM DEPT: NUM, NAME Input: text string FIND Output: the LAST_NAME, FIRST_NAME...
2
by: remya1000 | last post by:
i'm using VB.NET. i have a table names "EMP". this table contain employee details like Index, empID, First name, Last name, address, phone number etc. while page loads, i need to display Last...
2
by: simonyong | last post by:
hello, anyone.. im newbie to asp.net my problem is : My task is related to view all data from a table called "employee " i had create a datagrid where called data from database and...
2
by: lairpr74 | last post by:
I am trying to update the managers name in active directory with an application I got that updates active directory. I been strugling with this for a few weeks now and couldn't find the best way...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.