Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

< 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: Recorded with Access 2013. Also use with 2007 and 2010. Everything in this class should work for all versions of Access.
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
SUM, AVG, COUNT, FIRST, LAST, MAX, MIN
Statistical Analysis
STDEV, STDEVP, VAR, VARP
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
SumOfCreditLimit
AvgOfCreditLimit
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
HOUR, MINUTE, SECOND Function
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
Timeclock
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
Forms!WorkLogLookupF!StartDate
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
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP