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

< Previous: Access Expert 11

Next: Access Expert 13 >

Access Expert Level 12

Expert Microsoft Access Tutorial - 2 Hours, 33 Minutes
This Microsoft Access video tutorial picks up where Expert Level 11 left off. In this class we will learn how to use Sorting & Grouping Levels in Reports. Topics include:
  - Sorting & Grouping Levels
  - Build an Order List Form
  - Refresh vs. Requery
  - Employee Work Log Entry Form
  - Group Headers & Footers
  - Customers Grouped by State
  - Employee Hours by Week of Year
  - Sales Grouped in $500 Increments
Fix Collection Letter Reports

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 12
Description: Access Expert Level 12
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 11 strongly recommended
Running Time: 2 Hours, 33 Minutes
Cost: $24.99

This class picks up where Expert Level 11 left off. We will start by building an Order List Form showing all of our orders, the customer name, order date, the order total, whether it's a quote or invoice, and whether or not it's paid. We'll put a total of the last 90 days of sales in the form footer. We'll make a button to open the selected order directly. We'll make a button to requery the list of orders, and we'll learn the difference between the Refresh, Refresh All, and Requery commands.


In Lesson 2, we're going to build a Work Log Form where we can enter in each employee, their time in, and time out. The form will calculate hours worked. You can also select an activity to track what employees are doing with their time all day long. We'll add default value options to the form footer so you can add an employee once and then type in a bunch of dates/times, or you can enter a date once and then enter a bunch of employee records without having to select them each time.


Next we'll fix the Collection Letter Reports that we build in Access Expert 6. When we upgraded our Order Entry System, the changes essentially broke the collection letter report. We'll take care of that problem now.


Now we'll begin focusing on Report Grouping & Sorting Levels. This is where you can group data in your reports into sections. Each section can have a header and footer or you can display summary information, totals, and more.


We will begin by creating a report showing customers grouped by state. We will learn how to group data in our reports. We will learn about header and footer sections. We will create totals in our group footers. You will learn about circular reference errors. You will learn how to display information in a group header even if the data is null.

Next you will learn how to add a grand total to the bottom of the report. We will discuss what you cannot have aggregate functions and totals in your page footers. You will learn how to completely hide sections of the report. You will learn how to keep an entire group together on one page. You will learn how to force the new page after a section. We'll discuss multiple grouping levels and that further group our customers by country as well as state. We will display "United States" if the country field is blank.


In lesson six we will create a Work Log Report. This will show each employee, the total hours worked per week, grouped by the week of the calendar year. Unfortunately, Microsoft Access does not have a WEEKNUM function like Excel does but we will use the DATEPART function to achieve the same results. We will calculate the first day of the week so we can display "week beginning" and the Sunday the week starts.


So far the groups we created have been based on text or dates. Next we will learn how to group by numbers and currency values. We will show sales grouped by sales rep, grouped again into $500 increments. We will learn about integer division and how to format a number as currency with the format function.


Finally we will learn about the "group by expression" option, why I would rather use the query expression instead, and how to use the simple report wizard. I will also assign you some homework for the next class.

This is the 12th class in the Access Expert series. There's a lot of great material in this class. Learning report sorting and grouping will make your reports much more professional looking. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 12

00. Intro (8:01)

01. Order List Form (22:57)
Create Order List Aggregate Query
IIF to display PAID / UNPAID
IIF to display QUOTE / INVOICE
Sum of Order Total in Footer
Sort by Order Date Descending
Refresh Form Data Button Doesn't Work
Refresh vs Requery
Refresh vs Refresh All Buttons
Button Build Event
RunMenuCommand Refresh
Requery Macro Command

02. Work Log Entry Form (35:28)
Create a Button Without the Wizard
Copy Existing Button
Edit Embedded Macro Form Name
Copy Button for Query
Resize to Tallest
Create Work Log Form
Employee Combo Box
Tab Order
Bound Combo in Form Footer
Unbound Combo in Form Footer
Combo Default Value
Default Date Textbox
=[TodaysDate]+#9 AM#
Populate Defaults with Unbound Fields
Work Log for Current Employee
Add to Main Menu
Track Employee Activities
Activity Table
Add Activity Combo to Work Log

03. Fix Collection Letters (5:57)
Parameter Prompt from Missing Field
Update Order Collecton Letter Query
Aggregate Total for Amount Due

04. Report Grouping 1 (15:08)
Customers Grouped by State
Group & Sort
Add a Group
With a Header Section
With a Footer Section
With Totals
Group Footer Totals
Horizontal line
Line thickness
Showing a Value if Header IsNull
IIF IsNull
Display [None]
Circular Reference
05. Report Grouping 2 (17:56)
Add Credit Limit
SUM of Credit Limit
Grand Total
No Aggregate Functions in Page Footer
Page of Pages
Page Footer Below Report Footer
Section Visible Property
Hide Sections
Do not keep group together on one page
Keep whole group together on one page
Keep header and first record together
With title label - BUG
Force New Page After Section
Multiple Grouping Levels
No Country display United States

06. Employee Work Log Report (16:56)
Work Log Report
Group by Employee
Subgroup by Week of the Year
Access has no Week or WeekNum Function
DatePart Function
Calculate First Day of the Week
Week Starting Date
SUM of Hours Worked by Week
Total Hours for Employee

07. Number & Currency Grouping (17:55)
Show Sales Grouped by Rep
Subgroup by Month
Change to Numeric Grouping
Orders Grouped Every $500
Range of Values for Header
$0 to $499.99
Integer Division \

08. Miscellaneous (7:23)
Group by Expression
Use a Query Expression Instead
Simple Report Wizard

09. Review (5:21)


Keywords: Report Grouping & Sorting, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, grouping reports, order list, iif, refresh, requery, runmenucommand, work log, embedded macro, aggregate, group, sort, with a header section, with a footer section, with totals, isnull, circular reference, credit limit, visible, keep together on one page, force new page, subgroup, week, weeknum, datepart, first day of week, week starting date, numeric grouping, date grouping, integer division, format function, report wizard, group by expression


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: 6/24/2024 5:17:32 PM. PLT: 0s