2014
Lynda
Adam Wilbert
4:23
English

Get more out of your Access database than just the data you put into it, using queries—tools that help you translate complex raw data into information you can use to make better decisions. Join Adam Wilbert, as he explains how to create real-world queries to filter and sort data, perform calculations, and refine query results with built-in functions, all while offering challenges that help you master the material. Find out how to identify top performers, automate repetitive analysis tasks, and increase accuracy and consistency in your database using program flow functions.

Introduction
Welcome
Using the exercise files
Introducing the database
1. Creating Queries in Access
Understanding queries
Using the Query Wizard
Exploring the design interface
2. Creating Simple Select Queries
Defining criteria
Understanding comparison operators
Renaming the column headers
Exploring the property sheet
Working with joins
Challenge: Find the top ten employees
Solution: Find the top ten employees
3. Creating Parameter Queries
Understanding parameter queries
Obtaining parameters from forms
Creating a combo box
Challenge: Build a query that produces an employee phone list
Solution: Build a query that produces an employee phone list
4. Getting Started with Functions
Exploring the Expression Builder interface
Using mathematical operators
Applying functions to text
Challenge: Convert US dollars to Canadian dollars
Solution: Convert US dollars to Canadian dollars
5. Applying Aggregate Calculations
Creating aggregate calculations
Understanding the Totals field
Understanding the WHERE clause
6. Working with Dates in Queries
Understanding how dates are stored
Specifying a range of dates or times
Using date and time functions
Formatting dates
Sorting formatted dates
Defining today's date
Calculating time intervals
Challenge: Collect information on all orders from 2014
Solution: Collect information on all orders from 2014
7. Understanding Program Flow Functions
Introducing IIf
Creating an IIf function
Using the Switch function
Challenge: Calculate sales prices for a product line
Solution: Calculate sales prices for a product line
8. Alternative Query Types
Finding duplicate records
Identifying unmatched records
Creating crosstab results
Creating backups
Creating update queries
Making, deleting, and appending records
9. Exploring SQL Syntax
Understanding SQL basics
Writing SQL queries
Writing a union query
Embedding SQL code in queries
10. Useful Query Tricks
Using domain functions
Returning values above or below average
Returning random records from the database
Challenge: Find out which products have the highest and lowest markup percentage
Solution: Find out which products have the highest and lowest markup percentage
Conclusion
Next steps

Download File Size:522.12 MB

---