Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Microsoft Access Expert 11
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   8 years ago

Microsoft Access Expert Level 11 is 1 hour, 48 minutes long. In this tutorial we will learn how to use Aggregate Queries to summarize data. Topics include:

- Aggregate Queries
- Sum, Avg, Count, Max, Min
- Complex Query Criteria
- Sales Totals by Month
- Find Lowest Product Cost
- Last Customer Contact Report
- Employee Work Log, Timesheet
- Calculate Hours Worked
- DSUM Function

Click here for more information on Access Expert Level 11, 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 10.

Microsoft Access Expert 11 Upload Images   Link 
Neal Groeling 
13 months ago
In Lesson 3 at the 10:57 mark, is it possible to the format property for AvgSales set as currency to see it rounded? I realize if you were working with numbers that were not currency this would be confusing, but in this case...?
Richard Rost
13 months ago
Sure. Just add the Round function. AvgSales: Round(Quantity * UnitPrice). If that doesn't work, you might have to feed the results from this query into a second query and do the rounding there. I'd have to test it to be sure, and it's 3am and my brain is starting to shut down. LOL. Give it a shot and let me know.
Neal Groeling
13 months ago
Richard, thanks for the reply. I actually just went into the query and opened the properties for AvgSales, set the format to currency, and ran the query. Without adding ROUND, it turned it into a rounded currency in the results. On another note, I've noticed that in some queries when selecting a column, under properties and format, there are no choices and the dropdown does not work. Any ideas why? BTW, my brain quits at 10pm.
Add a Reply
Most Recent Date Upload Images   Link 
Andrew Washington 
5 years ago
Do you cover how to query the most recent date when the data is not able to be grouped?  For instance, I'd like to present the most recent date that someone took a test, but I'm having trouble because the test scores are all different so it returns the most recent date for every different score.  Tried breaking it into two queries, doesn't work.  Can't find it addressed adequately anywhere in this galaxy. Read More...
Add a Reply
Amount With Discounts Upload Images   Link 
Timo Knaepper 
5 years ago
The Control Source is =DSum"LineTotal","OrdersForStatsQ","OrderDate>=#" & Date()-30 & "#").The OrderForStatsQ uses OrderDate from OrderT, LineTotal: [Quantity]*[UnitPrice]and IsPaid from OrderT.

Reply from Alex Hedley:
Keith Stanton
4 months ago
I forgot about the discount until reading this thread.  I went back and changed the tables in the OrderForStatsQ from OrderT, OrderDetailT and ProductT  to  OrderT and OrderDetailQ.  Pulled the LineTotal from the OrderDetailQ which already has the discounts calculated in.  That also meant I was still able to use the same DSUM example as the field name of LineTotal was the same either way.
Add a Reply
Amount With Discounts Upload Images   Link 
Timo Knaepper 
5 years ago
Hi Rick. I am just curious about the "Orders in the last 30 days" field on the Main Menu. Does that amount account for discounts given? I have used "ExtPrice2" from "OrderDetailQ" to get the Total which accounts for discounts given. Read More...
Add a Reply
Hi Richard In your example of dealing with retur Upload Images   Link 
Brian Jensen 
6 years ago
Hi Richard,
In your example of dealing with returned errors by using iif(isnull, can this method also be used in the group by catagory to name the null grouping to something like "# of customers without contacts", this would only be when counting contacts by customer.  When I try it for my example  I get a error "The specified field [Shoplist] could refer to more than one table listed in the FROM clause of your SQL statement. Below is my SQL Read More...
Add a Reply
Homework Upload Images   Link 
Jeffrey Ervin 
6 years ago

Im Going back on lessons for employees and work logs, as with previous comment by CHARLES FULGHAM, the home assigned at 23mins was not fully resolved eg. Hours - reg pay, ot Pay, etc. was this covered somehere elsewhere? Read More...
Add a Reply
Homework Upload Images   Link 
Ferida Oe 
6 years ago
Where should I send the homework? Sorry I joined the course not from beginner or maybe you could refer me in which video that I can get the information about it.

Reply from Alex Hedley:
Add a Reply
Three Lowest Upload Images   Link 
Spiros P 
6 years ago
Dear Sir, the solution that suggests does not select the top minimun 3 offers from each product, but only the 3 min offers.

Reply from Alex Hedley:

Forgot the Group By (Σ) then choose Min.
Add a Reply
Three Lowest Upload Images   Link 
Spiros Poulis 
6 years ago
At section 4(02:23)  you make a query that returns for each product the offers from vendors. My question is if there is a way to limit the offers not to the min cost, but to make a query that returns for each product the 3 lowest offers. I have for example five or more offers but I want to make a query that returns only the first minimum 3 offers for each product. Read More...
Add a Reply
DSum Error Upload Images   Link 
Tom D 
6 years ago
I've tried =IIf(DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32") Is Null, 0, DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32")   Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

7/29/2021Event Enrollment
7/28/2021Membership Database
7/27/2021Quick Queries #5
7/27/2021Loop Thru Fields in Table
7/26/2021First Monday
7/25/2021Missing Months
7/24/2021Center Vertically
7/22/2021Buy Access
7/21/202164-Bit Access

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access expert 11 aggregate queries summary grouping dsum  Page Tag: whatsnew  PermaLink