To view Part-1 of this series, (by Ben Beitler ), Click here >>

Creating A Multi-Table Query

Since we have planned our question, we are prepared to design and run it. If you have made written plans for your query, make certain to reference them frequently all through the ms access query design process.

These are the steps in creating a multi-table query:

1. Choose the Query Design from the Create tab on the Ribbon.

2. In the Show Table dialog box that shows, choose each table you want to include in your query, then click Add. After adding the tables, click Close. In our example, we needed information from the Customers and Orders table, so we’ll add them.

3. The tables will be displayed in the Object Relationship Double-click the thin section of the line that connects the two tables to edit its join direction.

4. The Join Properties dialog box will display. Choose an option for the join direction.

• Select option 2 for a left-to-right join. In our query, the left table is the Customers table. Selecting option 2 would mean all customers who met our criteria, whether they had placed an order or not, would be included in the output. This is not the data we want to see.
• Select option 3 for a right-to-left query. Our right table is the Orders table so choosing this option will let us work with records for all the orders and only the customers who’ve placed orders. This is the option that fits our query.

  

  5. In the table windows, double-click the field name/s you want to include in your query, so it will be added to the design grid that’s at the bottom of the screen. In the example that we have, we will include most of the fields from the Customers table: First Name, Last Name, Street Address, City, State, Zip Code, Phone Number, and Email. We will also include the ID number from the Orders table.

  

  6. Set field criteria by entering the desired criteria in the criteria row of each field. We want to get the list of customers who purchased from our shop and are living in Delaware, so we will type “Delaware” in the City We will also set another criteria in the criteria row to include the zip code for Delaware City which is 19706 to only get the records of the customers who live in the Delaware City in DE and not in OH.

  

  7. After you set your criteria, run the query by clicking the Run on the Query Design.

  

  8. The results will be shown in the query’s Datasheet view, which looks like a table. Save your query by clicking Save on the Quick Access toolbar. When prompted to name it, type the desired name, and click OK.

  

Tags: access database design, access form design, how to create an access database, Microsoft Access 2016, microsoft access database forms, microsoft access databases, microsoft access queries, microsoft access tutorial, ms access forms, ms access queries