Clicking and storing information...

  • Thread starter Thread starter Arlindo Fragoso
  • Start date Start date
A

Arlindo Fragoso

Hi!
I'm a newbie and my english is not so good. So I'll try to expose my
problem:
I've a table call Pupils (numberId, name...) linked one to many to
Disciplines (numberId, disc1, disc2, etc) and I would like to make a form
(and a subform?) with several itens and by clicking them send the
information (Geography, History, etc - butons with macros?) to the table
Disciplines and then produce a report that list the pupils by grade, with
the disciplines hes/he have chosen.
I'm stucked and d'ont know how to make it...
Some help, please...
Arlindo Fragoso
 
You need a design similar to the following:

TABLES

tblStudents
-----------
StudentID (Primary Key)
StudentName
GradeLevel

tblCourses
----------
CourseID (Primary Key)
CourseName

tblEnrollment
-------------
CourseID
StudentID

(Primary Key is CourseID and StudentID)

FORMS

frmStudents
-----------
This is the main form that contains all of the fields from
tblStudents.

frmCourseChoices
----------------
The form will contain the fields CourseID and StudentID.
You can set the "Visible" property on the StudentID field
to No.
Make the CourseID field a combobox, querying tblCourses.
The combobox should contain two columns. The first column
would be CourseID. The second column would be CourseName.
Sort the query by CourseName. Bind column 1 but set the
column widths to 0";1.5" so that the user doesn't see the
actual CourseID number. It doesn't mean anything to them
anyway. Set the default view to "Continuouos Forms".

This will be the subform on frmStudents linked by
StudentID.

QUERIES

qryEnrollment
-------------
Create a query with all three tables linking the keys from
each table...StudentID to StudentID and CourseID to
CourseID.
Select StudentName, CourseName and GradeLevel. Sort by
StudentName.

This query will display the course selections for each
student.

REPORTS

rptEnrollment
-------------

Create a report using qryEnrollment as the data source.
Group the report as desired.

Possible groupings would be:
Student, Course
GradeLevel, Student, Course
 
I recently, with much help from this forum, completed a
similar sort of project. I agree with your general
approach, but I have a few comments.
You wrote this description of tblEnrollment:

"tblEnrollment
-------------
CourseID
StudentID

(Primary Key is CourseID and StudentID)"

I think it would be more accurate to say that these are
the Foreign Keys from the other two tables. tblEnrollment
should probably have its own PK (e.g. EnrollmentID).

For the combo box, I think for a newcomer it would be
simplest to make a query (qryCourses) from tblCourses,
then use the combo box wizard to create the necessary
combo box based on the query.

I suggest that a newcomer would do well to use autoform to
create forms from tables or queries. I would create
frmStudents and frmCourses, as you suggest, then open
frmStudents in design view and drag frmCourses from the
database window onto frmStudents.

The first thing is to establish the relationships between
the tables, as you have outlined. Queries based on the
tables will maintain the same relationships without any
additional work. I almost always use queries instead of
tables as the source for forms and reports, since they
provide sorting and other options not available in
tables. You mention creating a query for the report, but
I would use that query for the form as well.
To sum up my suggestions, I would say to create the three
tables and establish the relationships. This is done in
the relationships window by dragging fields with the same
name (CourseID and StudentID) from one table to the
other. Create queries as needed based on the tables, and
create forms and reports from either the tables or the
queries. tblEnrollment will contain a record for each
course the student is taking. If the student is taking
five courses, there will be five records in tblEnrollment
for that student's ID number.
Finally (this is important), Arlindo needs to remember
that selecting the courses for a student does not need to
(and should not, in the interest of sound database design)
copy information to another table. The Form/Subform
structure will present the properly linked tables for
viewing or printing. Nothing more is needed.
 
Bruce said:
I recently, with much help from this forum, completed a
similar sort of project. I agree with your general
approach, but I have a few comments.
You wrote this description of tblEnrollment:

"tblEnrollment
-------------
CourseID
StudentID

(Primary Key is CourseID and StudentID)"
Click to expand...
I think it would be more accurate to say that these are
the Foreign Keys from the other two tables. tblEnrollment
should probably have its own PK (e.g. EnrollmentID).


The purpose of making CourseID and StudentID the primary key of
the Enrollment table is to ensure that a student can't be erroneously
enrolled in the same course multiple times. Of course in practice a
Semester or similar field would also be needed. What would be the
reason for adding a superfluous EnrollmentID? What purpose
would it serve that isn't being served by CourseID and StudentID?
 
Back
Top