Microsoft Access Problem: Access tables: (d) When the query is executed, Access
ID: 3880789 • Letter: M
Question
Microsoft Access Problem:
Access tables:
(d) When the query is executed, Access will show a pop-up window for the user to enter a department code, such as 'CSCI in the example here Enter Parameter Value Enter advisors' deparment code: Entered by the user. OK When the user entered the department code, the query shows all faculty advisors of the department, together with their advisees, including the classes and grades they have taken, as below: Advisor Na Advisee last name Advsiee firs name classNumbe rgrade- anak Tanaka Tanaka Smith Smith Lee Lee CSC201A MTH101B MTH103C ra River Rivera Carol arol Jan Jane So 201A 1A 20 Jorginl Jorginlo id Dav 1AExplanation / Answer
The SQL query used to get the desired record is given below. 4 table are joined on common attributes to get the desired result.
SELECT f.name AS 'Advisor name',
s.lastName AS 'Advisee last name',
s.firstName AS 'Advisee first name',
c.classNumber
e.grade
FROM faculty AS f
INNER JOIN student AS s
ON f.facid = s.advisor
INNER JOIN class AS c
ON c.facid = f.facid
INNER JOIN enroll AS e
e.stuid = s.stuid
WHERE f.deptCode = 'CSCI';