ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access Expert Level 32 Tutorial Reports, Median, Mode, Intro Macros
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Expert 31

Next: Access Advanced 1 >

Access Expert Level 32

Expert Microsoft Access Tutorial - 1 Hour, 46 Minutes
 
 
Access Expert 32 focuses on Reports, Statistics, and an Intro to Macros. Topics include:
 
access cd   - Report Grouping Headers
  - Display a List of All Reports in Database
  -
Generate a Custom List of Reports

  - Report List Form, Click to Open
  - Subqueries, Rank, Median, Mode
  - Insert Blank Mailing Labels at Top
  - Introduction to Macro Programming
  - GoToRecord, SetValue Commands
  - InputBox, RunMenuCommand
  -
Lots more

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 32
Description: Access Expert Level 32
Versions: Recorded with Access 2016. Most of the material should work with all versions of Access from 2007 on.
Pre-Requisites: Access Expert Level 31 strongly recommended, as you should watch all of my classes in order. However, if you're skipping around, you should have at least completed the Beginner series and the first 3 or 4 levels of the Expert series where I start to cover functions.
Running Time: 1 Hour, 46 Minutes
Cost: $26.99


This class covers a wide variety of topics that we didn't get to in any of the other Expert series classes. We will begin by working with report grouping headers. I will show you how to make a single letter header for your reports.

single letter report header

 

Next we will see how to generate a list of all of the reports in the database. We will use this list to create a custom form where the user can browse the list and then click a button to open that report. We will use a system-generated list first, and then I will show you a technique to create your own custom list so you can control what appears there.

report list msysobjects

 

Next we will spend some time with statistics. The RANK, MEDIAN, and MODE functions are easy to implement in Excel, but Access is surprisingly missing them. You need to learn how to work with something called SubQueries in order to generate these values. So, that's what we'll do in this lesson. We'll also learn how to calculate percentiles.

rank median mode

 

The upcoming Advanced series of Access lessons will focus on Macro Programming, so in today's class we're going to take a little preview so you can see what's to come. In this lesson, we're going to tackle one of the most commonly asked questions that I get: how to insert blank mailing labels on top of a label sheet. We'll learn a whole bunch of macro commands like GoToRecord, SetValue, InputBox, RunMenuCommand, and more.

macro programming mailing labels

 

This is the 32nd class in the Access Expert series. If you're serious about building quality databases with Access, and you have any interest in charts, don't miss out on this course. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 32

00. Intro (4:10)

01. Single Letter Report Header (18:05)
Contact Report
Creating Group for Single Letter
Group by First Character
Display LEFT 1 Character
Group by Contact Name
IsNull to Show No Company Name

02. Report List Form (17:19)
List of All Reports in Database
MSysObjects Table
List of Type Values
Custom Report List Table
Report List Form and Listbox
Macro to Open Report
Build Event
On Click Event

03. Rank, Median, Mode (25:04)
SubQueries, SubQuery
Query Customers Less Than Average Employees
Rank Formula
Alias for a Table
Median Value "Good Enough"
Mode Value
Totals Query, Top 1 Value
Percentile Ranking
04. Intro to Macros (36:18)
Customer Mailing Labels
Create LabelT Table
Create LabelR Report
Insert Blank Records at Top
Cut and Paste
Create LabelF Form
Create View Labels Button
Print Preview Report
Macro to Insert Blank Records
Command Button Macro
GoToRecord New Record
SetValue
Show All Actions
Unsafe Actions
Form Macros v External Macros
Button to Run External Macro
Prompt For Loop Count Value
InputBox Function
Insert Blanks on Top of Label Sheet
RunMenuCommand
SelectRecord, SelectAllRecords
Cut, Paste
Button on Customer Form to Add Label
OpenForm

05. Review (5:12)

 


 
Keywords: Reports, Median, Mode, Intro Macros, microsoft access tutorial, microsoft access tutorial, microsoft access training, report header group, report list, on click event, rank, subquery, percentile, gotorecord, runmenucommand
 
 

Student Interaction: Microsoft Access Expert 32

Richard on 6/11/2016:  Microsoft Access Expert 32Access Expert 32 covers a bunch of random topics that we didn't get to in any of our other Expert courses, including single letter report groupings, statistics like MEDIAN and MODE, plus an Intro to Macros. - Report Grouping Headers - Display a List of All Reports in Database - Generate a Custom List of Reports - Report List Form, Click to Open - Subqueries, Rank, Median, Mode - Insert Blank Mailing Labels at Top - Introduction to Macro Programming - GoToRecord, SetValue Commands - InputBox, RunMenuCommand - Lots more Click here for more information on Access Expert Level 32, including a course outline, sample videos, and more. This course was recorded using Access 2016, but most of the topics covered are valid for all versions of Access from 2007 on. This class follows Expert Level 31. The next class in the series is Access Advanced 1.
Uriel R on 6/15/2016: Will the next Expert video explain how to use formulas from Excel in Access? I think it was mention during the comprehensive function videos, but I don't recall if it was done or not.

Reply from Alex Hedley:

This is more likely to be covered in the Developer series as it'll be using VBA.

Robert S on 6/23/2016: When will the handbook be available?

Reply from Alex Hedley:

When I write it :p

Hopefully I'll have time in an upcoming weekend.
Do you have any feedback on the handbooks?

Robert S on 6/24/2016: I think the handbooks are great value it adds another means of studying the data provided on video
JAMIL KHAN on 7/3/2016: very good
Delores on 7/11/2016: As long as it is free to me, I would love to have it. Thank you
Dennis Owens on 7/12/2016: I am already receiving Access 301. I am up to Access 304. Do you have a possible release date for Access Advanced Level 1?
I want to receive Access Advanced Level 1 as soon as it is released.

Reply from Alex Hedley:

I think it's on the Waiting List, or Pre-Order, not sure when the release date is.

Ray W on 7/16/2016: Delores
What do you mean:
As long as it is free to me, I would love to have it. Do you work for free?
Richard puts a lot of time and money into making the videos. If you like the training videos then support him for his great work.

Reply from Alex Hedley:

They might be a MyOLP customer.

Hardatt Trivedi on 10/1/2016: I need to read all excess exert topic.

Reply from Alex Hedley:

I've written most of the handbooks for the courses, if you have bought the video course you can buy the handbook too.

 

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