Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 < 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 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

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

 Check out these other pages that may be of interest to you:
 2/2/2023 Import Objects 2/1/2023 Continuous Forms Not Working 1/31/2023 Group On Top 1/30/2023 Association 8 1/27/2023 Math in Fields 1/26/2023 Association 7 1/25/2023 Change Query 1/24/2023 Association 6 1/23/2023 Association 5 1/20/2023 Association 4

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

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Login My Account My Courses Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Mailing Address Phone Number Fax Number Course Survey Email Richard [email protected]
Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/3/2023 7:16:30 PM.