Introduction

Welcome! Order Lists, Work Logs, Reports

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

Welcome to Microsoft Access Expert Level 12. In this course we will build an order list form to view and manage order summaries, create a work log entry form that calculates employee hours, and discuss setting default values to speed up data entry. We will focus on report grouping and sorting levels, starting with grouping customers by state, then creating weekly work reports, and building a sales report by order total intervals. We will also address fixing collection letters, look at grouping by numbers or currency, explore the report wizard, and review miscellaneous tips related to these tasks.

Navigation

Prev: X12
Next: Lesson 01

Keywords

Access Expert, order list form, order summary, open order form, work log entry form, calculate hours worked, report grouping, report sorting, group by week, sales report by interval, group by state, group by number, collection letters, employee time track

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 12 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to build an order list form so we can see a summary of all the orders in our system with the customer and the order total. You will be able to click on any one of those orders and quickly open up the order form.

Then, we are going to build a work log entry form so we can type in the employee, the time in, the time out, and the database will automatically calculate their hours worked. We will learn how to populate values based on defaults in the form button. You can specify a default employee so you can pick the employee once and then just type in time in, time out, time in, time out for each day, or you can do it the other way. You can enter in a date and then type in each employee. This will cut down on a lot of repetitive data entry.

Then, we are going to spend a lot of time in today's class learning about report grouping and sorting levels. We will start off simple, with a report showing customers grouped by state. Then, we will create a report showing hours worked by week, grouped by the week of the year and showing hourly totals for each week for each employee. Then, we will make a sales report where all of our orders are broken down into 500 dollar intervals, 0 to 500, 500 to 1000, and so on. In each of these lessons, there are many tips and tricks along the way.

This class was recorded with Access 2013. However, the lessons should work fine with 2007 and 2010. If you are using Access 2003 or earlier, sorting and grouping was handled much differently. Contact customer service and we will tell you which lessons you need.

This class follows Access Expert Level 11. I strongly recommend that you take Level 11 before watching this course. Level 11 covers aggregate queries, complex query criteria, sales totals by month, and the employee work log. A couple of these concepts we will be building on more in today's class, so it is very important that you have taken Level 11 before today's class.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You can understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all of the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and 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 have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at AccessLearningZone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes up in Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to build an order list form to show a list of all of our orders, sorted by order date, with the customer information, the order total, whether it is a quote or an invoice, and whether or not it is paid. We will make a button to open the selected order, another button to requery the list of orders, and of course, a 90-day sales total.

In lesson two, we are going to build a work log form to track our employees' activities and their time in, time out, and hours worked.

In lesson three, we are going to fix the collection letters that we broke when we changed the way the order entry system calculates the amount due. Initially, amount due was just a single field on my order table, and then we added the order details table to add more details to our orders. That unfortunately broke the collection letters, so in this lesson, we are going to fix them.

In lesson four, we are going to begin taking a look at report grouping levels where you can group records on a report based on one or more fields, such as show me all the customers grouped by state.

In lesson five, we are continuing our look at report grouping levels.

In lesson six, we are going to build a work log report grouped by employee, grouped again by the week of the year, displaying the week begin date, and then calculating the total hours worked.

We have seen so far how to group by text and by dates. Now we are going to see how to group by numbers or currency values.

In lesson eight, I have a few little miscellaneous items to cover. I am going to talk about group by expression. I am going to show you the simple report wizard, which also handles grouping levels, and I am going to give you your homework for the next class.

Quiz

Q1. What is the main purpose of building an order list form in this class?
A. To see a summary of all orders with customer and order total, and quickly open orders
B. To create invoices for customers automatically
C. To generate work log reports for employees
D. To set up user security settings

Q2. What feature does the work log entry form provide for entering employee hours?
A. It allows automatic calculation of hours worked from time in and time out
B. It retrieves employee hours from an external payroll system
C. It only allows entering daily totals, not time in and out
D. It exports the data directly to Excel

Q3. What convenience does the work log form offer regarding default values?
A. You can specify a default employee or a default date to fill repetitive entries
B. It automatically fills all previous data entries for reference
C. It allows only one entry per employee per day
D. It prevents changes to any default values set

