Access 2007-2016
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 Developer Level 3   dismiss
 
 

< 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
 
 

Student Interaction: Microsoft Access Expert 12

Richard on 8/31/2013:  Microsoft Access Expert Level 12 is 2 hours, 33 minutes long. This tutorial will focus on 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 Click here for more information on Access Expert Level 12, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 11. The next class in the series is Expert 13.
Don McGhie on 9/2/2013: When I try and download the student database, it says password protected. What is the password?

Reply from Richard Rost:

Read the instructions on the database download page. It explains how to get your password.

Simon Whiteduck on 9/20/2013: Access 2013 - would like to create better reports, forms/letters of employees cessation or start up sheets. Would like to create a main menu.
I do not work in stores yet. but I do work inside an office doing ...secretarial work, accounting work eg making purchase orders and scribing them to excel. I want to create forms and reports to hand into the band council meetings please let me know at the earliest time convenient. what steps or videos should I purchase.

Reply from Richard Rost:

Start from the beginning: Access Beginner Level 1 and then continue through the series... Level 2, 3, etc. I cover forms, reports, and everything you need as the lessons go on.

Janet Gangl on 10/2/2013: Using Access 2010 - How do you center the Form Name on a form? Mine are always left aligned but I've noticed they're centered on yours.

Reply from Richard Rost:

You're probably in the default TABBED view. You need to switch to Overlapping Windows.

Lone Vistoft on 10/5/2013: What do I do when Acess is set up to default currency DEK/EURO in Lesson 7, and I would like to have the report in dollars. When you in your example write =Format((([SumOfLineTotal]\500))*500;"Currency") .
How do I put dollar as one of standard currencies?
Kind regards
Lone

Lone Vistoft on 10/5/2013: On task 12. OrderR can not be opened until you have entered parameter value Forms!OrderF!OrderID. This happens not only when I am using my database but also if I download your database. What can the problem be?
Alex Hedley on 10/7/2013: It is also the default for 2013 now.
In overlapping windows it goes Control box then left aligned title for 2010 but centred for 2013.

Lisa Ashbaugh on 10/9/2013: I created the OrderListF form in Expert12-lesson 1 and my data only shows one record per page even though I used Continuous Forms in the Form Properties? What am I doing wrong or is there some other default setting I need to change?
Lisa Ashbaugh on 10/10/2013: Hi Richard. Yesterday I sent you a message about a problem I was having with continuous forms. I wanted to let you know that with the help of a colleague, my issue has been resolved. I had too much space below my data fields in the details section. All is well now.

Reply from Richard Rost:

Glad you figured it out. I haven't had time lately to go through and answer Forum posts.

John Borrelli on 10/19/2013: Thank you for breaking it to fix it. Much of my time is spent trying to figure out why a report that worked last week no longer works. This will certainly help me to find what I changed, reverse engineer and use simpler fixes.
Bruce Reynolds on 10/31/2013: I am curious, when was the term "Left Join" first coined? Who (what software or company) first came up with the term Left Join? Also, why is it called a Left Join?
Alex Hedley on 11/3/2013: Hi Bruce,

It's to do with what set of records are returned from the data sources.
Venn Diagrams are useful to show what is returned here.

LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2).

RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).

INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

FULL OUTER JOIN
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Alex

Mike W on 12/15/2013: Which class do you move into the inventory control? are you doing it any different in the 2013 version than in the previous classes?

Reply from Richard Rost:

Inventory is covered in the "recordsets" lessons from Access 320 to 329. I'll be covering it a little differently when I get to the DEVELOPER level lessons for 2013, but the basic concepts are the same.

John Miller on 12/30/2013: Access Expert12 video 1 at the 17:00 time mark where you begin to talk about the difference between Refresh and Requery to the data on the OrderListF form hitting the F5 key does the same as Requery.
Ravi Chandiran on 4/5/2014: Great, this grouping helped me a lot. Thanks.

Ravi Chandiran

John B on 5/7/2014: Lesson (6) Employee Work Log Report 9:30- I went back to this lesson and I noticed when I type in =Weekday([TimeIn])+1, I get the year 1900. I know you talked about this in earlier lessons, but I am not sure where you talked about it. Is there a reason why it is showing 1900 instead of 2014?
Joe on 5/12/2014: Enjoy your videos. We do field audit water use. Due to cost and measuring constraints what we really generate is a WAG. The only true knowns we have are from the irrigation controller and the irrigation equipment. I would like to create a report from this field data and the manufacturer equipment specs. to generate true water usage. So I am thinking that the basics is some inventory cross referencing from the field data and calculating in the form or query to generate a report. Are there specific lessons you could recommend?

