Query with multiple tables
The power of “Access” is the ability to analyze information from multiple tables. “Relationships” between these “Tables” and “Queries” allow us to do so.
In this example, I use three related “Tables”: “Students”, “Results” and “Subjects” that I’ve created in the meantime. The table “Results” is a table that explains the “Relationship” between the different “Tables”.
The purpose of this “Query” is to view the results of each student for each subject.
Select the “Create” tab in the “Ribbon” and click the “Query Design” button.
In the “Show Table”, I add the above tables to the “Query” window.
The various fields in these tables can be seen in the image below.
In the “Students” table, I double-click “Last Name” and “First Name ” fields, in the “Results” table, I double-click the “Score” and “Grade” fields and from the “Subjects” table, I double-click the “Subject” field.
When a field from the table is inserted in the grid, you can see the position of this field in the grid.
- To select the column (place mouse pointer above the top of the column)
- When it changes in a downward pointing arrow, click the mouse pointer (the color of the column becomes black)
- Drag it to the desired position in the grid. The position in the grid is represented by a thick black line.
This is what I currently have.
When I click the “Run” button on the “Ribbon”, the result is displayed.
Save the “Query” with an appropriate name, by clicking the “Save” button in the ribbon.
For example, “score_and_grade_for_each_subject”.
You've completed Lesson 24 START NEXT LESSON