Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2014
    Posts
    5

    Unhappy Unanswered: 4 Tables inner joined - showing multiple data, already distinct

    This is what the result is showing:
    Click image for larger version. 

Name:	results.jpg 
Views:	2 
Size:	491.0 KB 
ID:	15968

    Data Structure: Click image for larger version. 

Name:	sK2RK.jpg 
Views:	4 
Size:	179.8 KB 
ID:	15969

    I just would like to create a report that shows which students is taking up the course based on the course_id

    Here's my code below:
    Code:
    SELECT
    distinct stud_cour.staff_courses_id,
    course.course_id,
    instr.fname,
    course.course_name,
    course.course_date ,
    stud_cour.pre_test,
    stud_cour.post_test,
    stud_cour.retest_post,
    stud_cour.skill_test,
    stud_cour.skill_retest,
    stud_cour.course_result,
    stud_cour.notes,
    stud.fname,
    stud.lname,
    stud.department,
    stud.work_location,
    stud.title,
    course.location,
    stud_cour.staff_num,
    instr.lname,
    instr.instructor_ahaid
    
    FROM 
    (
    		(
    			(
    				(
    					[Instructor Table] as instr
    					INNER JOIN [Instructor Teaching Information Table] as instr_teach
    						ON instr.instructor_ahaid = instr_teach.instructor_id
    				)
    				INNER JOIN [Course Table] as course
    				ON instr_teach.course_id = course.course_id
    			)
    			INNER JOIN [Student to Courses Table] as stud_cour
    			ON course.course_id = stud_cour.course_id
    		)
    	INNER JOIN [Student Table] as stud
    	ON stud.staff_num = stud_cour.staff_num
    )
    
    WHERE ((course.course_id)=([Forms]![Course Information]![course_id]))

    I just would like to create a report that shows which students is taking up the course based on the course_id

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by kinsesyete View Post
    This is what the result is showing:
    just would like to create a report that shows which students is taking up the course based on the course_id
    based on your current design and your current requirement something like
    Code:
    select c.course_name, s.fname, s.lname from course as C
    left join stud_cour as sc on c.course_id = sc.course_id
    left join stud as s on sc.staff_num = stud.staff_num
    order by c.course_name, s.lname, s.fname
    as you don'' need to refer to the instr or instr_teach tables at all for that requirement

    so what column is which
    you have a column called staff_num in instrm stud_cour & stud, none of whcih are PK's
    you have a column called staffcoursesid in stud_cour, whereaas Id' expect the PK of this table to be the student PK and coursePK, as stud_cour is an intersection table identifying what courses a student is registered for

    presumably an instructor can teach one or more courses, so i woudl expect instr_teach to be another intersection table whose pk is a compoisite of the teacher id and course pk

    I suspect you are using an autogenerated / autonumber pk in all tables, which is fine AS LONG AS THERE IS NO BETTER, key.
    use an autogenerated key if there is no obvious column/columns that could be a primary key
    or if the obvious would be a complicated composite primary key (say many coliumns each from another table),
    or if the obvious column/columns are liable to change and therefore not especially good as a PK

    whenever you see more than one column with the same name except for a numerical suffix it suggests a non normalised design (eg partner_1, partner_2)...

    I suspect your design is causing you the problems you are experiencing. sometimes a normalised design can get ugly, but usually it doesnt'.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2014
    Posts
    5
    awesome! Thanks for the hint bro.

    Look at what I did. I'm kindda confuse on how to connect it. ((
    And yeah, sorry for the data structure. I'm still a noobie in database

    Code:
    SELECT
    distinct stud_cour.staff_courses_id,
    course.course_id,
    instr.fname,
    course.course_name,
    course.course_date ,
    stud_cour.pre_test,
    stud_cour.post_test,
    stud_cour.retest_post,
    stud_cour.skill_test,
    stud_cour.skill_retest,
    stud_cour.course_result,
    stud_cour.notes,
    stud.fname,
    stud.lname,
    stud.department,
    stud.work_location,
    stud.title,
    course.location,
    stud_cour.staff_num,
    instr.lname,
    instr.instructor_ahaid
    
    FROM 
    (
    		(
    			(
    				(
    					[Course Table] as course
    					LEFT JOIN [Student to Courses Table] as stud_cour
    					ON course.course_id = stud_cour.course_id
    				)
    				LEFT JOIN [Instructor Teaching Information Table] as instr_teach
    				ON stude_cour.course_id = instr_teach.course_id
    			)
    			LEFT JOIN [Instructor Table] as instr
    			ON instr.instructor_ahaid = instr_teach.instructor_id
    		)
    	LEFT JOIN [Student Table] as stud
    	ON stud.staff_num = stud_cour.staff_num
    )
    
    WHERE ((course.course_id)=([Forms]![Course Information]![course_id]))

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry I don't get it. your query has all manner of stuff not needed for your stated requirememnt. so if its not needed, then don't include it int he query.

    if the suggested approach doesnt' meet the requirement then you need to investigate why it doesn't

    if you need the lecturer details then perform another join to retrieve those details

    merely suggesting others should look at your query when we don't know why it isn't giving you the results you need is a fruitless waste of time. if you cannot articualte what the problem is then its hard for everyone, especially you, to start identifying a solution to that problem

    based on
    I just would like to create a report that shows which students is taking up the course based on the course_id
    you only require 3 tables, one of which doesn't have any columns returned in the result set, its merely there to provide the link between courses and students. based on your stated requirements thats all you need
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2014
    Posts
    5
    Hi,

    sorry for not explaining it to you thoroughly

    I need 4 tables because:

    1. Course - where I get the course's name
    2. Student_Course - where I get the student's test results as per course_id
    4. Instructor - where I get the instructor's name
    5. Student - where I get the student's name, staff_num

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so add a join to the other two tables
    Code:
    select c.course_name, s.fname, s.lname from course as C
    left join stud_cour as sc on c.course_id = sc.course_id
    left join stud as s on sc.staff_num = stud.staff_num
    left JOIN instr_teach as it on  c.course_id = it.course_id
    left join instr as i on it.instructor_id = i.instructor_ahid
    order by c.course_name, s.lname, s.fname
    you will need to add in the other required columns as part of the select statement. I used the table aliases (as ...) to make it easier to read and shorter to type
    s is the alias (shorthand for table stud
    sc for stud_cour
    c for course
    it for instr_teach
    i for instr
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •