Using the criteria row in conjunction with the totals row in the query design grid can lead to some unexpected results. In this movie, Adam demonstrates how to use the WHERE option in the totals row to indicate filtering criteria that should be removed from the process of grouping records. This allows the query to both filter and group simultaneously.
- [Voiceover] The Totals Queries allow you perform…aggregate calculations across a group of data…that shares some common attribute.…We just created a query that adds the sum total…of all of our sales and reports the value by state.…Now let's take a look at what happens…when we try to add additional filtering criteria…to our query.…Let's scroll down and find that query here.…It's called Total Sales By State.…I'll right-click on it, and go straight into Design View.…Now you might remember, if I run it,…you'll see that we have each state here,…and then we have the sum total of how much money…they've contributed to the bottom line of H+ Sport.…
Let's go back into Design View,…and let's say I wanted to find out…just the total contributed…by the three continental Pacific states.…In this case, that'd be California, Oregon, and Washington.…Now you might think, okay, we're going to come in here…to the criteria and just start adding these in.…I'll go ahead and type California.…On the next line, on the Or line, I'll type in Oregon.…
- Creating a query with the wizard
- Defining query criteria
- Using comparison and wildcards in criteria
- Working with joins
- Creating parameter queries
- Using the built-in functions in Access
- Summarizing data
- Aggregating records with totals
- Working with dates
- Creating alternative queries: unmatched, crosstab, and more
- Writing queries with SQL
Skill Level Intermediate
1. Introduction to Access Queries
2. Creating Simple Select Queries
3. Creating Parameter Queries
4. Use the Built-In Functions
5. Aggregating Records with a Totals Query
6. Working with Dates in Queries
7. Understanding Program Flow Functions
8. Alternative Query Types
9. Writing Queries with Structured Query Language
Explore the basics of SQL5m 59s
10. Useful Query Tricks
- Mark as unwatched
- Mark all as unwatched
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.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.