Relationship problem

[tanha]

Hi,
I want to create a database for the staff of a company, the attributes of the staff are (Code, FirstName, LastName, ContactNo, DateOfBirth, Position, Department and so on), now many staff has one SuperVisor, and that SuperVisor, maybe other SuperVisors has a SuperVisor, and again that SuperVisor has SuperVisor...

How many tables do I need?

 

[pbaldy]

Does this help?

http://allenbrowne.com/ser-06.html

 

[tanha]

thnx for quick replying,

may I request you just attach a sample?

 

[pbaldy]

Dive in and post if you run into trouble.

 

[tanha]

Thnx again

 

[tanha]

Hi again everybody,
I could not arrange and make the tables, so I attached everything...any help ang guide would be appreciated.

Thanks

 

[pbaldy]

You would need a supervisor field, which would be populated with the employee code of the supervisor of each person.

 

[tanha]

Thanks for replying,
Then How can I provide relationship, I mean many staff may have one supervisor, and that supervisor and maybe another supervisor again has one supervisor...

May I request you be in detail plz?

 

[pbaldy]

As long as any given person can only have one supervisor, that one table with a self-join as described in the link.

 

[tanha]

Look I did as you mentioned, and link u posted, but:

1. is it possible to enter the last supervisor which does not have supervisor here

2. Then in this format, we should enter first all the sub-staff, then sub-staff supervisor, ...and at last the big-supervisor, because I want the supervisor field as a lookup, which shows the firstname and lastname, and instead stores the code.

3. If the relation is correct, then why when I Expand the sub-datasheet, then again it has sub-datasheet, and again that have another sub-datasheet and the same...

4. When the data increased, and more staff entered, How can I filter and show all the staff of a specific supervisor?

Thanks much for ur quick respond and help...

 

[pbaldy]

Does this query help?

SELECT tblPersonnel.Code, tblPersonnel.FirstName, tblPersonnel.LastName, tblPersonnel.Supervisor, tblPersonnel_1.FirstName AS SupFirst, tblPersonnel_1.LastName AS SupLast
FROM tblPersonnel LEFT JOIN tblPersonnel AS tblPersonnel_1 ON tblPersonnel.Supervisor = tblPersonnel_1.Code;

 

[tanha]

Thanks for the code...
If I dont provide selfjoin, and just add a field Named "Supervisor" which keep the staff code, and then use the following query

SELECT * FROM tblPersonnel WHERE Supervisor = 'Criteria';

then what is the difference???

 

[pbaldy]

If that's all you want, none at all.

 

[tanha]

No, Just I want to know, if Use like I mentioned before, then what is the difference...I want to know more...