Loop Query

fido77777

Registered User.
Local time
Yesterday, 22:46
Joined
Aug 5, 2007
Messages
23
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
 
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).
 
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 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.
 
@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 (y) 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
 
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.
 
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
 

Users who are viewing this thread

Back
Top Bottom