Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
It's possible to run calculations within your queries. Let's make a query that gives us the total price on each order. Create a new query by clicking on the Create tab and then on the Query Design button, add the Products table, the Orders table, the SalesReps table, and the Customers table, and then click Close. Now double-click on the OrderDate and instead of choosing the company from here, that would give us just their ID code, we want their whole name, so let's pick it from the Company field in the Customers table.
Same thing for the Product. I could pick the product from here, but again it would give me the code. So let's pick the product name from the Products table. Now let's pick the Size and the Price, which will give us the details about what they ordered, and then from the Orders table, pull in the Quantity. Now I like to know which sales rep helps them and since the SalesReps is associated with the Customers, I could pull the SalesRep from here, but instead I am going to pull their LastName from the SalesReps tables, so I will get their actual name.
Now let's sort this by OrderDate > Ascending and then by Company also Ascending. Now, let's find the total for their order. Click in the last column and then click on the Builder button up here in the Ribbon. Type in "Total:" that tells Access that this will be the field's caption. Now, click the Plus sign next to the file name down here in the Expression Elements and then click the Plus sign next to the Table so that we can see our tables.
On the Products table, double-click on Price. The code will appear in the window. Erase the Expr, since we put in our own caption. The table and the field names are in square brackets. The Table is here. The exclamation point separates it from the Field Name. Click after the two and type in an asterisk for multiply. Then go down to the Orders table and double-click on Quantity. So now we have the total will equal the Price of the product times the Quantity ordered and click OK.
Let's View the results. Click up on the View table. So here's the OrderDate, the Company who ordered, the Product that they ordered, what Size bottle they got, their Price, their Quantity, who helped them, and the Total for their order. Let's save this query as Order Totals. Calculation queries allow you to do any kind of math between any fields in your database.
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