Introduction

Welcome! Report Header, Lists, Rank, Median, Macros

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 32. In this course we will address several user-requested topics that were not previously covered in the expert series. We will walk through how to create a report with single letter report headers, build a report list form using both automated and manual methods, and discuss how to calculate rank, median, and mode using subqueries in Access. We will also introduce Macros, which will be covered in depth in the advanced series, and explore working with mailing labels that have partially used sheets. Information on course structure and additional seminars is also provided.

Navigation

Prev: X32
Next: Lesson 01

Keywords

Access Expert, report header by letter, report list form, generate report list automatically, manual report list table, subquery for rank median mode, macros introduction, mailing labels insert blanks, Access seminars, sample database download

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 32, brought to you by AccessLearningZone.com.

I am your instructor, Richard Rost.

Today's class covers a variety of topics that we have not gotten to in the expert series, but that people have emailed me asking me about. Plus, we are going to spend some time in lesson four, going over an introduction to Macros, which is what we will spend lots of time on in the Advanced Series, so it is kind of a preview of what is to come.

This class was recorded using Microsoft Access 2016. However, everything should work just fine whether you are using 2007 or 2013, or even 2010.

In lesson one, we are going to build a report with a single letter report header. You have seen this before, like in encyclopedias or in different types of reference material, where there is a big A, and then a group of all the A stuff, and then a B, and then a group of all the B stuff. That is something that is kind of tricky to do in Access, but we are going to learn how to do it in lesson one.

In lesson two, we are going to build a report list form. Lots of people always ask me how can I make a single form if I have a list of all the different reports in the database. All you usually have to do is pick one of the reports and then click Open, and it just opens that report. I am going to show you how to do it two ways.

One, we are going to use a little bit of programming to make Access generate that list of reports automatically for us. The second way, which is the way I prefer to do it, is to make a separate table with a manual list of all the reports. We can put a nice description of each report in there as well.

Lesson three is for all you statistics people who keep emailing me asking how to do rank, median, and mode in Access. Those are three functions that are pretty easy to use in Excel, but they do not exist in Access. You have to learn how to create something called a subquery in order to do this in Access. So in lesson three, I will show you how to do that.

Lesson four is going to be our introduction to Macros. We will be spending all of our time in the Advanced Series covering event and macro programming, and lesson four is going to be our introduction to that so you can see what is coming.

We are going to basically create a sheet of mailing labels. Lots of people always ask me how can I take a sheet of mailing labels where I have already got two or three labels used, and I want to have Access insert some blank records on top so I can reuse that label sheet. I am going to show you how to do that in lesson four.

For those of you who are new to my courses, they are broken up into four different series: beginner, expert, advanced, and developer. Each series is made up of multiple levels. Expert, for example, has, as of right now, 31 levels. This is expert level 31. Beginner had nine different levels. My courses are designed to be followed one after the other. So you should take level one, then level two, then level three, all the beginner classes, then the expert classes, and soon we will have the advanced and developer series.

In addition to my normal Access classes, I also have lots of different seminars designed to teach specific topics in my seminars, including web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you need help, there are forums on my website where you can post your questions. You can find that at accesslearningzone.com/forums. We do not guarantee support, but we do our best to try and help you with questions if you post them in the forums.

Now we will be using a sample database in today's class. accesslearningzone.com/databases is where you can download the sample database that I will be using later on in today's class. You will need your password for this class that you can get on your account on the website.

Now sit back and relax. Watch the video as many times as you want and follow along with me. Build the database yourself if you can. Rewatch the video if you have to, and of course keep an open mind.

Anyone can do this. Access is not hard.

Thanks for watching.

Quiz

Q1. What is covered in lesson one of this class?
A. Building a report with a single letter report header
B. Creating a calendar report
C. Securing a database
D. Building user login forms

Q2. Which method is NOT mentioned for generating a list of reports in a report list form?
A. Using a separate table with a manual list of reports
B. Creating charts and graphs
C. Using programming to generate the list automatically
D. Adding descriptions to a reports table

Q3. In lesson three, which statistical functions does Richard explain how to calculate in Access?
A. Average, sum, and count
B. Rank, median, and mode
C. Standard deviation, variance, and range
D. Frequency, mean, and minimum

