Access 2007-2019
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  
 
 
 
Courses - Microsoft Access 221
Description: Advanced Access Queries, Part 2
Running Time: 110 minutes
Pre-Requisites: Access 220 very strongly recommended
Previous Lesson: Access 220
Next Lesson: Access 222
Main Topics: Aggregate Query, SUM Function, Employee Timesheet, Hours Worked, Total Due
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 11/24/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 221
Advanced Access Queries 2

Aggregate Queries, Total Amount Due for Customers, SUM in Footer, Hours Worked, Employee Timesheet, More. 65 Minutes.
 

AC221 Major Topics

  • Aggregate Queries
  • Grouping in Excel
  • Total Amount Due Per Customer
  • Last Date Customer Contacted
  • SUM in Report Footer
  • Calculate Hours Worked
  • Employee Timesheet

This class continues our look at Advanced Query Techniques. This is part 2 of the series. We're going to learn all about Aggregate Queries, also known as Summary Queries or Totals Queries. Aggregate Queries allow you to group data together and get sums, averages, counts, and so on.

We'll begin by taking a look at exactly how we can group information together using Microsoft Excel, so we have a familiar ground to start on... for those of you who have worked with Excel before.

 

Next we'll see how we can create an Aggregate Query using Microsoft Access.

 

This query, for example, will allow us to group all of our customer records together that may have the same name (for example, if we have 2 or more different offices for 'XYZ Corp' in our database) and show all of their AmountDue values summed together. We'll also learn about averages, max, min, count, and other summary functions.

 

Next we'll create a similar function to show all of the sales in our database (using the OrderLog we created in a previous class) grouped by the customer's state. This way we can see all of our sales from NY, CA, etc.

 

I'll also show you how to generate this list for sales between two dates - so you can specify a begin date and and end date and see all of your sales, by state, for the month of February (for example).

We'll next learn how to take our list of customers and generate a query showing the date of the last time we contacted that customer. This is done by joining our customer table with our contact history table, and finding the most recent contact date.

 

Next we'll make a report showing all of our customers sales grouped by state, between two dates. This will teach you how to put SUM calculations in report footers. Very powerful stuff.

 

Our next example comes from the "by popular request" folder. Lots of people have asked me how to deal with tracking HOURLY work in Access. Whether you need to track employees, billable service hours, or any kind of hourly information, this lesson will help you.

We'll create a WorkLog table that will track an employee, time in, and time out. We'll make a query that will then automatically calculate the total number of hours in that "shift."

 

We'll make a nice data entry form so our secretary can just select the employee's name from a list and type in his hours.

 

Now that I have all of this time information, I want to be able to bring up an employee timesheet. So, I want a form where I can select the employee and specify a range of dates...

 

I want to be able to click on a button and see their timetable information...

 

Or print out a full timesheet and give it to them or the payroll company.

 

As a bonus, I'll even teach you how to store their hourly pay rate in the employee table and then calculate how much they get paid!

 

Now, whether or not you actually have to work with employee times, this lesson is invaluable in teaching you how to create sums, totals, averages, and other calculations in your forms, reports, and queries. We'll learn about a whole new powerful class of queries that - if you learn how to master them - can make your databases give you the kinds of information you expect from a professional quality database.

 

 

Access 221 Outline
 

1. Aggregate Queries
Summary Queries
Totals Queries
Group Different Records Together
Sums, Averages, Counts
Examples in Excel (for understanding)
Sorting in Excel
Data > Subtotals in Excel
Grouping in Excel

2. Build an Aggregate Query
List of Unique Customer Names Only
Essentially Eliminate Duplicates
Totals Button in Query Design
Show Total Amount Due by Customer
Max Value
Count of Records
Average (Avg) of Amounts

3. Sales Totals By State
Group by State
First Record
Unique Phone List of Customers
Sales Totals by State
Add a Date Criteria
Add Parameters

4. Most Recent Contact Date
When was the last time you contacted a customer?
Show all customers and contacts if they exist
Outer Join on CustomerID
Show Min of DateTime
Oldest Contact Date for Each Customer
Count of Contacts for Each Customer
Renaming Query Fields
Limit by State with Criteria
In() Function
In("TX","NY")

5. Sales Totals by State
Report Page Header & Footer
Sorting & Grouping Levels
Group Footer Visible
Report Section Footer
SUM Total in Group Footer
Visible Property - Hide Report Fields, Labels

6. Employee Time Sheets
Table to Track Time In, Time Out
Join WorkLogT to EmployeeT
Calculate Hours Worked
SUM Up Hours Worked per Employee
CDate Function - Convert To Date

7. Work Log Form
Employee Combo Box
Hours Worked SUM Total in Form Footer
Filter By Employee
Button On Main Menu

8. Time Sheet Form
Parameter Form to Select Employee and Date Range
Display Time Sheet Based on Parameters Entered
Forms!FormName!FieldName
Get EmployeeID From Time Sheet Form
Run Query from Command Button Wizard

9. Time Sheet Report
Employee Data in Group Header
Hourly Data in Detail Section
SUM Hours Worked in Employee Footer
Command Button to Preview Report
"Print Timesheet" Button
Enter Hourly Pay
Calculate Amount Earned

 


Try a FREE Demo Lesson

 
 

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
Change Email
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