more student database questions

[Ann]

thanks so much to the MVP who helped me a couple of weeks
ago. the referral to a MS article was most helpful. i've
built a basic student database based using one of the
templates provided, but i've run up against something i
can't figure out. i don't just want to know how to do it,
i want to understand the logic behind it. any help or
referral would be appreciated. (2nd question below)

CLASSES table is linked to STUDENTS AND CLASSES with a one
to many relationship / the STUDENTS table is linked to
STUDENTS AND CLASSES with a one to many relationship /
this junction table allows me to create class rosters and
attendance sheets, as well as look up a student's entire
list of class assignments. i understand that logic. i've
approached this next step in multiple ways and can't seem
to solve it. I want to be able to track the students
attendance throughout the semester. Each class meets 15
times, so in the CLASS table I set up 15 fields so I could
define their unique meeting dates. The dates in these
fields fill in the column headers across the top of each
class attendance form, with students listed along the left
side of the grid. QUESTION: how/where do i store the data
that fills in this grid? I'm thinking the same solution
will help me figure out how to track a series of grades
for each student for each class. Can there be another
junction table?

My other question is related to this type of statement
(pasted in below) that appears in the DATA field of a
FORM's Properties. I want to understand how to read/create
these because there are so many of them in the template
I'm using. What terminology would I use to search for
articles on this subject? Or is there a relatively short,
logical explanation? THANK YOU

SELECT [Students And Classes].[StudentClassID], [Students
And Classes].[StudentID], [Students And Classes].[ClassID]
AS [Students And Classes_ClassID], [Classes].[ClassID] AS
[Classes_ClassID], [Classes].[ClassName], [Classes].
[Section], [Classes].[InstructorID], [Classes].[Day],
[Classes].[Time], [Classes].[Fee] FROM ([Classes] INNER
JOIN [Students And Classes] ON [Classes].[ClassID] =
[Students And Classes].[ClassID])

 

[Steve Schapel]

Ann,

Congratulations on getting as far as you have. However, it is a
mistake to put the meeting dates in separate fields. In fact, in a
database it is always a mistake, where there is a one-to-many
relationship between two entities, to define the data as separate
fields. In this case, you have a one-to-many relationship between
Classes and Meetings, and this means another table is needed. For
example...
Table: ClassMeetings
ClassMeetingID
ClassID
MeetingDate

Then, as regards the Attendance, there is a one-to-many relationship
between Students and sessions attended. Sooooo.... you need another
table! For example...
Table: Attendance
AttendanceID
ClassMeetingID
StudentID

- Steve Schapel, Microsoft Access MVP

thanks so much to the MVP who helped me a couple of weeks
ago. the referral to a MS article was most helpful. i've
built a basic student database based using one of the
templates provided, but i've run up against something i
can't figure out. i don't just want to know how to do it,
i want to understand the logic behind it. any help or
referral would be appreciated. (2nd question below)

CLASSES table is linked to STUDENTS AND CLASSES with a one
to many relationship / the STUDENTS table is linked to
STUDENTS AND CLASSES with a one to many relationship /
this junction table allows me to create class rosters and
attendance sheets, as well as look up a student's entire
list of class assignments. i understand that logic. i've
approached this next step in multiple ways and can't seem
to solve it. I want to be able to track the students
attendance throughout the semester. Each class meets 15
times, so in the CLASS table I set up 15 fields so I could
define their unique meeting dates. The dates in these
fields fill in the column headers across the top of each
class attendance form, with students listed along the left
side of the grid. QUESTION: how/where do i store the data
that fills in this grid? I'm thinking the same solution
will help me figure out how to track a series of grades
for each student for each class. Can there be another
junction table?

My other question is related to this type of statement
(pasted in below) that appears in the DATA field of a
FORM's Properties. I want to understand how to read/create
these because there are so many of them in the template
I'm using. What terminology would I use to search for
articles on this subject? Or is there a relatively short,
logical explanation? THANK YOU

SELECT [Students And Classes].[StudentClassID], [Students
And Classes].[StudentID], [Students And Classes].[ClassID]
AS [Students And Classes_ClassID], [Classes].[ClassID] AS
[Classes_ClassID], [Classes].[ClassName], [Classes].
[Section], [Classes].[InstructorID], [Classes].[Day],
[Classes].[Time], [Classes].[Fee] FROM ([Classes] INNER
JOIN [Students And Classes] ON [Classes].[ClassID] =
[Students And Classes].[ClassID])

 

[Ann]

Thank you, Steve. You've been incredibly helpful.

-----Original Message-----
Ann,

Congratulations on getting as far as you have. However, it is a
mistake to put the meeting dates in separate fields. In fact, in a
database it is always a mistake, where there is a one-to- many
relationship between two entities, to define the data as separate
fields. In this case, you have a one-to-many relationship between
Classes and Meetings, and this means another table is needed. For
example...
Table: ClassMeetings
ClassMeetingID
ClassID
MeetingDate

Then, as regards the Attendance, there is a one-to-many relationship
between Students and sessions attended. Sooooo.... you need another
table! For example...
Table: Attendance
AttendanceID
ClassMeetingID
StudentID

- Steve Schapel, Microsoft Access MVP

thanks so much to the MVP who helped me a couple of weeks
ago. the referral to a MS article was most helpful. i've
built a basic student database based using one of the
templates provided, but i've run up against something i
can't figure out. i don't just want to know how to do it,
i want to understand the logic behind it. any help or
referral would be appreciated. (2nd question below)

CLASSES table is linked to STUDENTS AND CLASSES with a one
to many relationship / the STUDENTS table is linked to
STUDENTS AND CLASSES with a one to many relationship /
this junction table allows me to create class rosters and
attendance sheets, as well as look up a student's entire
list of class assignments. i understand that logic. i've
approached this next step in multiple ways and can't seem
to solve it. I want to be able to track the students
attendance throughout the semester. Each class meets 15
times, so in the CLASS table I set up 15 fields so I could
define their unique meeting dates. The dates in these
fields fill in the column headers across the top of each
class attendance form, with students listed along the left
side of the grid. QUESTION: how/where do i store the data
that fills in this grid? I'm thinking the same solution
will help me figure out how to track a series of grades
for each student for each class. Can there be another
junction table?

My other question is related to this type of statement
(pasted in below) that appears in the DATA field of a
FORM's Properties. I want to understand how to read/create
these because there are so many of them in the template
I'm using. What terminology would I use to search for
articles on this subject? Or is there a relatively short,
logical explanation? THANK YOU

SELECT [Students And Classes].[StudentClassID], [Students
And Classes].[StudentID], [Students And Classes]. [ClassID]
AS [Students And Classes_ClassID], [Classes].[ClassID] AS
[Classes_ClassID], [Classes].[ClassName], [Classes].
[Section], [Classes].[InstructorID], [Classes].[Day],
[Classes].[Time], [Classes].[Fee] FROM ([Classes] INNER
JOIN [Students And Classes] ON [Classes].[ClassID] =
[Students And Classes].[ClassID])

.