Creating a complicated query

[Guest]

I am trying to design an application for a class. I need to create a query
that will add all the students in the class and all availible teachers,
devide the student and assign them a teacher. Can anyone help point me in the
right direction?

 

[John W. Vinson]

I am trying to design an application for a class. I need to create a query
that will add all the students in the class and all availible teachers,
devide the student and assign them a teacher. Can anyone help point me in the
right direction?

No, not without some knowledge of your table structures.

Also some clearer definition of the problem would help. How do you "divide the
student"? I guess Solomon proposed using a sword to divide a child, but I
doubt that's what you mean...

John W. Vinson [MVP]

 

[Jamie Collins]

I am trying to design an application for a class. I need to create a query
that will add all the students in the class and all availible teachers,
devide the student and assign them a teacher. Can anyone help point me in the
right direction?

This is one of those things that SQL (and computers generally) sucks
at: making decisions for itself <g>. You have to supply criteria.

You haven't specified how students should be allocated to teachers.
Here's a simple example using northwind to divide employees into three
'classes' using the MOD operator on EmployeeID:

SELECT E2.EmployeeID,
((
SELECT COUNT(*) - 1
FROM Employees AS E1
WHERE E1.EmployeeID <= E2.EmployeeID
) MOD 3) + 1 AS teacher_ID
FROM Employees AS E2;

Jamie.

--