Column Headings Question

[OnLiner]

Greetings,
I'm not sure how I should handle this, but I have a table with many fields namely "employee" and "supervisor". I want a query/table/form that shows the supervisors as column headings, and the employees listed under the column of the supervisor they are assigned to.

I've queried and googled on this but haven't found a clue yet, i'm brain crampin'...any help is appreciated.

thanks

 

[kimmer7389]

Do you have 2 separate tables? One for employees and one for supervisors?

 

[MajP]

Is your original data like this?

Employee Supervisor

Mike T. Smith
Tom H. Jones
Susan T. Smith
Robert H. Jones
Cathy S. Roberts

And you want this

H.Jones S.Roberts T.Smith
Tom Cathy Mike
Robert Susan

I can not figure a way to get you there in one step.
You can use a cross-tab query to get you close

TRANSFORM First(tblEmpSup.Employee) AS Employee
SELECT tblEmpSup.id
FROM tblEmpSup
GROUP BY tblEmpSup.id
PIVOT tblEmpSup.Supervisor;

Unfortunately your data will look more like this becuase there is nothing to group on.

H.Jones S.Roberts T.Smith
Tom
Robert
Mike
Susan
Cathy

To fix this I personally would make a table from this cross tab, and write the code to move Cathy and Mike up to the first record, and Susan to the second. Hopefully someone has a better approach.
Provide more information on your table/s structure, for a better answer.

 

[Pampers]

Hi OnLiner,
The easiest way to this is to create two tables, tblSupervisor and tblEmployee. Create a 1 to many relationship (1 supervisor has many employees). Create a form with tblSupervisor as source, create a subform in this form with tblEmployee as source. Set Link/Master Fields Properties = SupervisorID. That't it.

The outline of the tables would be:
tblSupervisor
SuperVisorID (primairy key, autonumber)
SuperVIsorName (tekst)

tblEmployee
EmployeeID (primairy key, autonumber
EmployeeName (tekst)
SupervisorID (foreign key, number)

Pampers

you're never too young to learn