Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you'd like to run statistical analysis on your database, you can use a Totals Query. Create a new query by clicking on the Create tab and then on Query Design. Add the Products table and the Orders table, and click Close. We sell olive oils in 8, 16, 32, 64 and 128-ounce bottles. We're going to analyze our products, grouped by size. Double-click on Size, and then on Price, and then on Profit, and then double-click on Profit a second time.
On the Orders table, double- click on OrderID and Quantity. Up on the Ribbon, there is a Totals button. Click on it, and you'll get an additional row down here on the grid. It defaults to Group By. We'll leave Size as Group By. This will group the bottles by size, no matter what kind of oil it is. Under Price, choose Average. Under Profit, click the dropdown and choose Min. On the second Profit from the dropdown, choose Max.
From the OrderID, choose Count. Under Quantity, select Sum. Now click on the View. We can see, by size: our average price, our smallest profit, our largest profit, the number of orders that were placed for each kind of bottle, and the total number of bottles sold for each bottle size. Now, it came up with codes across the top that are Access programming.
We can clean that up a little bit. Go back to the Design View, and before Price, type in "Average Price:". By typing in a name and a colon, it tells us that this is going to be the name at the top of the column. Before Profit, type "Smallest Profit:". Before the second Profit, type "Largest Profit:".
Before OrderID, type in "Number of Orders:". Then before the Quantity, type in "Total Number Ordered:". Now, when I go to View and run it, when I resize my columns, I have English captions for my statistics, which will help you understand what you're looking at. Let's go up to the Save, and title this Order Analysis Query, and click OK.
Every time you run this statistical query, you'll get completely up-to-date numbers for your product line.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64708 Viewers
80 Video lessons · 124322 Viewers
52 Video lessons · 60258 Viewers
59 Video lessons · 46091 Viewers