Access 2007-2013
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  
 
NEW Release: Access Advanced Level 2    dismiss
 
 
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 1/20/2017 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

 
 
 

Student Interaction: Microsoft Access 221

Richard on 1/1/2007:  Aggregate (Summary) Queries. Sums, averages, counts, etc. in Queries, Report/Form Footers, Employee Timesheets
Abdul Nwiran on 9/9/2008: Please Can you Show Me How to do overtime calcolation on timesheet .
Thanks Richard

Richard Rost on 9/9/2008: Abdul, I spend a LOT of time covering calculating employee pay in Access 306.
Edward  Anderson on 9/18/2008: How to average a row in a query? For example:There are three column heading with Home work assignment grades one per column 1 thru 3 how can these grades be averaged
Richard Rost on 9/18/2008: Well, if you know there are always going to be 3 and exactly 3 fields, you can just make a new calculated query field:

MyAverage: (F1+F2+F3)/3

That will give you the average of the three. If the number of fields is variable, then we need to do a little more work.

Richard Rost on 10/4/2008: Yes, this course will cover calculating the correct number of hours even if your times span midnight. For example, if your employee starts work at 10pm and finishes at 6am the following day, the timesheet will still be correct.
Greg Paradise on 3/2/2009: I would like to see the overtime segment and may already have it since this was from 06'....but you said drop a comment. Its dropped;)
Greg Paradise on 3/2/2009: The other thing I would like to learn is how to fomat time in 100 units per hour. That seems to be the norm in AS400 and QAD software and I would like to be able to communicate back and fourth.

Again I have all the classes and this may be in one. Let me know.

Thanks,

Paradise

Richard Rost on 3/5/2009: I'll add it to my to-do list (which is now overflowing).
Richard Rost on 3/5/2009: Greg, I have not covered this. You mean you want to calculate time like 15.2 hours? That's just a matter of converting your "number of hours" value (field/calculation/whatever) into a double or decimal value.
Greg Paradise on 3/5/2009: Yes that is what I would like to do and I have tried different formats and I don't seem to be able to get them to accept a decimal...I just get a zero when I enter or tab past the field?? I'm sure Currency would give me decimals but I don't think that will help. I've gone into the properties and set decimal places....nothing.

Keep up the good work, I'm sending referrals your way.

Richard Rost on 3/6/2009: Greg, are you using DOUBLE, SINGLE, or DECIMAL for your data type? If you use any of the INTEGER types, they'll get rounded off.
Greg Paradise on 3/6/2009: That was my problem I put it to double and it is working now. Thank you!
Richard Rost on 3/9/2009: Greg, you're very welcome.
hassan ali on 8/30/2009: Please could you let me know which Access did you make the table for every time you talk to a customer table in the customer form?
Richard Rost on 8/30/2009: Hassan, I believe that's the Contacts Subform and we covered it in Access 202.
manuel zurbano on 3/28/2010: hi how could i make criteria toget number of people eating on breakfast lunch dinner thanks

Reply from Richard Rost:

Manuel, I'd need more information than this in order to help you. How is your data set up?

 Chris on 4/1/2010: Ref: Access 221.6 TI 16:57. Follow-up to my observations on StarDate issues.

It seems there is an issue with Between ... And if you do not us the US Date/Time Format. I live in Canada. Commonwealth Countries (Britian, Canada, etc) use the format dd/mm/yy. The US format is mm/dd/yy. Once I changed over to using the US format, I was good to go.

 Chris on 5/6/2010: Access 221.6 TI 16:57. I was using Access 2000 and have upgraded to Access 2007.

In this portion of the course material, we are setting up two input parameters for Field StartDate.

In the Criteria part of this field we are using "Between [StartDay] And [EndDay]". When I use this verbatim in my code, I get no results regardless of the time periods used. This is the same result for both Access 2000 and Access 2007. Digging into this a bit, I found:

The EndDay input criteria works as you show it; but I MUST enter "#Date#" in the StartDay input or the query does not work. This is differrent than what is shown in the video.

Although I didn't need to change the EndDay portion, to get around this Issue, I have changed the Criteria as follows:

"Between CDate([StartDay]) And CDate([EndDay])"

My example now behaves as shown.



Reply from Richard Rost:

That is very interesting. I'll have to try it. Thanks for sharing!

Kenneth Lange on 11/25/2010: Richard. I (for one person) would like to know Howto calcualte Overtime based on Daily Overtime, and Overtime over 40 Hours in a Week, I would also like to figure Tax Deductions based on the Gross amount Paid, to figure out what my bottom line Net Paycheck Results should be. I am also trying to calculate these events into the Future to Gain Financial Insight into the effects of the economy on my Net Income.

