implementation - transact sql - many to many relationship

Hello, What is the implementation?
tblCourse(CourseID, CourseName, CourseField1, CourseField2...)
tblStudent(StudentID, FamilyName, FirstName, DoB, StudentField1, StudentField2...)
tblCourseStudent(CourseID, StudentID, PerformanceMetric1, PerformanceMetric2...)
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
enrique_aeo said:
>>Hello, What is the implementation?

What is your question?  What are you trying to accomplish?

Quick note: that table structure looks denormalized...
enrique_aeoAuthor Commented:
THE  CODE transact sql
Patrick MatthewsCommented:
enrique_aeo,

Sorry, but it is still not clear.

Patrick
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

enrique_aeoAuthor Commented:
tblCourse(CourseID, CourseName, --) when CourseID is primary key
tblStudent(StudentID, FamilyName, ..) when StudentID is primary key
tblCourseStudent(CourseID, StudentID, ...) when CourseID and StudentID are foreign key
something like I do in transact sql code?
enrique_aeoAuthor Commented:
my question is, as it resolves a many to many relationship in transact sql code?
GRayLCommented:
Your last table needs to look like this:

tblCoursesStudents
==============
CrsStuID - primary key - autonumber
CourseID - foreign key from tblCourses
StudentID - foreign key from tblStudents
PerformanceMetric1 - datatype as required
PerformanceMetric2 - datatype as required
etc.

I always pluralize tables - it is so much more natural - notwithstanding some pundits!  tblStudent sounds like info about only one student.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
There is no code to form the third table.  You should create a form with combo boxes for both Courses and Students as well as some way of entering all the performance metrics (combo or list box)
St3veMaxCommented:
Try:
SELECT 
	 C.*
	,CS.*
	,S.*
FROM
	tblCourse C
INNER JOIN
	tblCourseStudent CS ON C.CourseID = CS.CourseID
INNER JOIN
	tblStudent S ON CS.StudentID = CS.StudentID

Open in new window

GRayLCommented:
tblCoursesStudents is the Joining table between the two Many to Many tables allowing a one-to-many

tblCourses M-----> tblCoursesStudents <------M tblStudents
enrique_aeoAuthor Commented:
tblCourses and tblStudents are an eschema Intranet, these tables already exist. The new table is tblCoursesStudents,  therefore would only have to make the code table creating tblCoursesStudents or I do something extra in the tables tblCourses and tblStudents
GRayLCommented:
Somehow you have to get the info about what students are taking what course into the the tblCoursesStudents.  As I suggested earlier, the best way would be to use a form designed for that purpose.  You have to create the table and then use the form to populate it.
GRayLCommented:
Thanks, glad to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.