scheduling database

[mike]

Hi,

I need some help with a scheduling database I am working
on. I have a form, in which a database administrator can
set up multiple classes over the course of time (this
information fills a "class table" with various fields
including: class title, class date, class time, class
location, etc... in addition, I have a field
labeled "class ID" that is a auto number/primary key used
as a unique identifier). I have another form, in which a
student can enroll into a class (this form fills
a "registration table" with various fields including:
student ID, student first name, student last name, student
phone number... also, the user selects from a pull down
box the class ID for the class he/she wishes to enroll
into).

I am having difficultly in figuring out how to go about
the remaining pieces. First, is there a way to auto-
populate a table from two separate tables? Meaning, can I
get the two tables (class and registration table) to fill
another table, so I can use all the information together?
Because the third form, "approval form", I need to develop
will allow the database administrator to view the students
that enroll in a class and allow the administrator to
approve (yes/no) the enrollment and add any comments.
This form would fill a "master class table" with all of
the information from the class and student table along
with the approve/comments information.

I have tried using a subform on the "approval form" which
is based on a query that pulls all the information from
the class table and student table... then having the
approve/comments controls outside the subform to allow the
administrator to select the approval and comments fields,
but I cannot get all the fields (class, student,
approve/comments) to populate into a "master class table".

Any help would be appreciated. I am getting stuck.

 

[Kevin]

Hi,

It sounds to me like you are having trouble with data
normalization. You are on the right track, but you do not
need to populate the third table with data from the other
tables. I think what you need is a table called say
studentClass. In it you would need at a minimum the
student id and the class id. When a student enrolls for a
class, you would insert into the table the students id and
the class id for each class. If the student can enroll for
multiple classes over time, you don't want them to have to
fill out their demographic data repeatedly, so have one
form to allow then to enter that information, then another
form to allow them to enroll into a class. The enrollment
form would be tied to the studentclass table.

If you have the student completing the demographic
information (Name, Phone etc.) each time, you will have
duplicate data which is a waste of disk resources and
will, over time affect the performance of your database.

There are a number of good resources that discuss data
modeling and normalization. Do a Web search for more
information or go to a good bookstore in you area. There
is great power and utility in a relational database, but
you have to structure the database correctly to take
advantage of it! Good luck and post back if you need more
help.

Hope that helps!

Kevin

 

[Allan Thompson]

Mike,

One of the principles of database design is to enter any given piece of
information only once. A corollary is to store the information (other than
keys) in only one table (occasionally exceptions may be made for
performance reasons, etc). You really don't need to, and shouldn't, create
a new table from your Class and Registration tables. You indicate that you
created a query that joins the Class and Registration tables to populate
your form-subform. This also shouldn't be necessary. The Class table, or a
query based on it, should be the record source for your master form -- and
the Registration table should be used for the subform. In the subform
properties, you would use ClassID for "Link Child Fields" and "Link Master
Fields"

You seem to be headed in the right direction with your form-subform
approach. Regarding the Approval & Comments, I'm not clear as to whether
this applies at the student/enrollee level or at the class level. If is is
at the enrollee level, I would suggest a adding Approval & Comments as
columns to the Registration table and including them in the subform. If at
the class level, add the columns to the Class table and include them in the
master form.

I hope this helps.

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

 

[Darryn]

Hi,

It sounds to me like you are having trouble with data
normalization. You are on the right track, but you do not
need to populate the third table with data from the other
tables. I think what you need is a table called say
studentClass. In it you would need at a minimum the
student id and the class id. When a student enrolls for a
class, you would insert into the table the students id and
the class id for each class. If the student can enroll for
multiple classes over time, you don't want them to have to
fill out their demographic data repeatedly, so have one
form to allow then to enter that information, then another
form to allow them to enroll into a class. The enrollment
form would be tied to the studentclass table.

If you have the student completing the demographic
information (Name, Phone etc.) each time, you will have
duplicate data which is a waste of disk resources and
will, over time affect the performance of your database.

There are a number of good resources that discuss data
modeling and normalization. Do a Web search for more
information or go to a good bookstore in you area.

I would strongly recommend "Database design for Mere Mortals" by M.J
Hernandez

It goes through the whole process in everyday terms and if you follow
the steps in the book and use copies of the sheets in the back of the
book to make up lists of tables, their assoc. fields and the table
relationships for your databaseyou will have something to start off
with in access.

The book doesn't cover using Access or any other database package but
gives you a good understanding of what you are trying to do.

Once you have your design worked out you are half way there, then just
search groups.google.com in the access groups to find out what else
you need

HTH

Darryn

-- remove two eyes to reply!