In this movie, Adam demonstrates his solution to the fifth challenge. He builds a query that uses the IIF() function to determine pricing for the H+ Sport anniversary sale, and illustrates a common tripping point when working with multiple conditions. Afterwards, he demonstrates an alternate method using the Switch() function.
- [Voiceover] For this challenge we are tasked with…producing a price list for all our products during a sale.…Here's how I'd go about it.…We would start, of course, with our Query in Design view.…We're taking a look at our product details,…so we'll add that table.…And we'll just go ahead and add all of these fields…down into our query design grid.…Let's go into the next empty field,…I'll right click and choose Build…to launch our expression builder.…So the question is asking to find all the products…that are both rose or teal,…and then we'll apply one price to them.…And if the product is any other color…we'll apply a second price.…I'm gonna show you a couple of ways to do this.…
The first way is gonna use the IIF function,…which we can find in the Program Flows group here and IIF.…Now the expression is going to be evaluating…the product's color, so let's start there.…We'll go into our database.…Go into the Tables, go to our Products,…and put in the reference to the color field there.…And what we wanna do is test to see…
- 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.