• MS Access 2: relationships and reports
Overview
This lab shows how to join tables in Access and create reports.
Procedure
1. Create a new Access database and save it as CS102_Ex8_YourUserName.accdb
2. Change field ID in the default table to Student ID and add two more text fields, "Student Name" and "Course Code"
3. Add 5 fictional records to the table. Use 3 different course codes (ie. CS102, CS122, IT310 etc)
4. Save the table as "Students"
5. Create a new table and rename its field ID to Course ID
6. Add two more text fields, "Course Code" and "Course Title"
7. Add five records to this table and save it as "Courses". Make sure to have the same course codes that you used in the table "Students"
8. Create a new table and rename its field ID to Instructor ID
9. Add two more text fields, "Instructor Name" and "Course Code"
10. Add five records to this table and save it as "Instructors". You may use an instructor's name more than once to specify that she teaches multiple courses
11. Switch to the "Database Tools" tab and click "Relationships" button. The ribbon should change to the Relationship Design
12. Add (double-click or select and click "Add") all three tables ("Courses", "Instructors" and "Students") to the canvas and close the "Show Table" dialog
13. Click "Edit relationships" button and create a new relation
14. Left table name is "Instructors", left column name is "Course Code"; right table name is "Courses", right column name is "Course Code". Click "Create" to confirm relation
15. Create another relation with Courses\Course Code on the left and Students\Course Code on the right
16. Create a new simple query using Query Wizard and include "Student Name", "Course Title" and "Instructor Name" n the selected fields
17. This query does not have a criteria and displays all students, courses they are taking and instructors teaching those courses
18. With the query results open, click "Report" button and preview the generated report. Switch to the "External Data" tab and export the report as PDF
19. Save (Publish) the report as CS102_Ex8_YourUserName.pdf
20. Save your work.
Result
• 3 tables with specified fields
• Relationships between tables are properly created
• Query displaying all students, their courses and instructors
• Report displaying the query results
• Save your file and submit it to the appropriate area.
• Project: Advanced Database Management
Description
1. In order to complete this assignment you need to create a new Access database and download a template file (CS102_Access2_Proj_Template.xlsx)
2. Import data from the "winners" worksheet of the template file into a new table of the Access database using an External Data import Wizard. Note that columns in the template file contain headers. Save the table as "Winners"
3. Import data from the "locations" worksheet of the template file into a new table of the Access database using an External Data import Wizard. Note that columns in the template file contain headers. Save the table as "Locations"
4. Join two tables by Game number
5. Create query that displays a game number, a winning team and the game location.
6. Create a report that contains results of the query and save it as PDF
7. Submit CS102_Access2_Proj_YourUserName.accdb and the resulting PDF file
Requirements
• Two tables created from the provided data
• Query runs over joined tables
• The query produces the required result
• A report is generated based on the query results.
Attachment:- Template.rar