Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
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.

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

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



The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/12/2024 11:31:40 PM. PLT: 0s