access database 14

  • 1- Open and save the Access database on your machine
  • 2- Create the Relationships between the tables.

Queries:

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).

Hint:

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.

  • 3- Output the names of all students taking Biology.
  • 4-Output all Student_Ids whose Advisor_ID is not 3.
  • 5-Output the names of all students whose ID is more than 3000.
  • 6-Show the number of courses in the Course table.
  • 7-Show the names of all courses in the Course table sorted by the Course name.
  • 8-Output the names of all students whose ID is in between 3700 and 4000.
  • 9-Show the names of all students whose advisor is Jones.

Forms and Reports

  • 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.