Applying the Query Wizard for multiple Tables
Using a Query combining several Tables has great advantages. Imagine we need to know information on our Clients, with an added topic of needing to know the vendor who worked with them, as well as the date in which the sale happened. It is undeniable that having all of this data in a single Table is simpler than having 2 Tables and looking for the information we need one-by-one. This is what we are going to learn in this lesson. The first thing we need to do, is creating a Query.
We are going to use the Query Wizard to combine several Tables; just click on the Query Wizard icon.
In the Query Wizard window you'll need to go through a few steps to create the Query. First, you'll get the "New Query" window. Here, we will simply select the option "Simple Query Wizard". We won't go into details on the other 3 options for the time being. Next, you'll get the "Simple Query Wizard" window. Here, you need to select the Table we want to use for our Query. This time, we will be using the "Orders" and "Clients" tables. NOTE: I added Records to our "Orders" Table to make this example. Some vendors and sales dates.
Then, we will select the fields we want our Query to show from the Available Fields section to the Selected Fields section. We can add them all at the same time using the second button in the centre or one by one clicking on the first button. From the "Orders" table, we will select the fields Client's code, Vendor, and Sale Date. Now, without clicking on "Next", we will select the fields from the "Clients"table. We will select Company, Client's First Name, Client's Last Name, and Position.
You probably noticed that the Available Fields changed when we chose a different table, but as we added the new ones, the previous ones didn't disappear. We can also choose the order in which we want them to appear, again by using the first button in the centre. We added the fields "Vendor " and "Sale Date" right after "Client's Code". Once we have done this, we click on "Next" to go to the next window. You are probably familiar with this window. It asks us for the Title of our Query. Write the title you want and then open the Query by clicking on "Finish".
And, this is the result of our Query:
As you can see, it's pretty simple. With just a few clicks, we can perform Queries in Multiple Tables. We can also create Queries combined on other Queries. The procedure is the same. In the window where we selected the Tables, we need to select the Queries we have available.