Start learning with our library of video tutorials taught by experts. Get started

Access 2010: Queries in Depth
Illustration by Neil Webb

Exploring the property sheet


From:

Access 2010: Queries in Depth

with Adam Wilbert

Video: Exploring the property sheet

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.
Expand all | Collapse all
  1. 9m 9s
    1. Welcome
      1m 10s
    2. Using the exercise files
      41s
    3. Introducing the database
      4m 29s
    4. Previewing the course
      2m 49s
  2. 17m 17s
    1. Understanding queries
      3m 31s
    2. Following naming conventions and best practices
      2m 56s
    3. Using the Query Wizard
      5m 21s
    4. Exploring the design interface
      5m 29s
  3. 26m 39s
    1. Defining criteria
      5m 40s
    2. Understanding comparison operators
      3m 19s
    3. Defining the column headers
      2m 49s
    4. Exploring the property sheet
      7m 32s
    5. Printing query results
      2m 41s
    6. Working with joins
      4m 38s
  4. 14m 14s
    1. Understanding parameter queries
      4m 27s
    2. Obtaining parameters from forms
      5m 17s
    3. Creating a combo box
      4m 30s
  5. 23m 24s
    1. Understanding the Totals field
      5m 31s
    2. Creating aggregate calculations
      3m 31s
    3. Exploring the Expression Builder interface
      4m 28s
    4. Using mathematical operators
      5m 46s
    5. Applying text functions
      4m 8s
  6. 24m 23s
    1. Understanding dates as serial numbers
      2m 42s
    2. Specifying a range of dates or times
      3m 47s
    3. Formatting dates
      4m 31s
    4. Using other Date/Time functions
      3m 47s
    5. Defining today's date
      2m 41s
    6. Calculating time intervals
      6m 55s
  7. 20m 9s
    1. Introducing the conditional IIf function
      2m 57s
    2. Creating an IIf function
      7m 31s
    3. Nesting IIf functions
      4m 57s
    4. Using the Switch function
      4m 44s
  8. 20m 41s
    1. Understanding the reporting tool
      2m 13s
    2. Building the form
      6m 57s
    3. Building the query
      5m 4s
    4. Building the report
      3m 30s
    5. Finalizing the reporting tool
      2m 57s
  9. 25m 37s
    1. Finding duplicate records
      2m 17s
    2. Identifying unmatched records
      2m 29s
    3. Creating crosstab results
      2m 57s
    4. Creating backups
      1m 29s
    5. Creating update queries
      3m 22s
    6. Making, deleting, and appending records
      5m 36s
    7. Uniting tables
      3m 16s
    8. Embedding SQL code in queries
      4m 11s
  10. 1m 0s
    1. Next Steps
      1m 0s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Access 2010: Queries in Depth
3h 2m Intermediate Jun 16, 2011

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.

Topics include:
  • Naming conventions and best practices
  • Working with joins
  • Using comparison operators
  • Defining criteria for select queries
  • Creating parameter queries
  • Creating calculated fields
  • Working with dates and times
  • Using the Expression Builder
  • Creating conditional statements
  • Making, deleting and appending records
  • Building reports
Subjects:
Business Databases
Software:
Access Office
Author:
Adam Wilbert

Exploring the property sheet

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.

 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Access 2010: Queries in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Notes cannot be added for locked videos.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.