Q4. What is the focus of the section about report grouping and sorting levels?
A. To demonstrate how to group and summarize data on reports
B. To show how to secure reports with passwords
C. To automate sending reports via email
D. To embed charts and images in reports only

Q5. Which of the following is NOT one of the grouping methods demonstrated in the class?
A. Grouping by pictures
B. Grouping by text fields like state
C. Grouping by week of the year
D. Grouping by numerical intervals

Q6. If you are using Access 2003 or earlier, what should you do regarding sorting and grouping?
A. Contact customer service for guidance on alternate lessons
B. Upgrade immediately to Access 2013
C. Skip all sorting and grouping topics
D. Use Excel instead of Access

Q7. What topic is covered in Access Expert Level 11 and is built upon in Level 12?
A. Aggregate queries and complex query criteria
B. Event programming and macros
C. Visual Basic for Applications
D. Database security and attachments

Q8. What is the recommended method for learning from these lessons, according to Richard Rost?
A. Watch the whole lesson, then follow along step by step on your own database
B. Immediately apply each concept to your real-world projects
C. Read the course transcript instead of watching videos
D. Only watch the summary at the end of the lesson

Q9. Where can students post questions about the course material?
A. Student forums on AccessLearningZone.com
B. Microsoft Access built-in help documentation
C. Facebook groups
D. YouTube comment section only

Q10. If you get stuck while following the lesson, what is the recommended solution?
A. Watch the video again and seek help in the forums if needed
B. Abandon the lesson and move to the next course
C. Contact Microsoft directly for technical support
D. Start the course over from Level 1

Q11. What is one of the purposes of downloading the sample database from AccessLearningZone.com/databases?
A. To compare and learn by examining a finished project
B. To replace your business data with it
C. To submit it as homework
D. To use it for external data integration

Q12. What will you fix in lesson three related to collection letters?
A. The collection letters that broke after changing the order entry calculations
B. The formatting of employee work log reports
C. The attachment feature for invoices
D. The login security for the orders database

Q13. In which lesson do you build a report grouped by employee and week?
A. Lesson six
B. Lesson two
C. Lesson one
D. Lesson eight

Q14. What miscellaneous topic is covered in lesson eight?
A. Group by expression and the simple report wizard
B. Exporting data to Word
C. Creating pivot tables in Access
D. Adding new users to the database

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A

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 Access Expert Level 12, and I am your instructor, Richard Rost.

In this lesson, we will create an order list form that provides a summary of all orders in the system, displaying the associated customer and the order total. The form will allow you to quickly open any order simply by clicking on it.

Next, I will show you how to build a work log entry form. This form lets you enter the employee name, time in, and time out, and Access will automatically calculate the hours worked. We will cover how to set default values so that, for example, you can select an employee once and then keep entering time entries for that person, or do the reverse - enter a date first and then select employees for that date. The goal is to speed up data entry and minimize repetitive work.

A major focus in today's class is report grouping and sorting. We will start with a basic report that groups customers by state. Then, I will show you how to make a weekly hours worked report, grouping data by week and by employee, and showing weekly totals. We will also build a sales report that groups orders into 500 dollar bands, such as 0-500, 500-1000, and so on. Each of these techniques will come with tips and tricks to help you make the most efficient reports.

These lessons were recorded in Access 2013, but you should have no trouble following along if you are using Access 2007 or 2010. If you are still on Access 2003 or earlier, sorting and grouping work differently there, so you may need a different set of instructions - just contact customer service and we will get you sorted out.

This course picks up where Access Expert Level 11 left off. It is important to complete Level 11 before starting Level 12, as we will be expanding on concepts like aggregate queries, advanced query criteria, monthly sales totals, and the employee work log. I highly recommend making sure you are comfortable with the material from Level 11 first.

My complete Access curriculum is divided into beginner, expert, advanced, and developer tracks. The beginner classes build foundational skills, and as you progress to the expert level, you will be working with more complex topics like those in this course. Advanced level classes shift focus to event programming and macros, and the developer classes introduce Visual Basic for Applications (VBA). Each track is broken down into multiple sequential levels.

