table issues

[Marie]

I have a database (ACCESS 2002) with separate tables for student names,
addresses, contct details etc. One of the tables is a Qualification Record
which has the course code. Each course has 8 Attendance days for set dates.
The days are: ODAY, TACG1, TACG2, TACB1, TACB2, TACY1, TACY2 and TACSFA. I
would like to be able to enrol students for all the specific training days
for that course or individual ones. Then I would like to mark them as having
attended, or not attended with a comment. If they did not attend then they
should be enrolled in a TAC day further on. in another course. Thanks.

 

[John W. Vinson]

I have a database (ACCESS 2002) with separate tables for student names,
addresses, contct details etc. One of the tables is a Qualification Record
which has the course code. Each course has 8 Attendance days for set dates.
The days are: ODAY, TACG1, TACG2, TACB1, TACB2, TACY1, TACY2 and TACSFA. I
would like to be able to enrol students for all the specific training days
for that course or individual ones. Then I would like to mark them as having
attended, or not attended with a comment. If they did not attend then they
should be enrolled in a TAC day further on. in another course. Thanks.

STOP.

You're "committing spreadsheet", a misdemeanor punishable by being required to
read about normalization. <g>

If you have a Many (students) to Many (attendance days, 8 in this case) you
should model it using a third table:

Students
StudentID <primary key>
LastName
FirstName
<other biographical details>

TrainingDates
Trainingday <Text, Primary Key, e.g. "ODAY")
TrainingDate Date/Time

Attendance
StudentID <link to Students>
Trainingday <link to TrainingDates>
TrainingComment
<other fields concerning THIS student's attendanace at THIS training>

 

[Marie]

Thanks John, but is it really a many to many relationship as the days are on
different dates. ie Course Code CA81108 will have ODAY on 8/11/08 and TACG1
may be on 10/11/08 and so on. Course CA81201 will have ODAY on 1/12/08 and
TACG1 on 8/12/08?

 

[John W. Vinson]

Thanks John, but is it really a many to many relationship as the days are on
different dates. ie Course Code CA81108 will have ODAY on 8/11/08 and TACG1
may be on 10/11/08 and so on. Course CA81201 will have ODAY on 1/12/08 and
TACG1 on 8/12/08?

Then you need a different table structure... but it will NOT have one field
per day. I'm guessing you'll need a table with fields CourseCode, DayType, and
CourseDate with records like

CA81108; "ODAY"; 8/11/08
CA81108; "TACG1"; 10/11/08
CA81201; "ODAY"; 1/12/08
CA81201; "TACG1"; 8/12/08

and so on.

 

[Marie]

Trying to get my head around this. I can see that the 3 table is the way to
go, I guess. (Not too sure about Junction tables so desperately trying to
avoid). In your model below: the junction table is Attendance -yes? Primary
key would be the 2 Primary keys from Students and Training Day? This is the
table where I have "enrolled", "attended" or "not attended" and comments re
why not attended?

 

[John W. Vinson]

Trying to get my head around this. I can see that the 3 table is the way to
go, I guess. (Not too sure about Junction tables so desperately trying to
avoid).

Ummmm... why?

That's a bit like saying you desparately want to avoid calculated expressions
in an Excel spreadsheet, or Styles in a Word document. Junction tables are
simply *essential* when you have a relational database beyond the rock-bottom
minimum, and they're not difficult to manage!

In your model below: the junction table is Attendance -yes? Primary
key would be the 2 Primary keys from Students and Training Day? This is the
table where I have "enrolled", "attended" or "not attended" and comments re
why not attended?

Exactly.

Think about it this way: you have three kinds of things - "entities" is the
usual jargon term - that you're trying to track. A Student is one kind of
entity - a person, with a name, birthdate, hair color, other attributes of a
human being; a TrainingDay is another kind of entity, one with a location, a
date, an instructor, perhaps other attributes. A Student doesn't have (as a
personal attribute) a training date; a training day doesn't have a hair color!

The Attendance table is yet another entity, an event rather than a person or a
thing; it's the event of one person attending one training event. Therefore
you need to identify the person (with a StudentID foreign key linking to the
student table, so you can tell who attended), and which event was attended
(with a TrainingDay ID to link to the specific event which was attended). You
might want to call this table Enrollment instead of Attendance; the mere
existance of a record in the table would indicate that the student was
enrolled, so you wouldn't need a field for it; and you could add just a Yes/No
field Attended which would be Yes if they did attend, No if they didn't. Of
course you could have other fields as well (ReasonMissed, Comments, fields for
the student's evaluation of the session, etc.)

 

[Marie]

Thanks John, That was a most helpful answer. If you could now explain in the
same wonderful simple and clear manner how junction tables are actually
populated, I would be eternally grateful. I understand (on a basic level)
about making Primary Keys and Joining tables, but then do you do the entries
through a form or query or both? And what happens if the Junction table
becomes corrupted. That's something that I worry about, since every table I
have as the linking student ID or otherlink as a field and these tables
won't. Do you mind answering all this first and then I have some other
questions about the Enrolment table. thankyou, thankyou.

 

[John W. Vinson]

Thanks John, That was a most helpful answer. If you could now explain in the
same wonderful simple and clear manner how junction tables are actually
populated, I would be eternally grateful. I understand (on a basic level)
about making Primary Keys and Joining tables, but then do you do the entries
through a form or query or both? And what happens if the Junction table
becomes corrupted. That's something that I worry about, since every table I
have as the linking student ID or otherlink as a field and these tables
won't. Do you mind answering all this first and then I have some other
questions about the Enrolment table. thankyou, thankyou.

Typically you would use a Form with a Subform to populate the tables. For your
enrollment example you could do it either (or both!) of two ways. One way
would be to have a Form based on the students table, into which you could add
students; on this Form you would have a Subform, based on the Enrollment
table. This subform would use the StudentID as the master and child link
field; this would show all the sessions in which that student is enrolled, and
automatically populate the StudentID from the mainform into the enrollment
table when you add a new record to the subform. On the subform you would have
a Combo Box based on the TrainingDays table, to allow you to enrol the
currently displayed student into any (or many) of the classes.

The flipside would be to have a form based on the class session, with a
Subform letting you pick a Student from a combo box. Which you use depends on
your workflow and whatever works best for you.

You needn't worry about corruption per se. If you have a Relationship defined
between the Student table and the StudentID in the Enrollment table, and
similarly a relationship between the schedule table and the enrollment table,
with referential integrity enforced - then Access will see to it that you
*CANNOT* add a record with invalid foreign keys, nor would you be able to
delete a student or a class if doing so would leave an "orphan" record in the
enrollment table.

I'm not certain what you mean by

every table I have as the linking student ID or otherlink as a field and
these tables won't

What's "otherlink"? What tables won't have ID's???


Take a look at the Orders form in the Northwind sample database for an example
of how this can work. The analogy is direct:

Orders ----> Students
Products ----> TrainingDates
OrderDetails ---> Enrolment

 

[Marie]

Well, I'll have a try at this tonight, thanks John. Other links I meant was
that in address table there is a Student ID (Primary key from Student Names
table) field to join the tables. Thanks again.

 

[John W. Vinson]

Well, I'll have a try at this tonight, thanks John. Other links I meant was
that in address table there is a Student ID (Primary key from Student Names
table) field to join the tables. Thanks again.

That you'll fill in using a Combo Box on the subform. The user need not enter
(in fact should never even SEE) the StudentID.