Access Expert Level 12

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