Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< Previous: Access Expert 10

Next: Access Expert 12 >

Access Expert Level 11

Expert Microsoft Access Tutorial - 1 Hour, 48 Minutes
This Microsoft Access video tutorial picks up where Expert Level 10 left off. In this class we will learn how to use Aggregate Queries to summarize data. Topics include:
  - Aggregate Queries
  - Sum, Avg, Count, Max, Min
  - Complex Query Criteria
  - Sales Totals by Month
  - Find Lowest Product Cost
  - Last Customer Contact Report
  - Employee Work Log, Timesheet
  - Calculate Hours Worked
  - DSUM Function

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 11
Description: Access Expert Level 11
Versions: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 10 strongly recommended
Running Time: 1 Hour, 48 Minutes
Cost: $24.99

This class picks up where Expert Level 10 left off. We will start by learning about Aggregate Queries (also called Totals, Summary, or Grouping Queries). We'll learn about the various popular aggregate query functions like Sum, Avg, Count, Max, Min, First, and Last. We'll create a simple query to calculate sales grouped by state.


Next, we'll learn about some different types of complex query criteria. For example, we'll make a query to show customer credit limits, grouped by state, but only for customers who were added to the database after 1999. We'll learn how to break a date value down into its components with the MONTH, DAY, and YEAR functions. You'll learn about the Where option.


Next we'll go through several examples of different aggregate queries. First, we'll create a query to calculate sales totals by month. We'll learn how to use the Format function to put our dates in the "YYYY MM" format. We'll also break down our sales by week of the year as well.


Next we will create a query to calculate the lowest cost of a product from the many vendors we can purchase it from. Since we have multiple vendors that we can buy each of our products from, we want to know who has the lowest price for all of our products. We'll use the DLOOKUP function (that we learned about in the last class) to help with this one.


It's sometimes nice to have a list of all of our customers along with the last time we talked to them, and what was said. We'll make a customer list showing the last contact date along with the text of what was in that contact. If there was no contact, we'll put "No Contacts" there, courtesy of the IIF function.


We'll make an employee work log (time sheet) where we can clock each employee in and out. You'll see how to calculate hours worked per day, and total hours worked per week or month (or any two dates). You'll learn about the CDATE function to convert a text string into a valid date value.


Finally, we'll learn some other odds and ends, like the Expression option of the aggregate query, and how to use the DSUM function to add up a range of values from a table. We'll use it to put the total sales for the last 30 days right on our Main Menu. We'll make a Refresh button to update the calculation whenever we want.


This is the eleventh class in the Access Expert series. There's a lot of great material in this class. Learning aggregate queries will make your databases much more powerful. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 11

00. Intro (8:38)

01. Simple Aggregate Queries (13:13)
Aggregate Query
Totals Query
Summary Query
Grouping Query
What they are
Popular Aggregate Functions
Statistical Analysis
Covered in future class
SUM in Form Footer Total
SUM in Table Footer (last row)
Totals Button
Simple SUM of all credit limits
Group By State
Default sort on Group By field
Sort by Credit Limit descending
Add criteria Is Not Null
Notice Field Name is Changed
One query with SUM, AVG, MAX, MIN
ALIAS field names
Count of customers from each state
Only NON-NULL values are counted
Count ID field to include NULLs
Simple Query Wizard
Aggregate Options in Wizard
Summary Information

02. Complex Query Criteria (7:44)
Credit Limit by State After 1999
Brief overview of date functions
MONTH, DAY, YEAR Functions
Break into Two Queries
WHERE Field in Total Row

03. Sales Totals by Month (10:50)
Format to show "yyyy mm"
Format Property in Query Field
Format() Function
Week of Year ww
04. Lowest Product Cost (12:46)
MIN Cost for each Product
DLOOKUP Vendor ID at Lowest Cost
DLOOKUP Vendor Name, Unit Price
Calculate Most Profitable Products

05. Last Customer Contact (6:36)
MAX of Contact Date
DLOOKUP Contact Notes
Dealing with NULL Values
IIF Function Review

06. Employee Work Log (26:02)
Create Work Log Table
Time In, Time Out
Difference Between Two Times
Calculate Hours Worked
Caution with Dates and BETWEEN
BETWEEN #1/1# and #1/2#
Does NOT Include #1/2 5pm#
>=Start AND <End
CDATE() Function Brief Mention
Work Log Lookup Form
Employee Combo Box
Command Button Run Query
Homework for Next Class

07. Miscellaneous (16:13)
Expression Option
DSUM Sales Last 30 Days
Refresh Form Data Button
Can't Refresh Unbound Form

08. Review (5:52)


Keywords: Aggregate Queries, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, aggregate query, totals query, summary query, grouping query, sum, avg, count, max, min, first, last, month, day, year, hour, minute, second, where, expression, format function, dlookup, dsum, iif, sales totals by month, lowest product cost, last customer contact, employee work log, timesheet, time sheet


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/19/2024 5:26:11 AM. PLT: 0s