auto filling a master table

[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.

 

[Emilia Maxim]

mike,

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).

First of all it seems your data structure needs to be changed. How
would you store the data for students taking several classes?

Here's what I'd suggest:
Leave the student's personal data (name, address, ID, phone...) in a
Students table and create another table StudentInClass with these
fields:
ID (autonumber primary key)
ClassID (foreign key, equals Class ID from classes table)
StudentID (foreign key, equals Student ID from students table)

Create then 2 1:n relationships with relational integrity:
1)
Main table: Classes, Key: ID
Foreign table StudentInClass, Key: ClassID

2)
Main table: Students, Key: ID
Foreign table StudentInClass, Key: StudentID

The form for enrollment would display only the students' personal data
and would have a subform based on table StudentInClass where the
student can register, i.e. add records with chosen classes.

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.

Then you can add the fields for approval and comments (and others as
needed) to the StudentInClass table. IOW, this table will store all
information related to both students _and_ classes.

For the approval you could create a continuous main form based on data
from StudentInClass table. You could have unbound and inactive text
boxes to display the name etc. from Students table using the lookup
technique (see Help for this).

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de