Tables and Forms

[yeshwant]

wrewr

-----Original Message-----
From: Steve Schapel
Posted At: Friday, May 20, 2005 11:40 PM
Posted To: microsoft.public.access
Conversation: Tables and Forms
Subject: Re: Tables and Forms

John,

From your description, there is a one-to-many relationship between
Students and Enrolments. This should be reflected in your table
structure. There is also a one-to-many relationship between Courses and
Enrolments. Therefore, you need at least 3 tables: Courses, Students,
Enrolments. The Enrolments table will need a Foreign Key field to
relate it to both of the other two tables. For example, it might have
fields something like this...
EnrolmentID
StudentID
CourseCode
EnrolmentDate

Therefore, John Smith's details (name, address, etc) only get entered
once into the database, and every time he enrols in a course, all you
need is to get his StudentID into the Enrolments table.

Not a complete answer, but hopefully it will help point you in a useful
direction.

 

[345]

67657
-----Original Message-----
From: tina
Posted At: Friday, May 20, 2005 11:38 PM
Posted To: microsoft.public.access
Conversation: Tables and Forms
Subject: Re: Tables and Forms

you're right, John, your tables need to be set up correctly. suggest the
following 3 tables to model the info in your post:

tblStudents
StudentID (primary key)
FirstName
LastName
(other data that describes a specific student.)

tblClasses
ClassID (pk)
ClassName
(other data that describes a specific class. it's likely that you need
multiple tables here; this table would be strictly class names, then perhaps
a "child" table listing specific instances of classes. (your school might
offer 3 English 110 classes, for example. my school would designate each of
those instances with a Section number.)

tblStudentClasses
StClassID (pk)
StudentID (foreign key from tblStudents)
ClassID (foreign key form tblClasses)
(other fields that describe a specific student/class, such as Semester,
perhaps. maybe DateEnrolled, etc.)

you have a many-to-many relationship between tblStudents and tblClasses:
one student may attend many classes, and one class may be attended by many
students. in Access, such a relationship is expressed by two one-to-many
relationships between the "parent" tables and a "linking" or "child" table
(tblStudentClasses), as

tblStudents 1:n tblStudentClasses
tblClasses 1:n tblStudentClasses

normalized tables and correct table relationships are the foundation of your
database, more important by far than any other element. i strongly recommend
you read up on both; the time you spend now will be saved a thousand-fold as
you build queries, forms, and reports on a properly designed foundation. see
the following webpage for many links to help you:

http://www.ltcomputerdesigns.com/JCReferences.html
suggest you begin with the "Database Design 101" and "Starting Out" links.

hth

 

[345]

5765
-----Original Message-----
From: tina
Posted At: Friday, May 20, 2005 11:38 PM
Posted To: microsoft.public.access
Conversation: Tables and Forms
Subject: Re: Tables and Forms

you're right, John, your tables need to be set up correctly. suggest the
following 3 tables to model the info in your post:

tblStudents
StudentID (primary key)
FirstName
LastName
(other data that describes a specific student.)

tblClasses
ClassID (pk)
ClassName
(other data that describes a specific class. it's likely that you need
multiple tables here; this table would be strictly class names, then perhaps
a "child" table listing specific instances of classes. (your school might
offer 3 English 110 classes, for example. my school would designate each of
those instances with a Section number.)

tblStudentClasses
StClassID (pk)
StudentID (foreign key from tblStudents)
ClassID (foreign key form tblClasses)
(other fields that describe a specific student/class, such as Semester,
perhaps. maybe DateEnrolled, etc.)

you have a many-to-many relationship between tblStudents and tblClasses:
one student may attend many classes, and one class may be attended by many
students. in Access, such a relationship is expressed by two one-to-many
relationships between the "parent" tables and a "linking" or "child" table
(tblStudentClasses), as

tblStudents 1:n tblStudentClasses
tblClasses 1:n tblStudentClasses

normalized tables and correct table relationships are the foundation of your
database, more important by far than any other element. i strongly recommend
you read up on both; the time you spend now will be saved a thousand-fold as
you build queries, forms, and reports on a properly designed foundation. see
the following webpage for many links to help you:

http://www.ltcomputerdesigns.com/JCReferences.html
suggest you begin with the "Database Design 101" and "Starting Out" links.

hth

 

[Guest]

Hello,

I am working on a school database. Previously I entered the student’s names
and information thru the form. Now, I like to avoid typing the names of the
students again if they decide to enroll into another class. For example: If
john smith enrolled into English 110 last week, and now enrolled into English
111, I don't want to add his information again if its already in the system.
I need hints on what to do first. I know table is what I need to focus on.
Thank you.

 

[tina]

you're right, John, your tables need to be set up correctly. suggest the
following 3 tables to model the info in your post:

tblStudents
StudentID (primary key)
FirstName
LastName
(other data that describes a specific student.)

tblClasses
ClassID (pk)
ClassName
(other data that describes a specific class. it's likely that you need
multiple tables here; this table would be strictly class names, then perhaps
a "child" table listing specific instances of classes. (your school might
offer 3 English 110 classes, for example. my school would designate each of
those instances with a Section number.)

tblStudentClasses
StClassID (pk)
StudentID (foreign key from tblStudents)
ClassID (foreign key form tblClasses)
(other fields that describe a specific student/class, such as Semester,
perhaps. maybe DateEnrolled, etc.)

you have a many-to-many relationship between tblStudents and tblClasses:
one student may attend many classes, and one class may be attended by many
students. in Access, such a relationship is expressed by two one-to-many
relationships between the "parent" tables and a "linking" or "child" table
(tblStudentClasses), as

tblStudents 1:n tblStudentClasses
tblClasses 1:n tblStudentClasses

normalized tables and correct table relationships are the foundation of your
database, more important by far than any other element. i strongly recommend
you read up on both; the time you spend now will be saved a thousand-fold as
you build queries, forms, and reports on a properly designed foundation. see
the following webpage for many links to help you:

http://www.ltcomputerdesigns.com/JCReferences.html
suggest you begin with the "Database Design 101" and "Starting Out" links.

hth

 

[Steve Schapel]

John,

From your description, there is a one-to-many relationship between
Students and Enrolments. This should be reflected in your table
structure. There is also a one-to-many relationship between Courses and
Enrolments. Therefore, you need at least 3 tables: Courses, Students,
Enrolments. The Enrolments table will need a Foreign Key field to
relate it to both of the other two tables. For example, it might have
fields something like this...
EnrolmentID
StudentID
CourseCode
EnrolmentDate

Therefore, John Smith's details (name, address, etc) only get entered
once into the database, and every time he enrols in a course, all you
need is to get his StudentID into the Enrolments table.

Not a complete answer, but hopefully it will help point you in a useful
direction.

 

[G. Vaught]

Did you know that at the Microsoft Template for Office site, there is a
database already created to track students, classes, grades etc. Why
reinvent the wheel. I use this database to track my students in the class I
teach. I even modified it to track attendance. This would give you a
starting point where you could modify this database to suite your needs.

 

[Guest]

Thank you all for your inputs and suggestions. I will be working on it.