Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Creating Queries in Design View gives you complete power and flexibility in analyzing your data. Let's start with looking at the query that we already made. Right-click on the Sales Reps Phone List Query and choose Design View. The top part of the window is where you show your tables with their fields, and the bottom is the grid where you specify the fields you want to see. So here is our Sales Rep table, with all of its fields, and the grid below, with just the five fields we wanted to see. If it asks if you want to save the changes, say No. So let's take this a step further.
Let's make a Query to analyze our sales in Maryland. We would need to pull our Maryland customers and look at their orders. Here's how to do it. Click on the Create tab and then choose Query Design. We'll get a new blank Query. The first thing we need to do is add our tables to the query. Now when you're doing this, only add the tables that contain the fields that you need. If you add extra tables here, it can distort your results. If you accidentally open up an extra table, click on it and press Delete to remove it. Double-click on the Customers Table. To add the Orders Table, click on Orders and click Add.
You can add your tables either way. Click Close. Now, stretch out the bottoms of the boxes so that you can see them. Hold your cursor over the bottom border of each box and pull down. You can also resize your grid by moving this up or down, as is practical. Now let's add fields in a few different ways. One is to double-click. Double-click on Company and it will appear in the grid. You can also drag the field down. Try that for State.
Click in the third column. You can also click on this dropdown arrow to pick a field, but because we have two tables showing, that's a lot of fields to scroll through. So double-click on OrderDate. Next, we want to see the Product Name, but in the Orders Table, the Product is shown as an SKU number. So if we add Products from the Products Table instead, we'll get the actual name of the oil. So click on Show Table and double- click on Products, then click Close.
Add Product to the grid and then Price. Before we add in a criteria, let's test this and see if it works. There are two buttons over here on the left. View allows us to see the query results. If it's a Query that performs an action, you'd have to click this Run button to make it finish. Click on the View button, and we can see the Company who ordered, the State, the Date of their order, the Product they ordered, and how much they paid. But let's refine it some more.
Go back to the Design View. First, let's just see customers from Maryland. Let's reorder the columns so that State is first. Click one time on this thin gray line at the top of the column. Then click on it again and drag it before the Company column. You can see a dark line as you move. Now under the State column, in this Criteria line, type in MD and then hit the Tab key. The cursor moves to the next column, and Access automatically put quotes around the letters because they are text.
This tells Access to find a literal match. Also, let's sort the Company alphabetically. Click on this Sort row. Drop it down and Sort Ascending. Let's also Sort by the OrderDate as well so that all of the orders from one company show up in the order they were purchased. Now I also want to add in the Product's Size. Pick up Size from the Product Table and drop it on top of Price. Size will appear, and Price will move over. Now let's see how that works. Click on View.
I can see all my orders from Maryland, what companies are there, the dates that they ordered, the products that they ordered, their bottle size and how much they paid. I notice that Maryland favors 8-ounce bottles. So let's Save this query and call it Maryland Orders. Using the Design View to create your queries gives you a lot of control over your database analysis.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.