Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You'll use queries extensively to explore your data, and you can actually take your analysis a step deeper by turning your queries into pivot tables. Pivot tables allow you to interactively analyze your data along more than two dimensions. Let's start by understanding our scenario and our goal. I have opened my Orders table, and I can see every order. The Date it was placed, the Customer who ordered it, the Products that they ordered, the Quantity that they ordered, as well as the OrderStatus. If I go down to my Queries, and I double-click on OrderTotals, I can see similar information with the addition of a grand Total row for that particular order.
When I look at it in Design View, here is each order with a calculation in the last column to find out the Total for that order. But if I go up to that toggle button, and I dropdown the bottom half, I have some additional options. Let's click on PivotTable View. When we choose it, a white grid appears with placeholders at the top and left. There is also a Field List on the right-hand side. If you don't see the Field List, there is a button on the upper left of the ruler, so you can turn it on and off.
Find your Company field. Click on it, and drag it to the left-hand side where it says Drop Row Fields Here. Now we have a list of all of our companies. Click on Product, and drag it up to where it says Drop Column Fields Here. We have each of our products. We can take this even deeper. If I pick up Size and drag it next to Product, now each of my Product is broken up by the Size that has been ordered. Go to your totals and drag it into the big middle area where it says Drop Total or Detail Fields Here.
Now have a new calculation. I can see by Company, the grand total that they've spent on each of my Products by Size. You can really get creative with pivot tables. I can even pick up my Quantity, and drop it into my Totals row, and now I know the grand total for how many bottles of that product they've ordered. This new analysis will be saved when you save the query. So once you've created it, you can toggle back and forth between your Design views, your Datasheet views, and your PivotTable View, and this analysis will always be there.
Creating a pivot table from your query will allow you to do detail analysis anytime on multiple dimensions simultaneously.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64651 Viewers
80 Video lessons · 124285 Viewers
52 Video lessons · 60227 Viewers
59 Video lessons · 46062 Viewers