Access database question!

py2006

Hi guys, I hope this is the right section to post this question.

I am about to create a database for work. I will be using Access 2003. The database will contain the following:

A full list of student names
Which course they are doing. (might put these on seperate table?)
A list of assignments for each course.

I would like for it to display what assignments a student has done to date and possible a field saying precentage of assignments completed.

I kind of have an idea how this might go but I can't get my head around how many seperate tables to use and in what way they should be related.

Can anyone advise me on this?

Thanks.

DublinWriter

Tables:

1. Students
2. Student/Course link table
3. Courses
4. Assignments

There's a method you can use called '3rd normal form' for deciding the tables that should be in a system.

I generally just go on common-sense.

py2006

Thanks for that! I was thinking something along those lines myself but was unsure where to set up the relationships etc.

If my assignments table has a list of say 30 assignments and a particular student has done 15 of them, could I have a field that automatically calculates the percentage of assignments done. In this case 50%.

CraggyIslander

students table:

student_id (unique)
student details (name, etc) as seperate fields.

courses table:

course_id (unique)
course name, description, etc

assignment table:
assignment_id
assignment name, description, priority, etc

student-course link:
student_id (links back to student table)
course_id (links back to course table)
unique index on both fields

course-assignment link:
course_id (links back to course table)
assignment_id (link back to assignment table)
unique index on both fields

results table that you'll update when a student has completed an assignment
student_id
course_id
assignment_id
completed y/n

From there you should be able to get all the results that you're after

CraggyIslander

afcourse you can add date of completion in the results table if you want to see who did or didnt do any assignments during a period

py2006

Fred C. Dobbs wrote:

students table:

student_id (unique)
student details (name, etc) as seperate fields.

courses table:

course_id (unique)
course name, description, etc

assignment table:
assignment_id
assignment name, description, priority, etc

student-course link:
student_id (links back to student table)
course_id (links back to course table)
unique index on both fields

course-assignment link:
course_id (links back to course table)
assignment_id (link back to assignment table)
unique index on both fields

results table that you'll update when a student has completed an assignment
student_id
course_id
assignment_id
completed y/n

From there you should be able to get all the results that you're after

Hey, thanks alot for that! That should do the trick!