Reply from Richard Rost:

I'm not familiar with your specific industry, but any data you get into Access (whether typed in or imported) can certainly be used to perform any calculations you need, and you can generate whatever reports you desire. I'd need to know more about exactly what you're trying to do before I can tell you specifically which courses to take, but if you don't have a lot of Access experience, I'd just recommend starting from Beginner Level 1 and working up.


Richard Wilson on 8/21/2014: At 4:22 you are talking about not liking the name of CompanyNulQ but were not going to change it because you might have used it somewhere else. Is there a quick way to find where you may have used something and then changed it? I have that problem and have the dickens of the time finding out where it was used.

Reply from Richard Rost:

Yeah, you could use the Object Dependencies feature to figure out whether or not you've used a query before. I guess I was just lazy in class. :)

Richard W on 8/26/2014: Believe it or not, I actually discovered that myself on the interim!!

Reply from Richard Rost:

Awesome! :)

vicki Hudson on 9/25/2014: 33:50 time You put in duplicate dates for Joe. He worked from 9a-4p on 8/26 and 9a-10p on 8/26. How do you prevent those duplicate entry dates?

Reply from Rick Rost:

Vicki, the short answer is: you really can't without some programming which is much more advanced than we're going to cover at this level.

You'd need an AfterUpdate event to perform a DLOOKUP calculation to see if that particular timeslot already has an entry. We'll talk about this in the Advanced lessons.

Brian Farley on 10/2/2014: I think I've Identified when the header section with/without bug happens.

After you have used grouping, the Grouping, Sort, and Total box remains until closed by you. When you start a new report, and the Grouping box is already open, you will get the bug and have to select without then with.

This in itself creates another bug by doubling the header. Easy fix, close the Grouping box and create another new report.

Found this while creating Sales by Rep report in Expert13, drove me nuts trying to figure where my group header was. Hopefully this will save some insanity.

Carry on :)

Reply from Alexander Hedley:

Thanks for the info, I'm sure this will help others out if they come across this.

Brian Farley on 10/2/2014: Opps, check that, was this Class, (Expert 12) lesson 7 with the sales report.
Michael on 2/17/2015: Hi Richard / Alex,
I capitulated and downloaded Microsoft Access 2013 from the Internet. (I had been following along using Access 2007.) I will admit, I like Access 2013 better, however,I've run into an issue that I'm unable to resolve on my own and wanted to ask for your help.

In part 2 of the printable invoice in Expert 9, in design view you are able to right-click on the print order button, navigate to "Build Event" and adjust the macro to refresh the record before opening the printable invoice to print the order. When I try this, Access takes me to a Visual Basic screen that is all code and doesn't look anything like the screen that you are able to get to in the videos. I believe it is a screen for working with macros. Can you help me with this?

Thanks,
M.

Reply from Alex Hedley:

Did you copy along and build the db yourself?

Try deleting the line before pressing the "...", it'll likely day [Event Procedure]
Then click it,
If that doesn't work delete the code in the click event that you're taken to and repeat the first step
You should then be brought to an option to choose Macro Builder when you click the ellipse.

Michael Czuchra on 2/20/2015: Thanks Alex - you were correct - Access placed "Event Procedure" in there. When I deleted that, I was able to click on the builder and select macro builder. Many thanks for your help!
Michael Czuchra on 2/20/2015: Alex, I just noticed that I didn't answer your question - yes, I followed along and built the database myself. From a learning perspective, my plan is to go through all the lessons as I have been doing and follow along and build the database. Then, I think I am going to go back and watch them all again and build it a second time to reinforce the fundamentals. I figure that I'll continue doing that until I get as fast as Richard...hahahaha. Take care, M.

Reply from Alex Hedley:

That's the best way :)
Practice practice practice.
It may take a few times but it's worth it.

West Bobby on 7/26/2015: Hello, I need to build a database for employee pay for truck drivers that are paid a percentage of so much per a mile, per a ton of weight that they haul ex: .13 cent a ton/mile.... 104 miles times .13 equals 13.52 a ton, the load has average 27 tons so 13.52 times 27 equals 365.04 now the driver gets 30% so 365.04 times 30% equals 109.51. Now the best part the miles and rate is ALWAYS changing, I currently do my figures in excel but I am looking for a way to create pie charts for the company's gain/loss, keeping up with income, and parts,insurance, and a lot of etc...... will access be able to help me if I learn it???

Reply from Alex Hedley:

