Group records based on string criteria

[stupiet]

To anyone who can help,

I am creating an employee list database.
For this database i want to list employees for each supervisor.
Employees each belong to a department.
Departments belong to supervisors.
Some supervisors have control over several departments.
Also, some supervisors share the same department.
My problem is how do I group all employees under the correct supervisor based on which department they fall under?

I created an employee table that looks like this:

Emplnum | FirstName | LastName | Dept

And then a supervisor table that looks like this:

SVnum | SVname | Deptsview

Under "Deptsview" i was going to create a string of text that contained all departments they look over.
So how do I run a query now that would recognize these departments and display all employees under the departments that belongs to this supervisor? Is this even possible?

Please help. I have no idea where to start.

Thanks!

Stupiet

 

[ThomasLafferty]

Supervisors and employees belong in the same table, and distinguish between them by their job titles. Add a field to your employees table called ReportsTo which looks up employees from your employees table. Create a separate table:

tblAssignmentDetails
DetailID.....Autonumber primary key
Emplnum......Foreign key from your Employee table
             (One to many relationship)
DeptID.......Lookup from your Departments table
             (create one if you don't have one)

In the data entry form (employees) you will have a subform displayed as a datasheet or a continuous subform which you can use to enter/display every department the manager supervises or in which the employee works.

Hope this gets you enough to get started...

Tom

Born once die twice; born twice die once.