In the previous lesson, you learned how to create a simple query with one table. Most queries you design in Access 2013 will likely use multiple tables, allowing you to answer more complex questions. In this lesson, you’ll learn how to design and create a multi-table query.
Making a multi-table question
Since we’ve arranged our inquiry, we’re prepared to plan and run it. In the event that you have made composed plans for your question, make certain to reference them frequently all through the inquiry configuration process.
To make a multi-table question:
Select the Query Design charge from the Create tab on the Ribbon.
In the discourse box that shows up, select each table you need to incorporate into your question and snap Add. You can press and hold the Ctrl key on your console to choose in excess of one table. When we arranged our inquiry, we chose we required data from the Customers and Orders table, so we’ll include these.
After you have included the greater part of the tables you need, click Close.
The tables will show up in the Object Relationship sheet, connected by a join line. Double tap the thin area of the join line between two tables to alter its join heading.
The Join Properties discourse box will show up. Select a choice to pick the course of your join.
- Pick choice 2: for a left-to-right join. In our question, the left table is the Customers table, so picking this would mean the greater part of the clients who met our area criteria—regardless of whether they had submitted a request—would be incorporated into our outcomes. We would prefer not to pick this alternative for our inquiry.
- Pick choice 3: for a right-to-left join. Since our correct table is our Orders table, choosing this choice will give us a chance to work with records for the greater part of the requests and just the clients who’ve set requests. We’ll pick this choice for our question since this is precisely the information we need to see.
- In the table windows, double tap the field names you need to incorporate into your question. They will be added to the outline framework in the base piece of the screen.
In our illustration, we’ll incorporate the vast majority of the fields from the Customers table: First Name, Last Name, Street Address, City, State, Zip Code, and Phone Number. We’ll additionally incorporate the ID number from the Orders table.
Set field criteria by entering the coveted criteria in the criteria column of each field. We need to set two criteria:
Initially, to discover clients who don’t live in Raleigh, we’ll compose Not in (“Raleigh”) in the City field.
Second, to discover clients who have a telephone number start with the region code 919, we’ll compose Like (“919*”) in the Phone Number field.
After you have set your criteria, run the question by tapping the Run summon on the Design tab.
The inquiry results will be shown in the question’s Datasheet see, which resembles a table. On the off chance that you need, spare your inquiry by tapping the Save order in the Quick Access toolbar. At the point when provoked to name it, type the coveted name and snap OK.
Presently you know how to make a multi-table question. In the following lesson, we’ll discuss more question outline choices that can make your inquiry much more intense.