Setting up tables properly

[forest8]

Hi

I have started creating a database and realize that I haven't set it up
properly.

Currently I have about 10 tables in my database. In each table, I have the
Student and Fields included. Some of these tables are currently "Students",
"Contact information" (for emergency contacts), "Needs assessment", "General
Information" (which includes address), "Courses taken".

The reason for this was that I thought I had to create individual tables to
reflect a report. By the way, I have already created some forms for this
database.

When I try to attach a Primary Key to one table and relate it to another
table, I want the actual related information to be included as the words and
not a numeric.

First, when I try to create a relationship between "Students" where the
Primary Key is their Student ID and link it to "Courses taken", I get a
Indeterminate relationship. I understand that a Student only has 1 ID number
but can take many courses.

How do I fix my database at this time?

Thank you in advance.

 

[Gina Whipp]

forest8,

Rule number one - Do NOT set up tables for the sake of reports or forms.
Tables just hold the data and need to be normalized in order to give you the
reports and forms you want. Now that that is out of the way...

You need to fix your tables. Not being exactly sure what you are trying to
track, here's a suggestion of what tables you need. (Sorry for those forms
you created, you will probably have to redo some, if not all, of them.)

tblStudents
sStudentID (PK)
etc...

tblStudentContacts
scStudentID (FK)
scContactTypeID
etc...

tblContactTypes
ctContactTypeID (PK)
ctContactType (Primary, Secondary, etc...)

tblAddresses (I am assuming you need this because a Student can have more
then one address.)
aAddressID (PK)
aStudentID (FK)
etc...

tblCourses
cCourseID (PK)
etc...

tblEnrolledCourses
ecCourseID (FK)
ecStudentID (FK)
etc...


With the tables set up as above, a Student can take many courses and Course
can be taken by many Students. Using FK's (Foreign Keys) in that table
allows for that.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm