If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
table issues
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 a 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. |
#2
|
|||
|
|||
table issues
On Fri, 28 Nov 2008 13:56:01 -0800, Marie
wrote: 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 a 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 -- John W. Vinson [MVP] |
#3
|
|||
|
|||
table issues
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" wrote: On Fri, 28 Nov 2008 13:56:01 -0800, Marie wrote: 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 a 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 -- John W. Vinson [MVP] |
#4
|
|||
|
|||
table issues
On Sat, 29 Nov 2008 00:45:01 -0800, Marie
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
table issues
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" wrote: On Fri, 28 Nov 2008 13:56:01 -0800, Marie wrote: 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 a 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 -- John W. Vinson [MVP] |
#6
|
|||
|
|||
table issues
On Sat, 29 Nov 2008 12:59:01 -0800, Marie
wrote: 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.) -- John W. Vinson [MVP] |
#7
|
|||
|
|||
table issues
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" wrote: On Sat, 29 Nov 2008 12:59:01 -0800, Marie wrote: 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.) -- John W. Vinson [MVP] |
#8
|
|||
|
|||
table issues
On Sat, 29 Nov 2008 14:54:01 -0800, Marie
wrote: 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 -- John W. Vinson [MVP] |
#9
|
|||
|
|||
table issues
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" wrote: On Sat, 29 Nov 2008 14:54:01 -0800, Marie wrote: 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 -- John W. Vinson [MVP] |
#10
|
|||
|
|||
table issues
On Sat, 29 Nov 2008 18:01:00 -0800, Marie
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|