Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the last movie, we saw that the Query Design Wizard used a function to calculate the count to the number of records within our query. You can see that here. I currently got the qry_ OrderHistory query open in Design view. In the very end here, I got the Count function. Let me right click in this field and I'll say Zoom to open it up in the Zoom box. So the way that Access's wizard created this counting field is it used a function called Count. Now functions are always written with the same formatting.
You have the name of the function and then in parentheses wrap whatever you want to apply that function to. So we had a count function that's counting everything with the asterisk wildcard. We also see that Access's wizard provided us with an alias. Count Of tbl_Products:. So let's take a look at how we can apply functions to our database. We'll go ahead and say OK to close the Zoom box. Let's go ahead and close this query. We don't need to save it. I'll create a new query in Design view. We'll add a couple of tables here.
We'll add the Direct Customer table and the Orders table and close Show window. We'll add FirstName and LastName to our query and then in the third field a we re going to use a function to count up the number of transactions. Let's right-click and go the Build. This will open up the Expression Builder. Now the Expression Builder window has a couple of functions. First, the top pane allows you to write in SQL syntax or functions that will then populate into this field.
They'll populate down here. Then there is three windows on the bottom. The bottom window on the left gives you a list of all of the objects within our database. So it's just the Two Trees.accdb file. If I expand that open, I have access to everything within my tables, queries, forms, and reports. We also have a list of functions and we have our built-in functions. These are the functions that are built into Access. If we've written custom functions, those would appear into the Two Trees folder and if we are connected to a SharePoint site, we might see some things under Web Services. But for now, let's just take a look at the function that are built-in with Access.
We'll click on Built-In Functions and the second two panes populate. Let me go ahead and expand this window a little bit so we have more room. Middle window shows us the categories that the expressions fall into. We have Arrays, Conversion, Database, Date/Time, etcetera. Towards the bottom, we can see a category called SQL Aggregate. If we click on that, the third window populates with all of the functions within that category. And these are the ones we saw before in the drop-down menu. Average, Count, Max, Min, Standard Deviation, Sum, and Variants.
So let's go ahead and use the Count function to count the number of records in our database. We'll double-click on Count to add the expression to our Build window. We could see that Access helps us out with some of the syntax that says we are going to apply the Count function and we need to supply an expression that we want to count up. So it's saying Count. Well, what are we going to count? If we click in there, we can provide that value. Click on the expression and the whole section will highlight and then we'll drill into our tables to find the piece of what we want to count. We go into Orders table and let's count up the Customer IDs that appears in the Orders table.
We'll go ahead and say OK and run our query. Now Access is going to tell us that we try to execute a query that does not include the specified expression FirstName as part of an aggregate function. And that's really just a complicated way of saying that Access doesn't know how we are grouping things together. We need to first to activate our Totals column anytime we are using a function. Let's go ahead and say OK. It will turn on our Totals column. If we try and run it again, we'll get a second message. Access cannot have an Aggregate Function in a GROUP BY clause.
You need to change this GROUP BY to Expression so that Access knows that we want to calculate those value in a group based on those values. We'll go ahead and say OK and change our GROUP BY to Expression. Now if we try to run it, we'll get the calculated results. Let's go ahead and sort our expression, the Count column, and we'll sort this Largest to Smallest, and we'll see the Tobias Rivera has had the most number of transactions with our company. He's had 9 total orders.
As you get more experience writing out the expressions that you want to use, you might find it quicker to just write them out by hand directly in the grid. But for learning the proper syntax, troubleshooting, or for discovering how new functions work, the Expression Builder is a fantastic tool.
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.