Newbie - School Database

[canadiandude007]

Hello,

I'm sure this has probably been answered, but I haven't found the answer I was looking for. I need to create a database with students and courses. I would like to have it so that when I enter a student year and semester, the appropriate courses show up in browse mode. Am I doing something wrong?

Example

=======

Let's say I have Jack and Jill. Jack is in year 1, semester 1. Jill is in year 1, semester 2. Both can take 5 courses. How do I setup the relations table so that the proper information is displayed?

What I would like is something that looked like this...

Name: Jack

Year: 1

Semester: 1

Course1: English

Course2: Biology

Course3: Chemistry

Course4: History

Course5: Latin

Name: Jill

Year: 1

Semester: 2

Course1: History II

Course2: Physics

Course3: English II

Course4: Computers

Course5: Geography

I tried first to have a table of each course with its course ID and course name, including a courseNumber to keep track of all the courses. However, I could not setup a relationship in the student table with course1 = courseNumber and course2 = courseNumber and course3 = courseNumber, etc. This ended up with blank spots in browse mode.

I tried setting up the courses table to have year, semester, and course1 through to course5, then link the tables so that "year" under students related to "year" under courses.

This didn't work either. Any ideas??

Thanks in advance.

FileMaker Version: 7

Platform: Windows XP

[stanley]

CanadianDude:

You most likely need three tables. The first two, Students and Courses, you've already got. All the data for a student goes in Students, and all the data for a course goes in Courses. Then you'll need a third one, which you can call "Registrations." In this new database, you'll create a record for each class added to a student's registration. All the data it needs to carry is:

Serial Number

Year

Semester

Student ID

Course ID

Now, you get the Student ID & Course ID numbers for the relationships, and when you go to the student record, you can have a portal showing that student's course load. The reason you've got Year and Semester in there is so that you could keep a record of past courses as well (in which case you'd build a more detailed relationship, but that's another subject.)

-Stanley

[Ender]

I think you're right on Stanley, but I would consider adding a Section table between Registration and Course.

This extra layer of abstraction is not essential but since many courses have multiple sections (times or days), this can save some data entry work. It can also be handy if you want to view all students that have taken a particular course, you go to the Course table and see this (through a portal to Registration.) If the times and days are fields in the Course table, you would not be able to do this as easily.

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[stanley]

Ender:

I agree with you about the Section table, but am implementing it a bit differently. In the system I am working on (very similar to CanadianDude007's) the Section table has an entry for each occurance of the course during the week. Thus, when a student registers for a course, I can build the student's schedule from the data in Section...

-Stanley

[canadiandude007]

Thanks so much for your help