Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
Every object and control in Microsoft Access has a set of properties that can be defined to give you more control over your database. Queries and the fields within your queries are no exception. By understanding the options available within the query property sheets, you will gain more control over the queries that you construct. In this movie, we will look at some of the properties that we can control for each of the fields in our query. Now, if you've saved your query for the last exercise, you can continue using that here. I've opened the qry_ProductsWithAlias query. We could see that we've already changed our field headers. Now we need to go ahead and format our data.
My Wholesale Cost and Retail Price represent dollar values, and I want to format them, so that all the decimal places are aligned vertically. MarkupDollars and MarkupPercent are calculated fields from the data table. Let's take a look at that so we could see how these are formatted. I will go into my Chapter 1 folder and open up the Products table. We will switch in to Design view using the Design button on the Home ribbon. Access is going to give us a warning message saying that this table is currently being used by our query that's opened. In order to switch into Design view we can only view the table in Read Only mode, and that's fine. We just want to take a look at the back end. Go ahead and say Yes and we'll take a look at the data types for each of our field.
MarkupDollars and MarkupPercent use the new Calculated data type that's new for Access 2010. Here we are taking the price and subtracting the cost to get the markup in dollars. You can see that the Format is set to Currency and is set to 2 decimal places. MarkupPercent is also a calculated data type. It takes the price and divides by cost to get percent. The Format is set to Percent. When we view the data sheet for our Products table we will see that those formatting rules apply to the column. When we make a query based off of this table, the formatting follows.
Back in our query here, we could see that the formatting is the same as it appears in the table. Let's use our query properties to format Wholesale Cost and Retail Price to match. We will switch into Design view, and we'll open up the Property Sheet. There are a couple of ways we can open the Property Sheet. We have a button here on the ribbon called Property Sheet and that will toggle it open or closed. You could use the F4 key on your keyboard to open or close the Property Sheet. We can also use Alt+Enter on our keyboard. So hold down the Alt key and press Enter to toggle the Property Sheet open or closed.
So whatever method you prefer, go ahead and open up the Property Sheet and we'll take a look at the properties for the field that's selected. Currently, we have Product Name selected and the Property Sheet opens up to the field properties for this field. Let's switch to a Numerical datatype so we can see all the values available. Click on Wholesale Cost. The Description field here is a great place to sort notes about your query. Particularly when we move into creating calculated fields, you can use the Notes field to document what this specific calculation is doing. The Format field controls how numerical data will be displayed and there are a range of options available in the drop-down menu.
For Wholesale Cost, we will go ahead and select the Standard number. The Decimal Places controls how many digits after the decimal will appear. We will go ahead and select 2. Input Mask controls how data is entered into the query and how that is saved into the table. Now, I've honestly never come across to reason why you would want to use this property at the query level. This functionality is much better applied at a table level instead. So I would recommend avoiding this one unless you have a really specific use case that requires the data entered through the query gets formatted differently than data entered into the table.
The Caption field acts just like the aliases that we've added to our headers in the field grid. The difference here is that caption only appears in the Property Sheet and in the Datasheet view at the very top. It doesn't appear here in the field in the Design view. Now I prefer to use an alias so I can see it here in Design view instead of having it hidden in the Property Sheet. Finally, smart tags are a way to link very specific data types to external content. For instance, you can dynamically link a stock ticker symbol to a live quote from Microsoft's msnmoney.com web site.
You will click in this field and click the Build button to make that link. Now, we've gone ahead and changed the Wholesale Cost field to a standard number with two decimal places. Let's do the same for Retail Price. I will click on Retail Price, change the Format to a Standard number, and the Decimal Places to 2. Let's run our query to see the results. We will see that our decimals are all right-aligned and the numbers are much easier to read now. Now, there are different schools of thought on whether you should include the dollar sign or not. I personally prefer to not have the dollar sign show up in the column of data. I am going to go ahead and remove it here from the Markup field.
Let's go back into Design view. We'll scroll across in our Field grid until we get to MarkupDollars. I will change the format for this field. I will change the Format from the Currency that's it's currently set at from the data table to a Standard number. We will change this to 2 decimal places as well. We will click Run and you will see that the dollar sign has now been removed from this field and I think it makes it much more easy to read all of the data. Let's go back into Design view and take a look at the query level properties. Design view. And if you click anywhere in this blank area up here in the Table pane, you will switch to the query level properties.
Now, the properties for the overall query mainly offer high-level record management options that are useful only to select users like Visual Basic programmers. But there is a couple here that can apply to a wider audience. Again, the Description field is useful for documenting what the query does, who made it, and why. The Default View allows you to specify if we like the query to open up in PivotChart or PivotTable view by default when you double-click it on the navigation pane. We will leave it as Datasheet view. The Output All Fields property is currently set to No. When set to No, Access will respect the show checkboxes here in the Query Designer.
If you change this to Yes, regardless of whether you have Show turned off or not, Access will display all of the fields in your query. I'll leave this on No. The Top Values property is linked to the Return value on the Query Setup section of the Design ribbon. We could change this to only return a specific number or a specific percentage of records within our dataset. For instance, if I only want to view the top 25 records, I could select 25. When I make that change, you will notice that the Return section up here changes as well. Now you are not only limited to the values that appear on the list.
For instance, I could type in 30, and Access will show me the top 30 records, or I could type-in 7% and Access will tell me the top 7% of the records. I will go ahead and change this back to All and you will see that changed in the Property Sheet as well. The Recordset Type is probably the most useful of the query level properties. By default, a query's datasheet or its dynaset is a live link to your data tables. You can make edits to the query results that will update the data in the tables. Let's take a look at that. If I click to run my query, I can go in here and change any of these values.
For instance I will change Wholesale Cost for this particular oil to $8 and you see that I am allowed to make that change and that change is made into the original data table. Let's go back into our Design view. I will click up here to change the query level properties and I will change my Recordset Type from Dynaset to Snapshot. With Snapshot selected, the datasheet will be in a Review Only mode and users will not be permitted to change the data in the link tables. So with this change made, I will go up to the Run menu and if I try to make a same change, let's change it back to 7, Access will give me a warning beep saying that I can't change that value.
So depending on the query and the data types in the fields that you are working with, the query Property Sheet will change to show you only the applicable parameters. It's a good idea to make sure that your queries are well-documented in the Description view, so that anyone that needs to review your work down the road, maybe months or years later, will have a solid understanding of what you did and how it works.
There are currently no FAQs about Access 2010: Queries in Depth.
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.