Loop Query

[fido77777]

i have a table with employee details which include column for manager which is employee too

something like that :

empid---- name----- manager
1 ----------x
2---------- y---------- 1
3 ----------z---------- 2
4---------- n---------- 1

i want to make a query which list all employees with manager tree
and put them on a form

there will be calculations done and updates depending on the list created

so i prefer that to be done through VB code

or any other suggestions will be very helpful

thank you

 

[JamesMcS]

Usually if I'm running SQL in VBA, I cheat - design the query as normal, sqitch to SQL view, copy and paste into your VBA. Then you can do a for loop (but why you'd want to do this I don't know from your post).

 

[fido77777]

why i want to do it
let's say z is sales he gets a percent for sales
and y gets a percent
and x percent

coz z works under y who works under x

i need to know that series of levels of management before i calculate sales percent

i wish i am more clear now

and any suggestions or help will be great

 

[The_Doc_Man]

The trick is to use a query to do this. Base the query on the personnel table. In the query grid, top segment, you can add tables. Add the personnel table a second time. This does not create two tables - but it DOES create implied references to the same table twice. this would allow you to create a JOIN between the employee table and itself using the manager employee number field JOIN (second table's) employee number field.

Search this forum for the topic "Self Join" to see a few detailed articles on the subject.

 

[fido77777]

@The_Doc_Man
thank you for your reply
but it is already done
the table and the self join

please check the first post here
and then imagine (z) made an order
i need to run a query to find out that is Z's manager
and run the query again to find that (x) is Y's manager
and so on

from the order's form i can get Z's ID
using self relation i can find Y's id and information

so the BIG trick here
how to make that query run more than once to get all the information of all managers tree

z --> y --> x --> c -- > b and so on

 

[JamesMcS]

It would be separate queries all based on the same table (if correctly constructed). Grouping by whichever manager you like will produce the appropriate level report.

 

[fido77777]

thanks for everyone tried to help me
but i solved the problem myself

1-i created a table with self join
2-entered the first employee id as value in list box on a form
3-used loop statement (do while - loop)
4-within the loop i added a SQL statement to query the manager of the employee
5-within the loop i kept changing the Empid to find next manager
and now i have list box contain the tree of managers
and it is working so fine till now

Private Sub Command4_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Dim emp As Integer
emp = List0.Value
Dim istop As Integer
istop = 0

Do While istop <> 1
Set db = CurrentDb
strSQL = "SELECT * FROM employees where id = " & emp
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
intResult = rs("admin")
List0.AddItem (intResult)
emp = intResult
On Error GoTo 10
Loop
10: istop = 1

rs.Close
db.Close


End Sub

this was the main ideas to get this part of the job done