You can find data on this link : https://drive.google.com/file/d/19W_rUJP3StkNyCYgt…
- Open and save the Access database on your machine
- Create the Relationships between the tables.
- Output the names of all students taking Biology.
- Output all Student_Ids whose Advisor_ID is not 3.
- Output the names of all students whose ID is more than 3000.
- Show the number of courses in the Course table.
- Show the names of all courses in the Course table sorted by the Course name.
- Output the names of all students whose ID is in between 3700 and 4000.
- Show the names of all students whose advisor is Jones.
- Create a Student form
- Add 6 new student entries using the Student form and populate all the fields (including the State and Zip) in the form for the new students and the existing students.
- Go to the datasheet view of the Student_Course and Student_Advisor table and add 2-3 entries in both these tables for the new students (Do not add new courses or advisors, use existing ones only). Populate the scores field in the Student_Course table as well (make up some scores).
- Create two sub-forms to show the Course and Advisor information for each student on the Student form.
- Create a report to show the individual scores obtained by each student in each course they took. The student_id and name should not be repeated with the scores. Show in the report the average score for each student (use the Report layout view) at the end of each student record and the average for all students at the bottom of the report.
Using the Access Query design option, run all the following 7 queries (questions 3 to 9):
Save the queries as Query(q.no.).
(Example: Query3 for question 3 below).
For queries 4,5 and 8 use comparison operators (research online if necessary): <, >, =, >=, <= in the criteria
Use the Sort attribute in query design for question 7
Use the Totals feature in the Show/Hide block in the query design view for question 6.
Forms and Reports
Save the database and upload it. Make sure Access is not open when you upload it.