Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
When creating queries using calculated or filtered records, it's always a good idea to properly document your workflow so that your results will be easily understood by other users. One way to do this is to rename the field headers in your queries to give them a more meaningful and accurate name. Let's go ahead and build a query that will demonstrate this. We'll go up to the Create tab and in the Queries section click on Query Design. We'll add our Products table by double- clicking and then we can say Close Show Table. I'm going to go ahead and open this up and let's add a bunch of fields from our table to our query.
I'll double-click on ProductName, Ounces, Cost, Price, MarkupDollars and MarkupPercent. Now these field names come directly from the table headers. ProductName, Ounces, Cost, Price, MarkupDollars, MarkupPercent. You will notice that they are written with no spaces and they are not exactly in the most human-readable format. We can change that by using a field alias and it works like this. If we click in the very beginning of the name, you could type in an alias followed by a colon and Access will use that in the table of results from the query.
So instead of ProductName as all one word we can say Product Name with a space followed by colon. Ounces, well this is our size field and Ounces is a little bit confusing. So we'll say Size in ounces followed by a colon. Our Cost? Well this is really our wholesale cost so I'll write in Wholesale Cost followed by colon. The Price field represents our customers' price.
So I'll click in the beginning and write Customers Price, followed by a colon. MarkupDollars and MarkupPercent are both markup fields but they have different units. So MarkupDollars I'll specify as dollars with a dollar sign and MarkupPercent, I'll specify the units as percent with a percent sign followed by a colon. Now if I go ahead and run this query, you'll see that we have new headers at the top of our columns.
Product Name is 2 words. Sizes is with ounces specified. Let me double-click here just to widen that. Wholesale Cost, Customers Price, Markup in dollars and Markup in percent. Later on in this course, you'll see that when we create a calculated field Access is automatically going to add an alias Expr1, Expr2, etcetera to denote an Expression 1 or Expression 2. Most of the time those are not going to be helpful at all so we'll be using these aliases to redefine Access's default.
By clearly identifying the fields returned in your queries you'll aid other users that will be working with your database and will help clearly communicate the meaning of the field in forms and reports.
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.