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