Access can do most things.
You can translate your Formulas from Excel into Calculated Query Fields as shown in E8.

Joe Beniacar on 12/23/2015: Hi Richard,
At time=30:00, is it a bad idea to set a HelperT s Short Text field (here, Activity) to Indexed = Yes (No Duplicates)? I tried this on my own database but for some reason, Access seems to yell at me in corresponding Queries, related to typing in your own values...something about not being able to change the one side of the relationship.

Reply from Alex Hedley:

Indexes can slow down the db, it keeps another reference to speed up searching, as it's a text field that could contain a lot of information it's generally not the best idea.
Normally you index the fields you're likely to search on often like IDs or Date Fields.

Joe Beniacar on 1/23/2016: (Time 3:31) When you put 1 field's control (Ex: FirstName) in a different field's Header section (Ex: EmployeeID Header) on the Report, how do both of those conditions functioning together or individually to make sense in Access? - (Ex: Does the EmployeeID Header kind of collect the corresponding Query's relevant "grouped" records together in its memory, and then the FirstName field in that EmployeeID Header takes the FirstName value in each of those related "grouped" records, and displays them one after another? Like, why wouldn t you just make it a FirstName Header , and add the FirstName control in that section?) Thanks for clarifying this topic this topic is a little bit confusing the first time through the material...

Best,
Joe


Reply from Alex Hedley:

It'll take the first record value from the data set and display that

Joe Beniacar on 1/23/2016: Is Requery always better to use than Refresh Record ?

Reply from Alex Hedley:

Rich wrote a Tip about this.

Joe Beniacar on 1/23/2016: *(Time 6:45) (i)If you make a Query (based on the relevant Tables), which only has the fields you want to see that s fine for the Form you make from it But when end users go to the Form and add new records of data for just those fields wouldn t it transfer back to the corresponding Tables (through the Query) for only those fields (and not the rest of the fields) resulting in new Table records (in each of the underlying Tables) that are incomplete (from not having those other fields data entered anywhere)? (ii)Then, if you make a new Query or Form based on those Tables in the future, and want to display one of the other fields' values that you did not included in the first Query wouldn t all of those fields values show up as blanks for every record (when they could have had a value, if there was some way to specify the rest of the fields values in the first Query)? (iii)Could we instead somehow make a separate Table from our initial Query that only has those fields from the Query, and make that Table the Record Source for our first Form, so that the data gets transferred there(correctly), and doesn t fracture the original Tables with only partial-data records. Is there a way to do this (like, is that what a Make Table Query does to get around that issue), or is there another way you might know of around this problem?

Reply from Alex Hedley:

It's a design choice you can make.
If you want the Form to be a data entry Form then put the Fields you wish to have values into the Query and show them on the Form, sometimes you might not want to have all the Fields entered, or the Form might just be for display purposes so you don't need to show the IDs or other unnecessary values.

Joe Beniacar on 1/23/2016: (Time 1:47) Why don t you make the OrderT and OrderDetailQ have a Right Join? Isn t it more likely that an OrderDetail line item is for an Order we don t have anymore, than an Order that doesn t have any items?

Reply from Alex Hedley:

I'd never Delete Orders, just have a flag to mark it as old or unused.
If you are likely to delete orders then yes swap the join.

Joe Beniacar on 1/31/2016: Hi Alex, thank you for your response. Could you perhaps be a bit more specific, about this discrepancy in field header section vs. different field Text Box in that section? Thank you very much! You and Richard are really helping me to learn Access (slowly, but surely.)

Reply from Alex Hedley:

If your query has multiple fields and you have grouped them they will show a sub query for each group, this can have a sort/order,
Since you are grouping by a specific field and you place that in the header it will show that given value, since they are all the same, add the same field to the detail section and you will see. If you add a field that isn't in the group to the header it will show the first value in the sub query since it can only show 1 value, again see this in the header against the detail when you are displaying the data.

Robert Maddox on 2/29/2016: Has this topic been addressed in an Advanced Lesson? If so which one?

Reply from Alex Hedley:

Which topic?

Ronda Ficklin on 6/7/2016: As soon as the Order List, Browse Orders, Sales by Month and Lowest Product Price were added to the Main Menu as command buttons, the Orders in the Last 30 days stopped working. I am sure you address this later. Perhaps you can confirm that on this forum page.

Reply from Alex Hedley:

This vaguely rings a bell, I'm sure it is.
Can any other student confirm?

Ronda F on 6/13/2016: I never saw a resolution to this anomaly
, but spontaneously it began working again.

Reply from Alex Hedley:

Strange, glad it's working now :)

 

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