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  

News      User Comments     History     Notify Me

8/31/2013 1:21:58 PM
Microsoft Access Expert 12
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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.

Permanent Link
Course Link: Microsoft Access Expert 12
Keywords: access expert 12 report sorting grouping
Page Tag: whatsnew
Post Reply

Orders in the Last 30 days Comment from Ronda Ficklin @ 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?
Show Just This Thread        Post Reply
Topic addressed Comment from Robert Maddox @ 2/29/2016
Has this topic been addressed in an Advanced Lesson? If so which one?

Reply from Alex Hedley:

Which topic?
Show Just This Thread        Post Reply
Time 3 31 Header Comment from Joe Beniacar @ 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.
Show Just This Thread        Post Reply
Time 1 47 Comment from Joe Beniacar @ 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.
Show Just This Thread        Post Reply
Time 6 45 Filling in all Fields Comment from Joe Beniacar @ 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.
Show Just This Thread        Post Reply
Requery vs Refresh Comment from Joe Beniacar @ 1/23/2016
Is  Requery  always better to use than  Refresh Record ?

Reply from Alex Hedley:

Rich wrote a Tip about this.
Show Just This Thread        Post Reply
Time 3 31 Header Comment from Joe Beniacar @ 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...


Reply from Alex Hedley:

It'll take the first record value from the data set and display that
Show Just This Thread        Post Reply
Indexed Comment from Joe Beniacar @ 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.
Show Just This Thread        Post Reply
Salary Percentage with Graphs Comment from West Bobby @ 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.
Show Just This Thread        Post Reply
Following along Comment from Michael Czuchra @ 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.
Show Just This Thread        Post Reply
VBA not Macro Comment from Michael Czuchra @ 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!
Show Just This Thread        Post Reply
VBA not Macro Comment from Michael @ 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?


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.
Show Just This Thread        Post Reply
Comment from Brian Farley @ 10/2/2014
Opps, check that, was this Class,  (Expert 12) lesson 7 with the sales report.
Show Just This Thread        Post Reply
Header Section Bug Comment from Brian Farley @ 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.
Show Just This Thread        Post Reply
Timesheet overlapping duplicate values Comment from vicki Hudson @ 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.
Show Just This Thread        Post Reply
Have I Used An Object Comment from Richard W @ 8/26/2014
Believe it or not, I actually discovered that myself on the interim!!

Reply from Richard Rost:

Awesome! :)
Show Just This Thread        Post Reply
Have I Used An Object Comment from Richard Wilson @ 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. :)
Show Just This Thread        Post Reply
field audit water use Comment from Joe @ 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.

Show Just This Thread        Post Reply
weekday function Comment from John B @ 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?
Show Just This Thread        Post Reply
Comment from Ravi Chandiran @ 4/5/2014
Great, this grouping helped me a lot. Thanks.

Ravi Chandiran
Show Just This Thread        Post Reply
Refresh Requery Comment from John Miller @ 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.
Show Just This Thread        Post Reply
Inventory Comment from Mike W @ 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.
Show Just This Thread        Post Reply
Left Join Comment from Alex Hedley @ 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.

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

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

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

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.

Show Just This Thread        Post Reply
Comment from Bruce Reynolds @ 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?
Show Just This Thread        Post Reply
breaking it to fix it Comment from John Borrelli @ 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.
Show Just This Thread        Post Reply
All is well now Comment from Lisa Ashbaugh @ 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.
Show Just This Thread        Post Reply
Comment from Lisa Ashbaugh @ 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?
Show Just This Thread        Post Reply
Form Name Centered Comment from Alex Hedley @ 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.
Show Just This Thread        Post Reply
Comment from Lone Vistoft @ 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?
Show Just This Thread        Post Reply
Comment from Lone Vistoft @ 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
Show Just This Thread        Post Reply
Form Name Centered Comment from Janet Gangl @ 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.
Show Just This Thread        Post Reply
create better reports Comment from Simon Whiteduck @ 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.
Show Just This Thread        Post Reply
Database passwords Comment from Don McGhie @ 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.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 8+8:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
7/31/2018Microsoft Access Developer 8

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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