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 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: 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 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
OrderListQ
AllowEdits
AllowDeletions
AllowAdditions
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
DefaultEmployeeCombo
Default Date Textbox
TodaysDate
=[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 \
Format(X,"Currency")

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

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
 

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