Recursive SQL problem on Access

[parkers]

Please help !!

We are currently developing a company database on Access 2000 ... in our "employee" table we have a recursive relationship between supervisors and employees (I guess a 1 to many relationship) ...

The db Table "employee" can be summarised as follows:

empNo
empName
position
dept
supervisorNo
(for simplicity position enumerates to "Associate" or "Manager" ... in reality the hierarchy is much more complicated than this however anything above two positions is way too difficult for us to contemplate at the moment!!)

Background:
We have not yet developed the form interface and are working with tables only ... we have set up all managers and departments (with supervisorNo set to Null) ... and are at the stage of entering the employees.

Problem:
When entering the Associate employees we want the supervisor selection to auto-fill or restrict the supervisor list depending on department selected. Unfortunately all managers are displayed which could lead to an associate being managed by someone in another department (if data entered incorrectly)!!

This is where we've got with our SQL ...

SELECT E1.empNo
FROM employee AS E1, employee AS E2
WHERE ((E1.[dept] = E2.dept) AND (E2.position='Manager'));

Are we on the right tracks?? Any help or guidance would be really appreciated...
SP

 

[KenReay]

Hi

You say you have not yet developed the form interface, but you describe SQL to give a list of possible managers, so I assume you are trying the set the 'Lookup' in table design ?

My advice is stop right there, whilst you are as you say 'on the right tracl', it will be much easier to do this in the Form, using ComboBoxes and queries with appropriate criteria. Since no self respecting Access databse allows users to interact directly with the tables, I cannot see the point in investing time and effort in this area, when it can be better done in a form

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cmmrfrds]

A recursive relationship in a table is difficult to manage in SQL. Joe Celko, an SQL guru, published a paper on this a few years ago. Unfortunately, I don't remember the source/title of the article. He used a different approach to manage the recursion and it was open ended on the hierarchy. Perhaps you could find this article by author name and recursive on the internet. It would require a restructuring of your table(s) that maintain the recursive relationship.

 

[cmmrfrds]

I did a quick look on the internet and found a reference to start from. It talks about the "nested set model" as opposed to the "adjacency model" that you are probably attempting.

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html