Functions for results of preceeding queries

[Cougar50]

I have a table with employee number, employee name and superior number (which is the employee number of the superior).

I have to find for who on employee is the superior:

SELECT Numéro, Nom, Supérieur
FROM tblEmployés
WHERE Supérieur =10

pretty easy..

but then I have to find who are the employees of the employees of employee number 10,

then who are the employees of the employees of the employees of employee number 10 ,

and finally the employees that are not superiors of anyone...

Can someone help...as fast as possible please.

[FishVal]

I have a table with employee number, employee name and superior number (which is the employee number of the superior).

I have to find for who on employee is the superior:

SELECT Numéro, Nom, Supérieur
FROM tblEmployés
WHERE Supérieur =10

pretty easy..

but then I have to find who are the employees of the employees of employee number 10,

then who are the employees of the employees of the employees of employee number 10 ,

and finally the employees that are not superiors of anyone...

Can someone help...as fast as possible please.

Hi!

You have a self-referencing table where [Numéro] is a primary key, and [Supérieur] is based on it foreign key. Am I right?

If so I will be glad to suggest you example of VB function to recurcievely retrive employees.
If not so plz clarify table-to-table relationships.

P.S. By the way, you can visually browse them in Datasheet/Subdatasheet table view.

[Cougar50]

yes you are right, it is only one table... self referencing... there is no link to anything else, any other tables... the primary key is numéro, it is unique and it goes from 1 to 10 552, the second column is the name, and the third column is the supérieur, which is the employee (numéro) who is the supérieur for every employee (can be similar or different from one employee to another).

please give me ur example for the 3 queries i had in the earlier post.

thanks a lot...

[FishVal]

yes you are right, it is only one table... self referencing... there is no link to anything else, any other tables... the primary key is numéro, it is unique and it goes from 1 to 10 552, the second column is the name, and the third column is the supérieur, which is the employee (numéro) who is the supérieur for every employee (can be similar or different from one employee to another).

please give me ur example for the 3 queries i had in the earlier post.

thanks a lot...

Well I can suggest you the following

1. Create table "tblTarget" with the following fields
Nom (Text type)
Numero (Long number type)
Level (Integer number type)

2. Place the code below to a public module

Public Sub RetrieveEmployes(lngNumero As Long, _
intStartLevel As Integer, _
intEndLevel As Integer)
Dim strSQL As String
Dim RS As New ADODB.Recordset
 
With RS
.Source = "SELECT Numero, Nom, Superieur FROM tblEmployes;"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Filter = "Superieur = " & lngNumero
.Open
 
End With
 
With DoCmd
.SetWarnings False
.RunSQL "DELETE tblTarget.* FROM tblTarget;"
LevelDown 1, intStartLevel, intEndLevel, RS
.SetWarnings True
End With
 
Set RS = Nothing
End Sub
Private Sub LevelDown(ByVal intLevel As Integer, _
ByVal intStartLevel As Integer, _
ByVal intEndLevel As Integer, _
ByRef RS As ADODB.Recordset)
 
Dim rsClone As ADODB.Recordset
 
With RS
 
While Not .EOF
 
If intLevel >= intStartLevel And intLevel <= intEndLevel Then _
DoCmd.RunSQL "INSERT INTO tblTarget ( Nom, Numero, [Level] )" & _
" SELECT '" & ![Nom] & "', " & ![Numero] & ", " & _
intLevel & ";"
 
Set rsClone = .Clone
rsClone.Filter = "Superieur=" & ![Numero]
LevelDown intLevel + 1, intStartLevel, intEndLevel, rsClone
 
.MoveNext
Wend
 
End With
 
Set rsClone = Nothing
 
End Sub
 
 

3. Now you can call RetrieveEmployes using the following syntax
RetrieveEmployes Numero, StartLevel, EndLevel
it will write to table "tblTarget" employee name, number and level of subordination for all employes of employee having number Numero with subordination level from StartLevel, EndLevel


Example

RetrieveEmployes 10, 3, 3
will retrieve employes of employes of employes of employee 10

RetrieveEmployes 10, 1, 2
will retrieve (employes and employes of employes) of employee 10

It works fine with small table I've tried. With large table it may be time consuming.

[Cougar50]

Well I can suggest you the following

1. Create table "tblTarget" with the following fields
Nom (Text type)
Numero (Long number type)
Level (Integer number type)

2. Place the code below to a public module

Public Sub RetrieveEmployes(lngNumero As Long, _
intStartLevel As Integer, _
intEndLevel As Integer)
Dim strSQL As String
Dim RS As New ADODB.Recordset
 
With RS
.Source = "SELECT Numero, Nom, Superieur FROM tblEmployes;"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Filter = "Superieur = " & lngNumero
.Open
 
End With
 
With DoCmd
.SetWarnings False
.RunSQL "DELETE tblTarget.* FROM tblTarget;"
LevelDown 1, intStartLevel, intEndLevel, RS
.SetWarnings True
End With
 
Set RS = Nothing
End Sub
Private Sub LevelDown(ByVal intLevel As Integer, _
ByVal intStartLevel As Integer, _
ByVal intEndLevel As Integer, _
ByRef RS As ADODB.Recordset)
 
Dim rsClone As ADODB.Recordset
 
With RS
 
While Not .EOF
 
If intLevel >= intStartLevel And intLevel <= intEndLevel Then _
DoCmd.RunSQL "INSERT INTO tblTarget ( Nom, Numero, [Level] )" & _
" SELECT '" & ![Nom] & "', " & ![Numero] & ", " & _
intLevel & ";"
 
Set rsClone = .Clone
rsClone.Filter = "Superieur=" & ![Numero]
LevelDown intLevel + 1, intStartLevel, intEndLevel, rsClone
 
.MoveNext
Wend
 
End With
 
Set rsClone = Nothing
 
End Sub
 
 

3. Now you can call RetrieveEmployes using the following syntax
RetrieveEmployes Numero, StartLevel, EndLevel
it will write to table "tblTarget" employee name, number and level of subordination for all employes of employee having number Numero with subordination level from StartLevel, EndLevel


Example

RetrieveEmployes 10, 3, 3
will retrieve employes of employes of employes of employee 10

RetrieveEmployes 10, 1, 2
will retrieve (employes and employes of employes) of employee 10

It works fine with small table I've tried. With large table it may be time consuming.

Thanks a lot... it works... my table is 10 552 units but still works fine... Thanks...