Q4. What feature is introduced in lesson four as a preview for the Advanced Series?
A. Query design
B. Table relationships
C. Macros
D. Navigation forms

Q5. What is the challenge addressed with mailing labels in lesson four?
A. Printing labels with barcodes
B. Designing colorful labels
C. Inserting blank records so partially-used sheets can be reused
D. Grouping labels by city

Q6. What should students do if they need help with course material?
A. Email Richard directly for personal support
B. Post questions in the forums at accesslearningzone.com/forums
C. Call Microsoft technical support
D. Submit a support ticket through Access

Q7. Which version of Microsoft Access was used to record this class?
A. Access 2007
B. Access 2010
C. Access 2016
D. Access 2013

Q8. What is the recommended order for taking Richard's Access courses?
A. Developer, Advanced, Expert, Beginner
B. Beginner, Expert, Advanced, Developer
C. Expert, Beginner, Advanced, Developer
D. Advanced, Beginner, Expert, Developer

Q9. Which is NOT mentioned as a seminar topic available on accesslearningzone.com?
A. Web-based databases
B. Writing work orders
C. Creating inventory barcodes
D. SQL programming language

Q10. Where can students download the sample database for this class?
A. accesslearningzone.com/support
B. accesslearningzone.com/downloads
C. accesslearningzone.com/databases
D. accesslearningzone.com/samples

Answers: 1-A; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-B; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Expert Level 32, and I'll be guiding you through a variety of topics that I often get asked about but that we haven't had a chance to cover yet in the expert series. One of the main parts of today's class will be an introduction to Macros, which we'll be exploring in much greater detail when we move into the Advanced Series. So you can think of this as a preview of what lies ahead.

This class was recorded using Microsoft Access 2016, but you should have no trouble following along regardless of whether you're using Access 2007, 2010, or 2013. The techniques and concepts covered should work across all of these versions.

We'll get started in lesson one by learning how to create a report with a single letter report header. This is similar to what you might see in encyclopedias or other reference materials where, for example, all the items starting with A are grouped under a big letter A, then the Bs, and so on. While Access does not make this process entirely straightforward, I will show you how to pull this off.

Lesson two is focused on building a report list form. A lot of people have asked me how to set up a single form that displays all the reports available in the database, so you can just pick one from the list and open it directly. I'll walk you through two approaches for this. First, we'll use a bit of programming so Access can automatically generate the list of reports. Then I'll show you the method I prefer: creating a separate table that contains a manual list of all the reports, along with descriptions for each one. This makes it easier to manage and provide detailed information about your reports.

In lesson three, I'm addressing questions sent in by those of you who work with statistics and want to know how to calculate rank, median, and mode in Access. These statistical functions exist in Excel and are pretty straightforward, but Access does not have built-in functions for them. To accomplish this, you'll need to learn how to use subqueries. I'll show you how to set these up to get the results you want.

Lesson four will be our first look at Macros. We'll be spending a lot of time working with event and macro programming in the Advanced Series, so consider this lesson your introduction. For this example, I'll show you how to handle an often-requested scenario: you have a sheet of mailing labels with some labels already used, and you want to make Access insert blank records at the top, so you can reuse the label sheet efficiently. I'll walk you through the process step by step.

If this is your first time with my courses, let me explain how they're structured. They're divided into four series: beginner, expert, advanced, and developer, each one made up of multiple levels. Expert currently contains 31 levels before this one, and beginner has nine. The courses are intended to be followed sequentially, so I recommend starting at the beginning and working your way through each level in order.

I also offer specialized seminars that cover specific topics in depth, such as web-based databases, creating calendars, securing your database, handling images and attachments, generating work orders, managing service businesses, tracking accounts payable, learning SQL, creating loan amortization schedules, and many more. There's a full list of these seminars on my website at accesslearningzone.com.

If you ever need assistance, there are forums on my website where you can post your questions. While support is not guaranteed, we do try to help as much as possible if you reach out through those forums. You can find them at accesslearningzone.com/forums.

Throughout this class, we'll be using a sample database that you can download from accesslearningzone.com/databases. A password is required, which you can obtain from your account on the website.

I encourage you to watch the video as many times as you need, try building the database yourself, and don't hesitate to review sections if necessary. The key is to follow along and feel comfortable experimenting with the material.

