What tables do I need?

[Sarah]

I am creating a data base with the following info: Course number, course
name, discipline name, profile name, # students registered, # passed, avg,
grade, I need a report to display # students registered, % passed, and avg.
grade per course on the top( grouped by profile), and the courses grouped by
discipline ( on the left side) How many tables do I need? I understand how to
create the relationships between tables, by I am not sure which tables to
include. Should I create a table for each profile with the # of students
registered, avg. grade and % passed for each profile with the course number
as the primary key? Do I need to create additional tables for courses and
disciplines, names of profiles? Do I need to repeat field info in each of the
tables, and if so, is there an easy way to do so? I am using Access 2003
Please help.
Thanks

 

[Jeff Boyce]

Sarah

Already responded to in another newsgroup.

It's rarely necessary to post the same question to more than one newsgroup.

And the downsides are that you have to work harder to check all the places
you've posted, and we end up duplicating each other's efforts.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

names of fields or things doesn't give us enough to help you. For example,
Is Basket Weaving always the same course number? What is a disipline name?
What is a profile name?
What I can tell you is number of students registered should not be a field
in any table. Same for number passed and average grade. All three of these
values should be calulated when displayed on a from, a report, or if
necessary, a query.

The typical "school" model is something like this:

A table of Students
Primary key - an autonumber field
Should contain demographic information about each student. It should not
contain anything about any courses the student has enrolled in or completed.

A table of Courses Offered.
Primary key - an autonumber field
Should contain information about each course offered. It should not contain
any information about a specific instance of the course. It can contain
prerequisite information.

A table of Classes
Primary key - an autonumber field
Foreigh key - the Primary key of the Courses table
This is one instance of a Course. This is where the dates and times and
location of the class will be stored. You may want to have a field defining
the maximum number of students allowed.

An Enrollment table
Primary key - an autonumber field
Foreign key - The Primary key of the Classes table - (which instance of a
course it is)
Foreign key - The Primary key of the Students table (which student is
enrolled)
Here you would keep the grade, status, and other information about this
student in this class.

Hope this helps a bit, but can't tell you about the other fields you
mentioned until you describe what they mean. That included a description of
the relationship to other data items.

 

[Sarah]

Hi Jeff,

I am sorry. It is the same newsgroup, it is the first time I am doing this.
I responded to your other e-mail.

Thanks

 

[Sarah]

Hi Klatu,

I am trying to create a data base to compare pass rates in different
courses. Courses always have the same number, and therefore, could serve as a
primary key. Students are enrolled in different profiles such as social
science and commerce. Courses are grouped in disciplines. For example,
linear algebra and calculus would be under the discipline of math. I started
creating a table for each profile and I included the following fields: course
number, discipline name. I want the report to display the course numbers for
linear algebra and calculus and group it under the profile math. I would like
the course numbers to be displayed as rows in a report grouped by discipline
math, political science). I can do this. I want the # of students registered
in course, ,avg, grade and % passed ,to be displayed in columns grouped by
profiles (commerce, social science etc). I want to see if the pass rate is
the same for calculus if students are in commerce or in a different profile.
I want to create this data base so each semester, I would just have to change
the info % passed, # of students registered, and average grade,
Thanks for your help and I hope this is clear,