Alongside my regular Access courses, I offer in-depth seminars on special topics. These include building web databases, designing form and report calendars, database security, working with images and attachments, managing work orders for service businesses, tracking accounts payable, learning SQL, creating loan amortization schedules, and many other specialized subjects. If you are interested in any of these, you can find detailed information on my website.

If you have any questions about the material covered in today's class, I encourage you to participate in the student forums. If you are watching these lessons on my website in the online theater, you will see the forum window next to each lesson. There you can view other students' questions and comments, and my responses. Please read through these as you work through the lessons, and feel free to contribute your own questions or insights.

If you are joining the course from outside the website, you can still join the discussion later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I suggest you watch each lesson all the way through before trying to work along in Access. After the first viewing, start the lesson over and follow along, step by step, creating the same sample database yourself. Do not try to apply these new skills to your other projects until you have built and mastered the database we work on in class.

If something is unclear or you get stuck, watch the lesson again or come to the student forum for help, and I will do my best to clarify it for you.

Above all, keep an open mind. Access might look intimidating, but with practice, you will find it is quite approachable.

While I encourage you to build your own version of the database as you watch, you can also download the finished sample database from my website if you would like to review how everything was put together. Sometimes the best way to learn is to look under the hood of someone else's work. In fact, that's how I learned a lot about Access years ago - by exploring the Northwind Traders sample database. You will find a downloadable sample database for each one of my classes on my website.

Now, let me outline what we will cover in today's lessons.

Lesson one will focus on the order list form, which will display all orders sorted by date, with customer details, order totals, the status of the order (quote or invoice), and payment status. You will be able to open a selected order, refresh the order list, and view sales totals for the last 90 days.

Lesson two will walk through setting up a work log form that tracks employee activity, including time in, time out, and hours worked.

Lesson three will fix an issue with the collection letters that occurred when we changed how the order entry system calculated the amount due. Previously, this was a single field in the order table, but after adding an order details table, the letters were no longer working as intended. We will address and correct that problem.

Lesson four takes a closer look at report grouping, beginning with grouping customer records by state.

Lesson five continues the discussion on how to use grouping levels effectively on reports.

Lesson six will show you how to build a work log report grouped both by employee and by week, showing the week start date and total hours for each employee per week.

We will move on to grouping by numbers or currency - for example, creating a report where order totals are grouped into bands like 0-500 or 500-1000 dollars.

Finally, lesson eight will cover a variety of smaller topics, including how to group by an expression, an introduction to the report wizard for quick groupings, and I will assign your homework for next class.

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 an order list form with customer and order total
Creating a button to open selected order from the list
Adding a button to requery the order list
Displaying 90-day sales totals
Building a work log entry form for employees
Automatically calculating hours worked from time in and out
Setting default employee or date for work log entry
Fixing collection letters after order total changes
Grouping reports by fields such as state
Creating reports for hours worked by week
Grouping work log reports by employee and week of year
Calculating weekly totals in grouped reports
Grouping sales reports into value intervals (e.g. 0-500, 500-1000)
Using report grouping and sorting levels
Grouping reports by number and currency values
Using group by expression in reports
Using the simple report wizard with groups

Article

Welcome to this expert-level Microsoft Access tutorial where I will guide you through building an order list form, a work log entry form, and advanced report grouping and sorting. By the end of this lesson, you will be able to easily manage and analyze your order and employee data while leveraging some of Access's more powerful features.

Let us start with constructing an order list form. The objective is to display every order in your system, showing the customer, the order total, and essential status fields such as whether it is a quote or invoice and if it has been paid. To do this, create a form bound to a query or a table that contains your orders. You will want to include the OrderID, OrderDate, CustomerName, OrderTotal, OrderType, and Paid fields, either as bound controls or textboxes. Sort the records by order date so that the most recent orders are easy to find.

A key feature is adding buttons that make your form interactive. One button will open up the detailed order form for any selected order, allowing for quick edits or review. This can be achieved with a button that uses the OpenForm macro or VBA code. For instance, in the button's OnClick event, you might use the following VBA:

DoCmd.OpenForm "OrderForm", , , "OrderID = " & Me.OrderID

This opens the OrderForm and filters it to the current order. Another useful button is to refresh or requery the list of orders, ensuring that any new or edited data appears without closing and reopening the form. For this, use:

Me.Requery

Include a control that calculates a 90-day sales total. This can be a textbox with a ControlSource property set to a DSum expression, such as:

=DSum("OrderTotal","Orders","OrderDate >= Date()-90")

This formula sums all orders within the last 90 days.

Next, let us build a work log entry form for your employees. The purpose here is to track when employees clock in and out, and have the database calculate their total hours worked. Your form should allow entry of EmployeeID, TimeIn, and TimeOut. Create a calculated field called HoursWorked, where the control source is:

=DateDiff("n",[TimeIn],[TimeOut])/60

This expression gives you the difference in hours. For data entry efficiency, you can set a default employee or default date. If you specialize in entering one employee's entire week, set the EmployeeID once and change only the times and dates. If you prefer to enter all employees' hours for a single day, set the date field's default value to Date() in its property sheet. This flexibility reduces repetitive data entry.

You can further automate the entry form by providing a command button that, when clicked, fills in default values or copies the previous record's details. For example, if you want to copy the most recently entered EmployeeID, you can use VBA like:

If Not IsNull(DMax("EmployeeID","WorkLog")) Then
Me.EmployeeID = DMax("EmployeeID","WorkLog")
End If

With these features, your form will streamline employee work hour entry and calculation.

As you become comfortable managing data on forms, reporting on this data is essential. Reports in Access allow you to group, sort, and summarize your data in meaningful ways. For example, you might create a report that lists customers grouped by state. In the report design view, use the Group & Sort options to add a group based on the State field. This groups customers together under each state heading, making it easy to see your regional distribution.

A more advanced example is grouping work logs by week, so you can see which employees worked how many hours each week. First, in your query or report record source, create a calculated field called WeekOfYear:

WeekOfYear: DatePart("ww",[Date])

Now, in your report, group by EmployeeID first and then by WeekOfYear underneath that. In the grouping footer, sum up the HoursWorked field to get the total hours per week. Use a text box with a control source like:

=Sum([HoursWorked])

You can display the start of each week by calculating the week begin date, for example:

=DateSerial(Year([Date]),1,1) + (DatePart("ww",[Date])-1)*7

With this, you can generate weekly summary reports for each employee.

Another useful reporting technique involves grouping numeric or currency values, such as order amounts, into ranges or buckets. Suppose you want to see all orders broken into $500 intervals like 0 to 500, 500 to 1000, and so on. Add a calculated field to your query or as an expression in your report:

OrderBucket: Int([OrderTotal]/500)*500

Group your report by the OrderBucket field. Use the Expression Builder to display the range on your report clearly, for example:

=[OrderBucket] & " to " & [OrderBucket]+499

This makes it clear which orders fall into each financial bracket.

Report grouping in Access is powerful and flexible. You can group by text fields (like state), dates (like week of year), or numbers (like order totals). Each grouping level enables you to summarize data, calculate subtotals, and present information cleanly.

In addition to these practical form and report techniques, do not forget to address any broken processes from earlier changes to your database. For example, if you previously calculated an amount due with a single field and have since changed your order entry system to use an order details table for line items, your old calculation may no longer work. Review your queries and forms to ensure you are now properly summing order details to get the correct amount due. Update your forms and reports to use expressions like DSum or aggregate queries to sum totals from the order details table.

Access also offers tools like the report wizard, which can quickly generate basic reports with grouping and sorting. While more advanced features may require manual setup, the wizard is a good way to get started or rapidly prototype a new report.

When working through these processes, it is best to build and test your forms and reports with sample databases. Create a database alongside this tutorial, replicating the forms, queries, and reports described above. Practicing in a sample environment ensures you understand each step before applying the concepts to your real database projects.

If you have questions, consult Access user forums for help and solutions. Remember, repetition is key. Build the sample database, experiment with features, and do not hesitate to start over if you get stuck. Access may seem complex at first, but with practice, its logic and structure will become second nature.

By the end of this tutorial, you should be able to build interactive order list forms, efficient work log entry systems, and comprehensive summary reports using grouping and sorting in Access. Apply these concepts to your own business scenarios to streamline your workflow and make better use of your data.