Microsoft Access Query or Table ???? Please help me.

[roxys]

Hello,

I have a problem and I hope someone could help me with this.

I have a student registration database.
The case is that there is courses that student can register in.
And when they register there a groups formed for the courses. So there may be 2 groups that give the same course depending on how much students register.

The courses are divided in two levels, so there are courses that is level1, and level2.

The level 1 courses are teached in one day. So they are one day course.
The level 2 courses are teached in 5 days. so they are a 5 days course.

The students can only make the exam if they attend to the courses.
For the 1 day course they have to attend 1 time.
For the 5 days course they have to attend at least 3 times.

So I have made a query which I get the list of the students that can make the exams. That was easy.

But now I have to add a mark field for only the students that was allowed to attend to make the exams.

My problem is now that I don't know how to incorporate this table. So I can register the marks for the students that only was allowed to take the exams.
The exams are a one time exam, so that determine if you pass the course.

Please help me.

[almostbob]

add the marks column to the database default blank/fail
enable it with a query that enumerates the same output as the prior attendance query and asks you to update the mark fields, for each row in the result array
can be all records or single records

[roxys]

add the marks column to the database default blank/fail
enable it with a query that enumerates the same output as the prior attendance query and asks you to update the mark fields, for each row in the result array
can be all records or single records

Hi, thanks for your answer, but I have a question,
Could be more specific ?
Should I make a table and add only the marks field in it ? Could you explain that a little more ?

And what do I have to do in the query?

Thanks

[almostbob]

if a student can only attend 1 course, add the column to the student table
if the student can attend more than one course create a table
marks (id,student_id,course_id,mark)

[roxys]

if a student can only attend 1 course, add the column to the student table
if the student can attend more than one course create a table
marks (id,student_id,course_id,mark)

Ok, but I have another question,
Do I have to make a query or how can I do, because I don't know to only insert marks for only the students that are allowed to take the exams ?
Because only the students that are allowed to take the exams are going to get marks.

Can you help me with that now?

[almostbob]

you already have a query that prints a list of the students able to take the exams
ingonring the bad code for the moment (really bad code)
and the poor field names
output a table in a form something like

<?php echo '<table>';
for each $output ++{echo "<tr><td>$lastname</td><td>$firstname</td><td>$coursename</td><td><input 'name='mark' value='$mark'></td></tr>"; }
echo "</table><input type='submit'value='update marks'></form>"; }

and have the form submit to a script(or self) to update table marks with the marks data
it is a thought exercise this code does not work

[roxys]

you already have a query that prints a list of the students able to take the exams
ingonring the bad code for the moment (really bad code)
and the poor field names
output a table in a form something like

<?php echo '<table>';
for each $output ++{echo "<tr><td>$lastname</td><td>$firstname</td><td>$coursename</td><td><input 'name='mark' value='$mark'></td></tr>"; }
echo "</table><input type='submit'value='update marks'></form>"; }

and have the form submit to a script(or self) to update table marks with the marks data
it is a thought exercise this code does not work

Hi, I appreciate you are helping me with my problem.
The php statement that you wrote, do you know how to write the statement in sql, because i use the sql statement.

I hope you can help me, because I am finally seing a light in the horizon.

[almostbob]

I have an course_enrollment table, and I have a group_student table. The group_student table has the students that belong to the groups. But there a students that have enrolled for courses and they has not been placed in a group yet. Do you know how can I make an query in access that can give me the students that have not been enrolled in a group yet.

Sorry to bother you, but you seems to me a good help.

making assumptions
course_enrolment:id unique autoincrement, lastname,firstname, //and others
group_students:id unique autoincrement,group,student_id, //and others where student_id is the foreign key relating the two tables
select from course_enrollment * where id .... is not in the groups table, table joins mess me up
TRY

SELECT * FROM course_enrolment WHERE id NOT IN (SELECT student_id FROM group_student)

in access it would be something like

SELECT course_enrollement.[*],group_student.[id]
FROM course_enrollment INNER JOIN group_student ON course_enrolment.id = group_students.student_id HAVING (((group_students.[student_id]) Is Null));

have used access twice in my life, don't trust this code