Query with Rows from one table and columns from another (???)

[Keith Sorbo]

I am not sure how to ask this. It relates both to database design as
well as query and sql and form design.

I am designing a grading program. My current approach is to create one
table (tblAssign) for each assignment type with fields
(assignmentid-primary key, courseid-linked to course file,
assignmentdescription, assignmenttotalpoints,
assignmentweightofTotalGrade) This table would have one row for each
assignment in a course

and another table for student grades (tblGrades) with fields
(studentid,courseid,assignmentid,grade). This table would have one row
for each assignment for each student. (I.e. a student would have
multiple entries in this table for a single course)

I want to be able to design a system whereby the teacher can have as
many assignments as desired (assuming weights all add up to 100). Each
would be one row in tblAssign.

Then design a form that will show the course titles from tblAssign as
column titles and each row would be one student and each cell would be
taken from the tblGrades.


Is this a reasonable design? Am I making it too complex? Any other
ideas?

Keith

 

[Duane Hookom]

You are probably looking for a crosstab query. This is somewhat difficult to
code in a form. There are examples of crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.