Remember, Access is not as hard as it might seem. With a bit of guidance and practice, anyone can learn it.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Building a report with single letter report headers
Creating a report list form
Automatically generating a report list with VBA
Building a manual report list table
Adding descriptions for reports in a report list
Calculating rank in Access using subqueries
Calculating median in Access using subqueries
Calculating mode in Access using subqueries
Introduction to Macros in Access
Creating mailing labels with blank records at the top

Article

Welcome to this Microsoft Access tutorial, where we will cover some advanced report techniques, building a report list form, calculating rank, median, and mode in Access, and an introduction to macros with a practical example involving mailing labels. These are topics frequently requested by Access users, and while they may not be covered in basic tutorials, they are incredibly useful for managing and presenting data in Access databases.

Let us begin with building a report that groups items by a single letter, such as grouping names by their initial letter. This is common in reference materials or encyclopedias, where you might see a big letter A followed by all the entries starting with A, then B, and so on. In Access, this effect is achieved by adding a grouping level to your report that uses the first letter from a relevant field. For example, suppose you have a table of customers with a LastName field. You start by creating a new report in Design View, add the LastName field to the detail section, and then use the Group & Sort feature to group by the expression Left([LastName],1). This groups your records by the first letter of the LastName field. Then, you can enlarge the group header and use a larger font size or a decorative character to display the letter. This creates the distinct sections for each initial letter, making the report much more readable and attractive.

The next topic is creating a report list form, which is a central menu where users can see all available reports and simply click to open one. There are a couple of ways to do this. One method is to programmatically generate a list of all reports in your database using a bit of VBA code. For example, you can use the following code on your form's load event:

Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentProject.AllReports
For Each obj In dbs
' Add obj.Name to your list control (such as a listbox)
Next obj

This gathers the names of all reports and fills them into a list control. When a user selects a report and clicks Open, you can have a button use DoCmd.OpenReport to display the chosen report. The alternate, and often preferable, method is to create your own table listing all the report names along with meaningful descriptions. This gives you more control, lets you add categories or additional information, and makes the menu more user-friendly. Then, you simply bind your form's list control to this table and use the same DoCmd.OpenReport code on your button to open the selected report.

For those interested in statistics, calculating rank, median, and mode in Access does require a bit more creativity, since Access does not have these built-in functions as Excel does. To calculate rank, you can create a query using a subquery that counts how many records are greater than the current value. For example, if you have a table called Scores with a Score field, you can write the following query to get the rank of each record:

SELECT A.StudentID, A.Score,
(SELECT Count(*) FROM Scores B WHERE B.Score > A.Score) + 1 AS ScoreRank
FROM Scores A;

This subquery counts how many records have a higher Score and assigns a rank accordingly. Calculating median involves finding the middle value of a sorted dataset. You might use the DCount function to count how many records, then retrieve the value at the midpoint using another subquery or by creating a query that sorts the scores and uses criteria to find the one with the record number closest to half the total count. Calculating mode, the most common value, can be done by creating a totals query that groups by the field in question and counts occurrences, then sorting descending on the count and taking the top value.

Now let us introduce macros in Access with a real-world example involving mailing labels. Suppose you want to print a new sheet of mailing labels, but you have already used two or three labels from the top. To ensure the next printout starts on the correct label, you want Access to insert blank records at the top. You can automate this using a macro or some simple VBA code. For instance, you can create a query or a temporary table with a certain number of blank records and append these to your label data source. You could prompt the user for how many blanks they need, and then run an append query or loop in VBA to insert the required number of blank records before the actual data. This makes your label printing much more flexible without wasting sheets.

As you work through these examples, remember that Access is a powerful tool that can handle many complex tasks once you know a few key techniques. Building grouped reports, custom report menus, statistical calculations using subqueries, and automating routine tasks with macros or VBA are all within reach even if you are just advancing beyond the basics. Practice by following the steps above and try adapting them to your own data and business needs.

If you want to go even further, there are many more advanced topics you can explore, such as creating web-based databases, calendar-style forms, database security, image handling, and custom accounting or scheduling solutions, all achievable with a solid foundation in Access.

Keep asking questions, experimenting with new database designs, and do not be afraid to use VBA or macros to automate your work. With patience and practice, you will find that Access can save you a lot of time and effort, no matter what kind of data you are working with.