Using Joins 3

[NFI]

Hiya,

bear with me here...

I have four tables:

Student
student_id
fName
sName

Course
course_id
title

Course_Student
course_id
student_id

Mark
student_id
assignment_id
mark

Mark is a table containing the marks students have got for various assignments they have been set; each student will have several entries in it.

I'm trying to populate a list on a form like this:

student_id | fName | sName | <assign 1 mark> | <assign 2 mark> | ... | <assign n mark>

So far, I've put together the following query to pull the relevent students out of my database:

SELECT student.student_id, student.fName, student.sName
FROM student
INNER JOIN course_student
ON student.stuudent_id = course_student.student_id
WHERE course_student.course_id = 1;

where my course id would be 1 (I'm actually passing this in from a combobox on the form).

I'm not entirely sure how to get my query to pull out the marks for each assignment, but I thought I'd try this for starters:

SELECT student.student_id, student.fName, student.sName, mark.mark
FROM student
LEFT JOIN mark
ON student.student_id = mark.student_id
INNER JOIN course_student
ON student.stuudent_id = course_student.student_id
WHERE course_student.course_id = 1;

but, no matter how I try and word this, Access keeps throwing a syntax error: Missing operator in query expression 'student.student_id = mark.student_id INNER JOIN course_student ON student.student_id = course_student.student_id'.

So, here's my question, then; why is this throwing a syntax error?

Here's a second question, in case anybody's feeling very helpful; how can I populate my list with what I want in it?


Many thanks for reading all of this

Paul

 

[lespaul]

You have several issues going on here. I would recommend reading Understanding SQL Joins to help with joins and what they do. The LEFT join you added to your second query will pull all students regardless of whether they have a mark in the specified course.

I would suggest you look into a CrossTab Query.

You would need to use an INNER JOIN in this query and then convert to a crosstab. There's also a cross tab wizard you can use (when you select NEW QUERY).

SELECT student.student_id, student.fName, student.sName, mark.assignmentid, mark.mark
FROM student
INNER JOIN mark
ON student.student_id = mark.student_id
INNER JOIN course_student
ON student.stuudent_id = course_student.student_id
WHERE course_student.course_id = 1;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[NFI]

Thanks for that - I am kind of floundering a bit here

All the best,

Paul

 

[NFI]

Hi again,

right, I've tried the above query, but it too throws syntax errors, complaining about missing operators...

I've had a go at putting together a CrossTab query and this is actually producing most of the data I want, but it's messy:


TRANSFORM FIRST(mark.mark) AS FirstMark
SELECT mark.student_id AS [Student ID]
FROM student INNER JOIN mark
ON student.student_id=mark.student_id
GROUP BY mark.student_id
PIVOT mark.assignment_id;


This is pretty good, but I need to be able to replace the column headings with the assignment.title field for which they are the assignment_id. Also, I need to insert two more columns to display the student.fName and student.sName fields which relate to the student_id being shown on each row.

I'm definitely getting there, though.

Thanks for your help

Paul

 

[lespaul]

you would need to join into the table that contains the assignment title to get that information. I believe if you add the fields you need to the query it will perform as expected:

TRANSFORM FIRST(mark.mark) AS FirstMark
SELECT mark.student_id AS [Student ID], student.FName, student.LName, Assignment.Title
FROM student INNER JOIN mark
ON student.student_id=mark.student_id
INNER JOIN Assignment on Mark.AssignmentID = Assignment.Assignment_ID
GROUP BY mark.student_id
PIVOT mark.assignment_id;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

One minor addition

TRANSFORM FIRST(mark.mark) AS FirstMark
SELECT mark.student_id AS [Student ID], student.FName, student.LName, Assignment.Title
FROM [COLOR=red][b]([/b][/color]student INNER JOIN mark
ON student.student_id=mark.student_id[COLOR=red][b])[/b][/color]
INNER JOIN Assignment on Mark.AssignmentID = Assignment.Assignment_ID
GROUP BY mark.student_id
PIVOT mark.assignment_id;

Access loves parens.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[NFI]

Hiya,

thanks again

unfortunately, I think Access has problems with multiple joins, as it's throwing syntax errors about missing operators again...

I'll keep playing with it...

All the best,

Paul

 

[PHV]

Another minor additions

TRANSFORM FIRST(mark.mark) AS FirstMark
SELECT mark.student_id AS [Student ID], student.FName, student.LName[!][s], Assignment.Title[/s][/!]
FROM (student INNER JOIN mark
ON student.student_id=mark.student_id)
INNER JOIN Assignment on Mark.AssignmentID = Assignment.Assignment_ID
GROUP BY mark.student_id[!], student.FName, student.LName[/!]
PIVOT [!]Assignment.Title[/!];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

thanks for stepping in Golom & PHV, DB2 doesn't support crosstab so I was just taking a stab in the dark on the modifications!

les

 

[NFI]

Thanks everybody

That's exactly what I need - I'm going to have a good old look at it now and figure out what's going on

Many thanks,

Paul

 

[NFI]

Hello,

I feel slightly bad coming back to this again, but I've hit another problem with it...

I need to be able to filter the results by course_id, so I modified the query slightly:

TRANSFORM FIRST(mark.mark) AS FirstMark
SELECT mark.student_id, student.fName, student.sName
FROM (student INNER JOIN course_student ON student.student_id=course_student.student_id) INNER JOIN (assignment INNER JOIN mark ON assignment.assignment_id=mark.assignment_id) ON student.student_id=mark.student_id
WHERE (((course_student.course_id)=Forms![Show Marks]!cmbCourse.value))
GROUP BY mark.student_id, student.fName, student.sName
PIVOT assignment.title;

I had to add the Forms![Show Marks]!cmbCourse.value parameter to the Query Parameters list to get the query to run, but now I get this error:

This expression is typed incorrectly or is too complex to be evaluated...

That seems fairly self explanatory, but could anybody suggest how I might simplify my query?

I really appreciate all this help I'm getting, so many thanks for anybody who can spare me a bit of their time

All the best,

Paul

 

[Golom]

I suspect that it doesn't like the way you have constructed the JOINs.

TRANSFORM FIRST(M.mark) AS FirstMark

SELECT M.student_id, S.fName, S.sName

FROM ((Student S INNER JOIN Course_student C ON S.student_id = C.student_id) 
       INNER JOIN Mark M ON S.student_id = M.student_id)
       INNER JOIN Assignment A ON A.assignment_id = M.assignment_id 

WHERE C.course_id=Forms![Show Marks]!cmbCourse.value

GROUP BY M.student_id, S.fName, S.sName

PIVOT A.title;

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]