You mentioned maybe doing the Overtime calculations at the end of Access 221 part 9 on Timesheet Report.

 Chris on 12/6/2010: Richard,

In your examples in AC221, you use a more simplistic view of using and manipulating each day that an employee works in the reports. How would we use an aggregate to group the date entries into Weeks of work rather than days of work?

 Janet Persons on 1/9/2011: Actual I am trying to do a query for which I want the elligable date to look at hire date and a rehire date and choose which is later being the rehire date and calculate the elligable date. Is this possible?

Reply from Richard Rost:

Yes, this is certainly possible. You'll either need to use an aggregate query set to find the MAX date, or you can use the DMAX function.

 Janet Persons on 1/9/2011: Yu stated the trick to enlarge what your typing is to hit shift + f2 doesn't work in 2007 any suggestions?

Reply from Richard Rost:

It sure does work in Access 2007 and 2010. I just tried it. Click on the field (or TAB to it) then press and hold SHIFT followed by F2. This will open the ZOOM box and you will have more room to type.

 Tran Luong on 3/2/2011: I want to build the time sheet it requires people input the time by themself. Person A cannnot input the timecard for person B. Peron A can view all others 's timecards but not being able to edit them.

How we can control this ? I am thinking about the login account that helps the database keeps track with the login user?

Can many people login the database at the same time ?

What lesson you cover this feature ?

Thanks,

Reply from Richard Rost:

I cover individual user logins and controlling what users can do in my Access Security Seminar. http://www.599cd.com/site/courselist/seminars/access-security/

DARRELL WOLLAN on 3/18/2011: Richard, AC221 is great for keeping track of employee time "on", but what about employee time "off"? Is there a future lesson that deals with this?

Reply from Richard Rost:

Well, I suppose you could use the same exact technique for tracking time off, and then just merge the two sets of dates/times together for complete reporting. I have been wanting to put together a special seminar for Timekeeping (employees, students, etc.) with lots of options. I have an entry for it on the WAITING LIST. Vote for it if you'd like to see it.

DARRELL WOLLAN on 3/28/2011: I was very interested in the employee time tracking lessons and how to keep track of employee time "on". Do you plan on a lesson for employee time "off"? Employee vacation, sick leave, etc can get quite complicated.
Shams Momin on 3/31/2011: Richard I want this employee time sheet report when I click its give me a Box with combo employee button that i can select employee that I want and in report its give me two date option for curent Month From(1st) & To(30th) so User can use time period with he or she wants to look for

Reply from Richard Rost:

How is this different from what is shown in the course?

Sambit Kundu on 5/21/2011: Hi Richard, How to generate autonumber in an access query to able to use as ref number in letter?

Reply from Richard Rost:

There are a couple of ways to do this. You could just use AutoNumbers. If you want to artificially inflate them so you're not starting at 1, just insert 100 or 1000 blank records and delete them. Otherwise, see this tip on creating your own custom counter.

Keith Womble on 1/17/2012: I have a general database question. I'm using MS Access 2007. If I enter a dat in a date/time field in a table and the date is too long for the field width access 2003 truncates the cell contents. Access 2007 displays the # symbol. How do I get Access 2007 to truncate like 2003?
matt Pattison on 2/11/2012: Access 221 (worklog table) Access will not accept
StartDate: Format([TimeIn],[mm]/[dd]/[yy])can you tell me what im doing wrong

thank

Branko Jerovsek on 3/9/2013: Hy!
I have a problem with displying minutes. In videos you had examples only when start and end time were round (like 8:00-15:00 = 7 hours). What about minutes? When time is like START: 8:05 END: 15:16??
How to calculate this?
Thank you!

Reply from Richard Rost:

See this tutorial. It may clear things up for you more: Difference Between Two Times.

Jim Miller on 3/10/2013: Hi Rich, How can I have the Combo Box for the employee name show both first and last names? I thought that is what we picked wen we set it up. Why does it only show the first name?

Reply from Richard Rost:

You need to create a query and CONCATENATE the names together into a single field. Then use that query for your combo box.

Emad on 2/8/2016: Hello, How can I let the Label or Text Box control get the same name of the Report Object? Because If I Rename the Report object the Labels inside the Report does not Changed, so how can I linked to be synced together.

Reply from Alex Hedley:

I'm not sure it is possible, what is the reason for this?

Emad on 2/8/2016: Hello, Richard, Kindly, I want to see how to calculate the Overtime, that you were talking about on this course.

Reply from Alex Hedley:

Overtime shown in 306

 

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