Need Help Tweaking a Many to Many Design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I’m a software trainer and we register students for our training courses so
I’m in the process of designing a traditional Many to Many application that
will track student registration, the courses taken by each student, and the
ability to confirm registrations via email. So far, I have a traditional Many
to Many to design that does only part of what we need.

tblClasses [ClassID is the primary key]
tblStudents [StudentID is the primary key]
tblLInk [junction table that contains ClassID and StudentID]
tblCourses CourseNo and CourseName

So far, 2/3 of the application’s apparently working well. I can track the
students assigned to each class and which classes each student took. However,
there’s a 3rd component that’s equally important, if not more important, we
need to query the COURSES taken by each student.

In my tentative design, I’ve placed CourseNo and CourseName in tblClasses

The first 3 fields in tblClasses are:
ClassID
CourseNo
CourseName

The first 4 fields of tblLink are:

ClassID
StudentID
Confirmed
Completed

Unfortunately, I don’t think the above design will give me the information
we need to track courses taken for each student. How can I design the
application so we can run a query that will tell us which courses each
student completed? See below for samples of the courses taken from tblCourses.

CourseNo CourseName
------------- ------------------
001 Access I
002 Excel I
003 Outlook I
004 Access II
005 Excel II
006 Outlook II

Thanks,
Robert
 
I believe you need a to add the CourseID field as a foreign key in the
tblClasses table, and then delete the CourseNo and CourseName fields from
that table. Then you should be able to get the Course by joining back to
tblCourses table through tblClasses table, via a query similar to this:

SELECT tblStudents.StudentID, tblStudents.StudentName,
tblCourses.CourseNo, tblCourses.CourseName
FROM ((tblStudents INNER JOIN tblIink
ON tblStudents.StudentID = tblLink.StudentID)
INNER JOIN tblClasses
ON tblLink.ClassID = tblClasses.ClassID)
INNER JOIN tblCourses
ON tblClasses.CourseID = tblCourses.CourseID;
 
Hi Ken:

The CourseID and CourseName fields are the only two fields in their own
table called "tblCourses". I already have a CourseID field in tlbClasses
which has a lookup to tblCourses [foreign key]. If I understood your previous
post, that's the correct way to create a query where we can ask which courses
[not classes] were taken by each student?

Thanks,
Robert

Ken Snell (MVP) said:
I believe you need a to add the CourseID field as a foreign key in the
tblClasses table, and then delete the CourseNo and CourseName fields from
that table. Then you should be able to get the Course by joining back to
tblCourses table through tblClasses table, via a query similar to this:

SELECT tblStudents.StudentID, tblStudents.StudentName,
tblCourses.CourseNo, tblCourses.CourseName
FROM ((tblStudents INNER JOIN tblIink
ON tblStudents.StudentID = tblLink.StudentID)
INNER JOIN tblClasses
ON tblLink.ClassID = tblClasses.ClassID)
INNER JOIN tblCourses
ON tblClasses.CourseID = tblCourses.CourseID;
--

Ken Snell
<MS ACCESS MVP>



Robert T said:
Hello:

I'm a software trainer and we register students for our training courses
so
I'm in the process of designing a traditional Many to Many application
that
will track student registration, the courses taken by each student, and
the
ability to confirm registrations via email. So far, I have a traditional
Many
to Many to design that does only part of what we need.

tblClasses [ClassID is the primary key]
tblStudents [StudentID is the primary key]
tblLInk [junction table that contains ClassID and StudentID]
tblCourses CourseNo and CourseName

So far, 2/3 of the application's apparently working well. I can track the
students assigned to each class and which classes each student took.
However,
there's a 3rd component that's equally important, if not more important,
we
need to query the COURSES taken by each student.

In my tentative design, I've placed CourseNo and CourseName in tblClasses

The first 3 fields in tblClasses are:
ClassID
CourseNo
CourseName

The first 4 fields of tblLink are:

ClassID
StudentID
Confirmed
Completed

Unfortunately, I don't think the above design will give me the information
we need to track courses taken for each student. How can I design the
application so we can run a query that will tell us which courses each
student completed? See below for samples of the courses taken from
tblCourses.

CourseNo CourseName
------------- ------------------
001 Access I
002 Excel I
003 Outlook I
004 Access II
005 Excel II
006 Outlook II

Thanks,
Robert
Click to expand...
Click to expand...
 
Yes. The query that I posted should yield the results you seek (change field
names, etc. to match reality, of course).

--

Ken Snell
<MS ACCESS MVP>

Robert T said:
Hi Ken:

The CourseID and CourseName fields are the only two fields in their own
table called "tblCourses". I already have a CourseID field in tlbClasses
which has a lookup to tblCourses [foreign key]. If I understood your
previous
post, that's the correct way to create a query where we can ask which
courses
[not classes] were taken by each student?

Thanks,
Robert

Ken Snell (MVP) said:
I believe you need a to add the CourseID field as a foreign key in the
tblClasses table, and then delete the CourseNo and CourseName fields from
that table. Then you should be able to get the Course by joining back to
tblCourses table through tblClasses table, via a query similar to this:

SELECT tblStudents.StudentID, tblStudents.StudentName,
tblCourses.CourseNo, tblCourses.CourseName
FROM ((tblStudents INNER JOIN tblIink
ON tblStudents.StudentID = tblLink.StudentID)
INNER JOIN tblClasses
ON tblLink.ClassID = tblClasses.ClassID)
INNER JOIN tblCourses
ON tblClasses.CourseID = tblCourses.CourseID;
--

Ken Snell
<MS ACCESS MVP>



Robert T said:
Hello:

I'm a software trainer and we register students for our training
courses
so
I'm in the process of designing a traditional Many to Many application
that
will track student registration, the courses taken by each student, and
the
ability to confirm registrations via email. So far, I have a
traditional
Many
to Many to design that does only part of what we need.

tblClasses [ClassID is the primary key]
tblStudents [StudentID is the primary key]
tblLInk [junction table that contains ClassID and StudentID]
tblCourses CourseNo and CourseName

So far, 2/3 of the application's apparently working well. I can track
the
students assigned to each class and which classes each student took.
However,
there's a 3rd component that's equally important, if not more
important,
we
need to query the COURSES taken by each student.

In my tentative design, I've placed CourseNo and CourseName in
tblClasses

The first 3 fields in tblClasses are:
ClassID
CourseNo
CourseName

The first 4 fields of tblLink are:

ClassID
StudentID
Confirmed
Completed

Unfortunately, I don't think the above design will give me the
information
we need to track courses taken for each student. How can I design the
application so we can run a query that will tell us which courses each
student completed? See below for samples of the courses taken from
tblCourses.

CourseNo CourseName
------------- ------------------
001 Access I
002 Excel I
003 Outlook I
004 Access II
005 Excel II
006 Outlook II

Thanks,
Robert
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken:

Thanks so much, I truly appreciate you taking the time to build the query
that we need to track student courses. Have a great day!

Robert
 
Hi Ken:

Your script worked well, now we can run a query and in addition to seeing
who attend a specific class, we can also see which courses a student
attended.

Thanks so much,
Robert

Ken Snell (MVP) said:
I believe you need a to add the CourseID field as a foreign key in the
tblClasses table, and then delete the CourseNo and CourseName fields from
that table. Then you should be able to get the Course by joining back to
tblCourses table through tblClasses table, via a query similar to this:

SELECT tblStudents.StudentID, tblStudents.StudentName,
tblCourses.CourseNo, tblCourses.CourseName
FROM ((tblStudents INNER JOIN tblIink
ON tblStudents.StudentID = tblLink.StudentID)
INNER JOIN tblClasses
ON tblLink.ClassID = tblClasses.ClassID)
INNER JOIN tblCourses
ON tblClasses.CourseID = tblCourses.CourseID;
--

Ken Snell
<MS ACCESS MVP>



Robert T said:
Hello:

I'm a software trainer and we register students for our training courses
so
I'm in the process of designing a traditional Many to Many application
that
will track student registration, the courses taken by each student, and
the
ability to confirm registrations via email. So far, I have a traditional
Many
to Many to design that does only part of what we need.

tblClasses [ClassID is the primary key]
tblStudents [StudentID is the primary key]
tblLInk [junction table that contains ClassID and StudentID]
tblCourses CourseNo and CourseName

So far, 2/3 of the application's apparently working well. I can track the
students assigned to each class and which classes each student took.
However,
there's a 3rd component that's equally important, if not more important,
we
need to query the COURSES taken by each student.

In my tentative design, I've placed CourseNo and CourseName in tblClasses

The first 3 fields in tblClasses are:
ClassID
CourseNo
CourseName

The first 4 fields of tblLink are:

ClassID
StudentID
Confirmed
Completed

Unfortunately, I don't think the above design will give me the information
we need to track courses taken for each student. How can I design the
application so we can run a query that will tell us which courses each
student completed? See below for samples of the courses taken from
tblCourses.

CourseNo CourseName
------------- ------------------
001 Access I
002 Excel I
003 Outlook I
004 Access II
005 Excel II
006 Outlook II

Thanks,
Robert
Click to expand...
Click to expand...
 
Back
Top