Tables & Forms

[Guest]

First let me explain the problem:
I have a database that holds student information for a school.
Currently, students can take one class. If I add the same student already
entered before into another class, I would see another record add into my
table. That means I have to re-enter the personal info again. To get to the
point, I decided to re-design the database by creating a Many to Many
relationship.

I have the following:

tstudent (table)
studentID PK (I've made this "Autonumber", what do you think? Also
everytime
First name I try to view it thru datasheet, I notice it changes my Last
name
Last name sorting. Any suggestion on how to see the first last name to
receive
SSN 1 from the studentID. I'm not sure if I even should be
concerned?
etc.. Also, in this table I have some of the students twice,
because
they have been enrolled into other courses. Whats your
suggestion?)
tclass (table)
classID PK (Same thing here. I have Autonumber for this field. Also
I'd like to
start date know if I should delet any duplicated records of students?
end date
etc..

tstudentclass (table)
studentID
classID
Grades (I'm not sure if I should put this field in here or the class table?
Please
comment).


Thank you for your help.

 

[John Vinson]

First let me explain the problem:
I have a database that holds student information for a school.
Currently, students can take one class. If I add the same student already
entered before into another class, I would see another record add into my
table. That means I have to re-enter the personal info again. To get to the
point, I decided to re-design the database by creating a Many to Many
relationship.

Good move. Since in reality it IS a many to many relationship - each
student can take many classes (over time), and each class is taken by
many students, your database should model the real world.

I have the following:

tstudent (table)
studentID PK (I've made this "Autonumber", what do you think? Also
everytime
First name I try to view it thru datasheet, I notice it changes my Last
name
Last name sorting. Any suggestion on how to see the first last name to
receive
SSN 1 from the studentID. I'm not sure if I even should be
concerned?
etc.. Also, in this table I have some of the students twice,
because
they have been enrolled into other courses. Whats your
suggestion?)

Autonumbers have one function, and one function ONLY: to provide a
unique meaningless identifier. The value of the autonumber should not
be of any concern - if one student is StudentID 1, another is
StudentID 8323, and another StudentID -3211841231, don't worry; Access
can take care of it.

Secondly, Tables HAVE NO ORDER. A Table is an unordered "bucket" of
data. If you want to see records in Lastname-Firstname order, create a
Query sorting by last name then first name, and base your Form on that
Query.

Finally - don't use blanks in fieldnames. I'd suggest using "camel
case" - e.g. LastName, FirstName.

To get rid of duplicates... get rid of the duplicates. It's best to do
so manually so that you can determine which information in two
almost-duplicate records you actually want to keep.

tclass (table)
classID PK (Same thing here. I have Autonumber for this field. Also
I'd like to
start date know if I should delet any duplicated records of students?
end date
etc..

This I don't understand. tClass should not contain any duplicate
information, nor should it contain any information about students.
Don't you want a class Name field?

tstudentclass (table)
studentID
classID

Ctrl-click both of these fields and then click the Key icon, to create
a joint two-field Primary Key.

Grades (I'm not sure if I should put this field in here or the class table?
Please
comment).

The grade should certainly go here (if not in a fourth table). If you
have 23 students enrolled in Relational Database Design, class 132,
what is the correct value of "grade"? Obviously, the grade depends on
BOTH the class and the student, not on the class itself - so it should
go in tStudentClass, not in tClass. If you give multiple grades per
class (say, for various tests or homework assignments) then you'll
need the fourth table, with one record for each assignment.



John W. Vinson[MVP]

 

[Guest]

Autonumbers have one function, and one function ONLY: to provide a

unique meaningless identifier. The value of the autonumber should not
be of any concern - if one student is StudentID 1, another is
StudentID 8323, and another StudentID -3211841231, don't worry; Access
can take care of it.

***I am encountering a problem. My students table has more records than the
class table. That's obvious, because there are many students per **a**
class.
In my third table (conjunction) I cannot create the two primary keys from
the two tables, because I have many null values in the classID field. What
do you suggest?
Thank you***

Secondly, Tables HAVE NO ORDER. A Table is an unordered "bucket" of
data. If you want to see records in Lastname-Firstname order, create a
Query sorting by last name then first name, and base your Form on that
Query.

Finally - don't use blanks in fieldnames. I'd suggest using "camel
case" - e.g. LastName, FirstName.

To get rid of duplicates... get rid of the duplicates. It's best to do
so manually so that you can determine which information in two
almost-duplicate records you actually want to keep.
*****

 

[Douglas J. Steele]

The third table records the valid combinations of students and classes.

Let's assume you have students Tom, Dick and Harry, and classes A and B. If
Tom takes both classes, Dick takes class A and Harry takes class B, you'd
have 4 records in the third table:

Tom, A
Tom, B
Dick, A
Harry, B

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

But what would I do, if in the student table I have

Tstudent
Tom
Dick
Harry

Tclass
A
B

Tell me how I will be putting my fields into the third table:
tclassstudent
StudentID ClassID
Tom A
Dick B
Harry A

That means I have to manually add all my 800 studentID's to their proper
class in the third table? I just don't get the picture. Theres got to be a
fast way of categorizing the data. What you think? I can understand how my
autonumber from student table can be used. But what should I do for the
classID?
In my class table I have 130 classes. Currently they are asigned a number
for using Autonumber for the ClassID. I'm confused............. PLease draw
me a diagram or something. I'm going crazy here.

 

[Douglas J Steele]

I believe I already pointed you to examples in Northwinds of how you'd do
this.

If you want to assign students to courses, you'd have a form based on the
Class table, and a subform based on the third table. In the Detail of the
subform, you'd have a combobox that returns all of the students, bound to
the StudentId in the third table. That way, when you select a student from
the combobox, that student's ID will be inserted into the third table, and
since the subform is bound to the form, you pick up the ClassId that way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

I have looked at the Northwinds.

In the northwinds third table there is multiple orderID's
OrderID ProductID
101428 queso
101428 cake

What I need to know is since I have already typed the students in the table

What should I do to make mine look like northwinds?
Heres how mine looks:
example
StudentID ClassID
1 Math
2 Biology
3 History

Currently studentID 1 and 2 are both one (same) student

I like to make the table to be like this:
StudentID ClassID
1 Math
1 Biology
2 History

Any suggestions??

 

[Douglas J Steele]

Do you have any way of identifying when you've got more than one entry for
the same student?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

Yes. In the tStudent table I can see some of the students twice by their
last name.

 

[Douglas J Steele]

But in your example of

StudentID ClassID
1 Math
2 Biology
3 History

is there some way that you can automatically identify that 1 and 2 are the
same student?

You need a student table that has 1 entry per student. You need a way to map
every old student id to the new student id. You might have to do the mapping
of old to new ids manually, but then you should be able to do the rest.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

Doug,

Is there a number we can get in touch. I promise I won't take too much of
your time. I'm really close to getting it. You can email me your number if
you wish.
(e-mail address removed).

Thank you Doug.

 

[Douglas J. Steele]

Sorry, no.

 

[Guest]

Doug,

I solved the initial problem of duplicate records. Now the third table is
working fine.
Here is my next question.
I have bunch of of grades (Gr1 to Gr18) that I would like to add. Each
field of Gr represents a test or assignment. However, each class will not be
having the same equal Gr fields. For example Math might require three Gr's
(Gr1 Gr2 and Gr3). I'm thinking to add the Gr1 to Gr18 to my third table
which is the conjunction table that I just finished creating. What do you
think? Will this work? OR Please tell my why I should use a table
dedicated for grades rather than using the third (conjunction) table. And
How? Thank you.

 

[Douglas J. Steele]

Anytime you've got fields with names like Gr1, Gr2, etc, that's a sign of a
repeating field. In terms of Database Normalization, that's a violation of
the First Normal form.

Not normalizing the database can lead to many problems. First, in essence
you've got information "hidden" in the name of the field. Second, it becomes
difficult to do queries against the data (what if you need to know how many
marks over 90 a particular student got, or even to compute the average).
Third, what happens if you end up needing a 19th grade for one course? I
could go on...

You need a fourth table, linked to your third table. The fourth table would
have a row (not a column) for each grade: the primary key would be the three
fields Student Id, Course Id, Grade Number

A maxim many of us like to quote is "Rows are cheap, Fields are expensive".

For more information about normalization, take a look at some of the
resources Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Guest]

You need a fourth table, linked to your third table. The fourth table would
have a row (not a column) for each grade: the primary key would be the three
fields Student Id, Course Id, Grade Number

**I'm sorry I don't understand what you mean by have a row not a column for
each grade. Please give you some examples. Also, you said Gr1, Gr2, etc are
not a good names for fields. Instead what do you suggust? Thank you.
Please keep in mind My Gr1, Gr2, etc fields are all not part of one course.
Meaning, I would to make it availabe, should there be a course one day that
requires 18 Gr's (18 tests). Thank you. ***