Access Expert Level 11

This class picks up where Expert Level 10 left off. We will start by learning about Aggregate Queries (also called Totals, Summary, or Grouping Queries). We'll learn about the various popular aggregate query functions like Sum, Avg, Count, Max, Min, First, and Last. We'll create a simple query to calculate sales grouped by state.

Next, we'll learn about some different types of complex query criteria. For example, we'll make a query to show customer credit limits, grouped by state, but only for customers who were added to the database after 1999. We'll learn how to break a date value down into its components with the MONTH, DAY, and YEAR functions. You'll learn about the Where option.

Next we'll go through several examples of different aggregate queries. First, we'll create a query to calculate sales totals by month. We'll learn how to use the Format function to put our dates in the "YYYY MM" format. We'll also break down our sales by week of the year as well.

Next we will create a query to calculate the lowest cost of a product from the many vendors we can purchase it from. Since we have multiple vendors that we can buy each of our products from, we want to know who has the lowest price for all of our products. We'll use the DLOOKUP function (that we learned about in the last class) to help with this one.

It's sometimes nice to have a list of all of our customers along with the last time we talked to them, and what was said. We'll make a customer list showing the last contact date along with the text of what was in that contact. If there was no contact, we'll put "No Contacts" there, courtesy of the IIF function.

We'll make an employee work log (time sheet) where we can clock each employee in and out. You'll see how to calculate hours worked per day, and total hours worked per week or month (or any two dates). You'll learn about the CDATE function to convert a text string into a valid date value.

Finally, we'll learn some other odds and ends, like the Expression option of the aggregate query, and how to use the DSUM function to add up a range of values from a table. We'll use it to put the total sales for the last 30 days right on our Main Menu. We'll make a Refresh button to update the calculation whenever we want.

This is the eleventh class in the Access Expert series. There's a lot of great material in this class. Learning aggregate queries will make your databases much more powerful. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

Complete Outline - Access Expert Level 11