Microsoft Access 221 Advanced Access Queries 2 Aggregate Queries, Total Amount Due for Customers, SUM in Footer, Hours Worked, Employee Timesheet, More. 65 Minutes.

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.

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