When we create a two-table query within Access, we need to ensurethat a relationship exists between the two tables prior to creating thequery. This is an important rule as the results will neither query norproduce the correct output if the tables are not related via a commonkey such as Code, in the databaseexample we are using for this demonstration. To create such a query,follow these steps:
- Make sure you have the database open from the previousexample. Click on the
Createtab, then choose Query Design.
- Select the table on which you wish to base the new query. ClickAdd, then repeat the step to add asecond table. For this example, we are using the
StudentsTbl, the ResultsTbl, and the SubjCodesTbl tables. Click on Close.
- Select fields from both tables’ lists by double-clicking to add themto the query grid. As we are requiring a query to produce subjectresults for all our grades, we will add the following fields from eachof the tables listed. Please view the following screenshot to add therelevant fields to the query grid:
- Add the criteria for the specific query requirements in the criteriarow.
- From the Design tab, locate theResults group. Click the
Run icon. The query results are displayed inDatasheet View.
- Click on the Save icon on the QATand enter a query name called StudentMarksQry.