By Richard Rost 14 years ago
This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.
Microsoft Access 221
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 5/5/06
Copyright 2006 by Amicron Computing
All Rights Reserved
Welcome to Microsoft Access 221: Advanced Queries 2.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 221. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.
We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.
Table of Contents
Table of Contents 2
Lesson 1. Aggregate Queries 4
Lesson 2. Building an Aggregate Query 8
Lesson 3: Sales Totals By State 18
Lesson 4: Contact List With Most Recent Contact Date 31
Lesson 5: Sales Report – Showing Totals by State 37
Lesson 6: Employee Time Sheets 43
Lesson 7: Work Log 50
Lesson 8: Time Sheet Form 59
Lesson 9: Time Sheet Report 65
Welcome to Microsoft Access 221: Advanced Queries 2, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Aggregate Queries
· Totals on Forms & Reports
· Work Log / Timesheet
The class follows Microsoft Access 220 Advanced Queries 2. We’ll be using the same database that we’ve been using since Microsoft Access 101. I strongly recommend that you watch all previous classes before you start with this class.
In this class we will be using Microsoft Access XP (2002) and Windows XP, however this course is valid for any version of Windows, you should have no problems following this course if you are using Access 97, 2000 or 2003.
If you have not been building your database yourself since Access 101, you can get a copy of my database file at the website www.AccessLearningZone.com/Access/221 . If you purchased this course on CD-ROM or downloaded it from our website, you’ll find that it’s been installed in your help folder.
Lesson 1. Aggregate Queries
In this lesson, we’re going to take a look at Aggregate Queries. What are aggregate queries? Well, they’re sometimes also called Summary Queries or even Totals Queries, and essentially what they should do is group different records together. You can show sums, or averages, or counts for records by groups. And this allows you to get more information out of your queries.
For example, let’s say you want to get a list of all your customers grouped by state. We can see totals for all of our Sales for Customers by each state, or we can get an average of all their credit limits and different things along those lines. This would be great for doing all kinds of sales forecasting, marketing survey results, employee working hours, all kinds of things!
Before I show you how to set up a Summary Query, let me show you an example of how this is useful using Excel. Here I am inside of Microsoft Excel. I’ve set up a real simple spreadsheet. I’ve got customer names, the states they’re in, and the sales to those customers. I would like to see totals for each state and how many sales made to each state. I want them to be grouped by each state.
The first thing to do is to sort the list. Go to Data and then Sort. Let’s sort by state.
Now the list is sorted by state.
Now I’ll use a feature called Subtotals. Click Data – Subtotals.
Let’s put a Sum function at each change in State. And we’ll add a subtotal to the Sales column.
And it’s that simple! You can see the subtotal creates grouping levels on the left and breaks it down by each state.
Now this is what we’re going to do with an Access aggregate query. And yes, we can create something that looks just like this using an Access report. The query’s only going to give us the totals. In fact, you can hide this with the little buttons on the left.
So now we know what an aggregate query does and that’s to group together all our records based on a certain field. You can base them on multiple fields too. In the next lesson, we’ll set up our first aggregate query in access.
Lesson 2. Building an Aggregate Query
In the last lesson, we saw how to get sorting and grouping levels in Microsoft Excel – which is basically what we’re going to be looking for in Microsoft Access. Here I am in my PC Resale database.
I want to show you a simple example of how an aggregate query works. Let’s go to Queries – New – Design View. And let’s bring in our Customer table.
Now let’s bring in our CompanyName field and run the query.
As you can see I’ve got a pretty unique list of company names here. Sometimes you might get more than one record with the same company name. So let’s go to our Customer table and create another record or two with the same company name so I can show you how this works.
Go back to Design View and open up our Customer table.
Let’s say some new customers come in.
Let’s close our Customer table and go back to that query we were working on. Let’s run the query again. Now you can see multiple instances of “XYZ Corp.” Now, what do I do when I want a unique list of companies? Well let’s go back to Design View.
Let’s sort the list ascending.
Okay – there we go. It’s a little more apparent when you sort them ascending.
Let’s go back to Design View. Here, we’re going to turn Grouping on. We already have Sorting on, but now we need Grouping.
Where’s Grouping? Right here on the toolbar – it’s this little “E” – a “sigma.”
Click that button and notice that the Total shows up, and it says “Group By.” In other words, Access is going to group all the results based on whatever field we happen to be in. In this case, “Company Name.”
If I run the query now, Access gives me a list of all my customers grouped by Company Name.
Next, let’s find a numerical value somewhere in this table. How about the AmountDue field? That’s how much each customer owes us, right? Let’s bring that in.
Notice it also says Group By. That’s okay for now. Let’s see what we get. Run the Query.
Okay, now look at that! It’s showing us two Amicron Computing records again, but they have different AmountDue values. Well here’s what happened. Remember they’re different records. The default value now happens to be zero. So it’s grouped by CompanyName and by AmountDue. The amount’s due are different. Take a look at XYZ Corp. The one that we had in the database first was $100, both of the second ones were grouped together – and they’re both $0.
That’s what this query says. First it says group by CompanyName, then group by AmountDue. It’s grouping by two different fields.
Since this AmountDue is a numerical value, we’re not limited to just the “Group By” function. Let’s see what else we’ve got. Let’s drop this box down.
Since this is the AmountDue, let’s add it up by selecting the Sum function. This is going to group by all the company names and add up all the amounts due for each company based with the same name. Let’s take a look at it.
Run the query and look at that – it’s a beautiful thing! I’ve got each company name all grouped together nicely and it added all the sums up.
Let’s go back to our customer table to change some of the AmountDue values for XYZ Corp. When we run the query with the new values, we get this:
Now we’re not limited to just Sum. We can also do things like the average, or the min, the max, or the count. Let’s do Max.
Run the query and you’ll see the Max values.
What does Count do? Count adds up the number of each company. But look at Amicron Computing. It only shows as 1 but we know there are 2 of them in the database. Why is that happening? That’s because one of them is blank and Access doesn’t count blank records.
If you want to get a list that shows all the companies and how many of them there are, use a field that doesn’t have any blank records in it. Go back to Design View and get rid of the AmountDue column. Bring in a value that you know has to be there, like CompanyName again and Group By the first one; Count by the second one.
Now run it. Notice it says, “CountOfCompanyName” across the top.
If you wanted to, you could bring in AmountDue twice. You could make one the Sum. Bring it in again and make it the Average. When you run the query, you can see them both.
Lesson 3: Sales Totals By State
Now we’re going to actually take a look at the example I showed you first with Excel. And that’s how to take a list of customers by state and add up all their sales. We’re going to do this in Access using the aggregate query. Here’s another example with this basic aggregate query we’ve built here. Let’s say we’re going to make a phone list.
Let’s bring in Phone.
And let’s run the query. Here, we get all our customers and phone numbers. If you’ve got two different phone numbers from the same company, you’re going to see each one twice because we’ve got Group By on. That’s where the first and last comes in handy.
Let’s say you want to group by company name, but you only want to see the first record when it comes to phone number.
Now I’ll run my query. It groups by the first one so we get a unique list of company names and then of the different phone numbers, I only want to see the first one.
Let’s save this query as CustomerUniguePhoneListQ.
Now let’s get a little more advanced. Open your “SalesWithCustomerInfoQ” query. What I’d like to do is generate a list of all of my companies by each state and show the sales total. The first thing I’m going to have to do is add the State field to this query.
Let’s put state in this query, but then we’ll make a new query to do our aggregates with. Go to Design View and find the State field.
Double-click it to drop it down below and then save the query.
Run it to make sure we’ve got our details in.
Looks good! You can close it now. Let’s make a new query and go to Design View, but let’s bring in our other query that we just built.
Hit Add and then close it. Now we want to see a list of all the States and sales information. So let’s add UnitPrice.
Let’s run the query to see what we get.
Looks good! Now let’s turn our totals on. Now we’ve got Group By and Group By.
Let’s see what happens when I run it.
Now let’s group this by Sum and run it. Now all of the states have been added together.
We can save this as our SalesByStateQ.
Now I want to show you something tricky: How to see this week’s sales by state. Return to Design View and double-click SaleDate to bring it in.
Let’s take a look at it and see what we get. Run the query. All the dates came in as 1/19. Why is that? Well, when we made our SalesLog, all of the records were entered on the same date. I’m going to change the dates.
I’m going to go back to my initial SalesLog and I’m going to give these sales different dates.
Let’s run the query again. Looks like we’ve got Group By on the SaleDates. That’s not good. I want to look at them between two dates.
Down in the Criteria field, I want to put a parameter query on this. I’m going to say, “Between #1/1/06# and #1/10/06#” as a valid range of dates.
Let’s run the query now and see what I get. Whoops! I’ve got nothing!! What’s going on here?
Well let’s change Total from Count to Group By and see what happens.
Okay, well that’s not summing them together.
Let’s try Max. Okay, Max is good. That’s one way we could do it.
And of course, you can just hide this column by un-checking it.
As long as you don’t Group By and select something else, it’s going to allow you to sum them together.
Now that we’ve got this working, we can change the dates. We can start with 1/1/06 to 1/10/06.
Let’s run it – and there we go! That’s one solution.
As another solution, let’s get rid of SaleDate and open SalesWithCustomerInfoQ in Design View. And you can fill in the parameter here. When you run this query, it will ask for your start and end dates.
Now it limits the records here in the first query. I’ll close it and save it.
Now when I run my second query, SalesbyState, it asks me for my start and end dates because it’s got to run the SalesWithCustomerInfoQ query first.
As you can see, you’ll get the same results. Which ever way you prefer is fine.
Lesson 4: Contact List With Most Recent Contact Date
In this lesson, we’re going to make a contact list showing the most recent contact date. In other words, we’re going to get a list of all our customers and the last time we spoke with them. Let’s take a look at our Customer Table. You’ll see as you scroll through the list that there are no dates in there.
To find dates, we need to open our Contact Table.
Let’s make an aggregate query that makes a list of all my customers and the last time that we talked to them. Click Queries - New – Design View. Bring in the Customer Table and the Contact Table.
Hit the Close button. I want to make sure that I get all of my customers – even those that don’t have contacts. So I’m going to change my join type by double-clicking it, and select Include ALL records from ‘CustomerT’ and only those records from ‘CustomerT’ where the joined fields are equal.
Next, I want to bring in the Company Name, Phone Number, and the last contact Date and Time.
If I run the query now, I get everything. Notice there are several entries for the same company. I don’t want to see them all, so let’s go back.
Let’s turn our Totals on.
We also want to Group By Company Name and Phone Number as well. Let’s group Date and Time by a Max value. (If you wanted a list of customers organized by their first contact date, you would select Min.)
When we run the query, it looks nice and pretty.
We can also add in here the number of times that we’ve contacted them. Bring in Contact ID and give it a Sum value.
Run the query, and there it is as CountofContact!
If you don’t want CountofContact or MaxOfDateTime as field headers, you can change these yourself.
For example, instead of DateTime, you can type in LastContact: DateTime.
When I run the query, it’s going to call that field LastContact.
If we change ContactID to NumContacts: ContactID, we get NumContacts on the query after we run it.
Similarly, what if I want to see New York customers in here only? We can simply bring in State and type “ny” into the criteria field.
When I run it, I get the following.
If I want to see New York and Texas customer only, I’ll type in “ny” OR “tx” into the criteria field.
Instead of typing in “ny” OR “tx” into the criteria field, you can use the Inclusive Function and type “IN(“NY”,”TX”).
Running both will give us the following result.
Let’s save this query as ContactListWithMostRecentDateQ.
Lesson 5: Sales Report – Showing Totals by State
In this lesson, we’re going to build a sales report that’s going to show us the totals for each state and the details (like customers from New York, California, and their total sales and so on). So let’s go to Reports and click New and then click Design View. Select “SalesWithCustomerInfoQ” as your table.
Since we want to group by state, bring in SaleDate, UnitPrice, CompanyName and State.
Let’s rearrange the fields like so.
We need to put the field labels into the Page Header, line up the fields underneath, and drag up the Page Footer.
After bolding the Page Header content, we get a quick report preview that looks like this.
Now we want to break this down by state so we need to use Sorting and Grouping and group by state with a Group Footer (we don’t need a Group Header).
I’m going to copy State and paste it in the Footer section (and bold it).
When I preview the report, you can see that it groups by state and it put a copy of the state in the footer section. Now I need to sum up the unit price.
How do I do that? Well, let’s copy the UnitPrice into the State Footer and make it bold.
We need to bring up the properties for the UnitPrice that we copied and give it a name, SumOfUnitPrice. We need to change the Control Source of the field to =Sum([UnitPRice]).
Let’s save the report as SalesByStateR and preview what we’ve got so far. Isn’t that beautiful?
If you don’t want to see the details, just bring up the properties for the details and set the Visible property equal to No.
When you run it, you get this result.
For our lesson, I’ll turn them back on. And I’ll throw in a horizontal line above the Sum of UnitPrice field and State field in the footer. Maybe we’ll copy it and put one in the header just to make it look good.
We’ll turn the Header back on through Sorting and Grouping.
Maybe this time, I’ll move the State field from the footer into the State Header and put the horizontal line above the header.
Save it, preview it, and look at that!
Lesson 6: Employee Time Sheets
In this lesson, we’re going to begin building a system to track employee time sheets. We’re going to build a work log table where we’ll track each employee (time in, time out & how many hours they worked). We’ll build an aggregate query to calculate the total hours worked per employee.
What we’re going to do now is make a system for entering in employee hours, and we’ll use an aggregate query for making up a list of employees and their hours. I’ll also show you how to make an employee report with calculations in the footers.
Let’s make a table where we can track each employee and their time in and time out. Create a new table in design view and call it “WorkLog.” The first field (key field) will be the WorkLogID and the first bit of data we need to track will be the EmployeeID. Add a TimeIn and a TimeOut as Date/Time fields.
Save this table as WorkLogT and click “Yes” to add the Primary Key.
Now we need to put in some data.
Now that we’ve got some data in there, how do we figure out the hours worked? Well, let’s make a little query. Close the WorkLogT table and save it’s changes. Create a new query in Design View. Bring in the WorkLogT and EmployeeT table.
Just in case I have some entries in here that don’t have an employee, I’m going to select the Include ALL records from WorkLogT table option.
Let’s now bring in the employee’s first name from the employee table and all the fields from the work log.
While we’re at it, lets save the query as WorkLogQ. Now let’s calculate the hours worked. To calculate the hours worked, enter in HoursWorked: Round(([TimeOut]-[TimeIn])*24,2.
When you run it, Access will show the hours worked.
Save this and close it so that we can now make an aggregate query where we can see each employee and the hours that they worked. Let’s make a new query in Design View and bring in the query that we just made: WorkLogQ. From there, we want to see the employee’s first name and the hours worked.
When we run it, we see the following.
Now let’s turn on totals and group by first name, but change the total in HoursWorked to Sum.
When we run it, we get the sum of hours worked for each employee. Keep in mind, this is the sum of the total hours worked for the entire table. But what if we want to break that down by dates?
Let’s make our start date the TimeIn value.
When we run it, we get the following. Notice that when we do that, we’ve got Jane’s shift showing up as two records. It would be nice if I could group this by date somehow, and only see the total hours worked once per record.
Let’s go back into Design View and save it as WorkLogTotalsByEmployeeQ. Let’s pull up the WorkLogQ query and enter in a new field: StartDate:Format([TimeIn],”mm/dd/yy”). In other words, chop off the time.
Let’s save it and run it to see what we get. Look at that! It chopped off the time portion. I formatted the value as just a date.
Now we can group by our start date. Close this query and reopen it again to show the new StartDate field we just created. Let’s change the TimeIn field to StartDate.
Now when we run it, Access groups the dates together. Now we can put a parameter on this to see a specific range of dates.
First, we’re going to have to put a criteria into a different query. Save the current query and go back to your WorkLogQ query. Enter Between[StartDate] And [EndDay] as the Criteria under your StartDate field.
In the StartDate field, you have to change the Format portion of the data otherwise Access will format that data as a text string and won’t know how to mathematically evaluate the dates. So we need to convert that data into a date. The formula to convert a text string into a date is CDATE().
When you save it and run it, you’ll be prompted to enter in dates, so type in 1/1 for the StartDay value and 1/3 for the EndDay value.
Now you have your numbers.
Now that we’ve got that working,we can close it and run the WorkLogTotalsByEmployeeQ query with a StartDay value of 1/1 and EndDay value of 1/4 to get the following. There are no hours worked for 1/1 but keep in mind that 1/3 ends at midnight. So anything that happens 1/3 after midnight doesn’t get included. That means if you want to see hours worked on 1/3, you’ve got to put in an EndDay value of ¼.
Lesson 7: Work Log
In this lesson, we’re going to make a form that we can use to enter data into our work log. The first thing I want to do is create a form for our end users to type in employee data. So let’s go back to our WorkLogQ query and make a copy of it with Control+C. Press Control+V to paste and let’s call this our WorkLogFormQ. We’ll design it real quick by getting rid of it’s StartDate calculation field (delete it), getting rid of it’s FirstName field, and then saving it.
Next, go to Forms – New – Design View. We’ll base this form on our WorkLogFormQ query.
Click OK and bring in all the fields.
This is going to be a continuous form so we’ll rearrange the fields and field labels.
I’m going to make these all flat with a black border.
I’ll drag up the border a little bit and change the background color and I’ll make our text white
Now let’s bring up the form’s properties to make it a continuous form by changing the value in the Default View field.
Let’s bring up our header and footer and place all the field labels into the Form Header. We can now save the form as WorkLogDataEntryF form.
Now we have to change the EmployeeID fields into a combo box so we’re going to delete it. Bring up the toolbox and drop a combo box there so we can pick an employee. Select Look up values in a table or query. Hit Next.
Select the EmployeeT table. Hit Next.
Add the EmployeeID field to bind it to the combo box we just made. And we can bring in the FirstName and LastName fields. Hit Next.
Let’s store this value in the EmployeeID field. Hit Next.
Hit Finish. And there’s our EmployeeID combo box. (You can delete the label that appears atop the WorkLogID field.)
Let’s save this, close it, then open it back up to see what we’ve got. Looks good!
We need to return to Design View and change the tab order of the EmployeeID field. Select Tab Order from the View menu. I’m going to just click on the Auto Order button.
Notice that the field we’re ordering is labled Combo5? Let’s bring up the properties and change that to Employee Combo.
Now let’s close that, save the form, close it again. Reopen the form. Now I should be able to add in data at the bottom of the form.
If you want to drill this down, you can use your filters. For example, I can see only Joe’s records by right-clicking his name and then selecting the Filter By Selection option.
It might not be a bad idea to throw in a sum at the bottom of the form. How do we do that? Return to Design View mode. Copy the HoursWorked field and paste it into the Footer.
Double-click on it. Change the Control Source to =Sum([HoursWorked]). If you want, you can change it’s name to SumHrsWrd.
Save it, close it, and then re-open it. Now you’ve got a nice total.
You can filter the TimeIn or TimeOut, the employee, and the filter will update itself. We can put a button for it on the main menu too. Just right-click it’s title bar, select Form Design
Bring up the tool box and drag and drop a new button on our form. Select Form Operations and Open Form on the Command Button Wizard. Hit Next.
Select the WorkLogDataEntryF form. Hit Next.
Select Show All Records. Hit Next.
Let’s give it a text value of Work Log. Hit Next. Hit Finish. And there we go! We can now save it and reopen the main menu to see what it looks like. You can click it and view your work log!
Lesson 8: Time Sheet Form
Okay, we’re building up to making a time sheet but before we do that, we’re going to build a form to enter in the criteria for that report. So let’s design the WorkLogQ query real quick. Let’s keep the StartDate field in the query, but let’s get rid of the parameter.
So when we run the query, this is all we want to see. So let’s close that and save it.
Now we need to make a new blank form to enter in our criteria. Click Form – New – Design View – OK. So what do I want on this blank form? I want an employee list, a start date and an end date. I can steal an employee list from our WorkLogDataEntryF form. I can just copy the employee combo box we made earlier and paste into our new blank form.
I’ll Bring up my toolbox and throw in a lable.
Now we need a “begin” date and an “end” date.
Now we need to setup some default values in here. Let’s open up the properties for the Begin Date field. We can name it BeginDate. The Control Source will be a week ago, so I’ll enter =Date()-7 (that’s one week ago from today’s date). Let’s format that as a General Date.
After I set its border to black, I’m going to copy and paste it onto the same form.
I’ll change its lable to End Date, change it’s property name to EndDate and get rid of the minus seven.
We should have a week showing up in here. Let’s take a peek at it and see what we get.
Save this form as TimeSheetF. Let’s run it and look at that! But we can’t pick an employee! Let’s go back to Design View.
Open up the properties for the Employee field. We need to unbind or delete the date in the Control source.
Now we should be able to pick an employee.
You’ll notice that the date values at this point can’t be edited either. Look down at the status bar and you can see why. Let’s take a look at the design again.
Open up the properties for the Begin Date entry. As long as there’s a value in the Control Source, the user can’t change it. Let’s get rid of that. We can put it in the Default Value field however. Default Value means ‘start off with this value but the user can change it.’
You’ll want to do the same for the End Date. Now when you save it and run it, you can change the dates if you want to. Now we just need a button to make this all happen. This button will open a report based on the WorkLogQ query.
So let’s copy the WorkLogQ query with Control+C and paste it with Control+V. We can name it WorkLogForTimesheetQ. Now we can use the WorkLogForTimesheetQ to get it’s value from the TimeSheetF form that we just made.
Let’s open up the WorkLogForTimesheetQ. In Design View, let’s bring in the LastName field and the EmployeeID field. We want to unhide the EmployeeID field so that we don’t get multiple fields on the form.
Next, we need to set the EmployeeID field equal to [Forms]![TimesheetF]![EmployeeCombo].
Let’s save it and run it. Now we can see all the date entered for Joe. This query is getting the EmployeeID parameter from the TimesheetF form we made and modified.
In Design View, we want to enter in a new criteria on the WorkLogForTimesheetQ query to show the dates that Joe worked.
When we run the form, we have the employee and the dates that he clocked in.
If we change the end date on the TimeSheetF form, the dates on the WorkLogForTimesheetQ query change as well.
Returning to Design View, we can add a button to the TimesheetF form. Bring up your toolbox and drag a button onto the form. What are we looking for? Select the Miscellaneous and Run Query options. Hit Next.
Select the query that you want to run (WorkLogForTimesheetQ). Hit Next.
Select the text option and type in Show Timetable. Hit Next and then hit Finish. When you run the form, you get the following.
And when you click the button that you just made, you can see the WorkLogForTimesheetQ query for any date.
Lesson 9: Time Sheet Report
Alright! Everything has been working up to this point. Now we’re going to make a time sheet report, which is a printable timesheet.
Based on the WorkLogForTimesheetQ query, we’re going to make a report. Manipulating the End Date so that we have a lot of data to work with, let’s make a new report. (Go to Reports – New – Design View.) Let’s bring over all the fields but let’s group all the employee information together. Turn Sorting and Grouping on and base it on EmployeeID. We want a Group Header and a Group Footer.
Now let’s put all the employee information into the Group Header. And just so that we know it’s important, I’ll bold it.
Next, I’m going to put the StartDate (Work Date), TimeIn, TimeOut and HoursWorked labels in the Header. And I’m gong to drag the Footer up too. Then I’ll copy HoursWorked into the EmployeeID Footer and change its properties name to TotalHrsWorked.
The Control Source will be equal to SUM(HoursWorked).
We’ll save the report as TimeSheetR.
And when I print it (preview it), we get the following. That looks pretty good!
Let’s make a couple of minor changes. First thing I’d like to add is a horizontal line above the Detail. We can bold the sum of hours worked. We can change the Name to Employee, and we can drag the LastName field right next to the FirstName field. We can raise the EmployeeID field and change its label field to simply ID.
Next, I’m going to select both of the TimeIn and TimeOut fields and bring up their properties. (Right-click and select properties.) This is a Multiple Selection properties box and it displays the properties for both of those boxes. I just want to see the time so for their format, I’m going to enter hh:nn am/pm. Save it, close it, preview it to see what we get. Beautiful!
Let’s save the changes to that report and come back to our Timesheet form. We’re going to drop another button on here to open up a timesheet.
In the Command Button Wizard, select the Report Operations and Preview Report options. Hit Next.
Select the TimesheetR report. Hit Next.
Enter in Printable Timesheet as the button’s text. Hit Next and then Finish.
We can bold it, change the background, save it, close it.
Now we need to put a button on the main menu for it. So let’s edit the main menu in Design View. Drag a button on its form, and select the Form Operations and Open Form options. Click Next.
Select the TimesheetF form.
Name the button Print Timesheets and click the Finish button.
When we re-open the main menu and click the Print Timesheets button, we’re prompted to select an employee and enter in a couple of dates.
When we click the Printable Timesheet button, we get a nicely formatted report.
And that’s basically how you do it! Now we know how to do that with an aggregate query. But let’s add a way to figure out how much to pay an employee. In the employee table, we need to add a field to track Jane’s hourly wage.
Let’s say that Jane makes 20$ an hour. In our WorkLogForTimesheetQ query, we’ll bring in the HourlyPay field from the employee table.
If I run this, HourlyPay comes in but we need to multiply the HourlyPay by the HoursWorked. So let’s add another field to the LogForTimesheetQ called AmountEarned. That value would be HourlyPay * HoursWorked. Pretty straight-forward. When I run the query, it calculates correctly.
Now we can quickly and easily throw this on the timesheet report. Copy and paste the HoursWorked field onto the TimesheetReport form. On its properties, change the Name and Control Source from HoursWorked to AmountEarned.
Give the new field an Amount lable and then copy and paste the Sum box in the Footer section to a position right next to it.
In its properties, change its Control Source to =Sum([AmountEarned]). To format the fields as currency, select both the AmountEarned and Sum of AmountEarned fields, right-click, and enter in currency as its format. Save it, close it, preview it, and there we go!
Let’s take a moment now to review what we covered in class.
· We learned about aggregate queries, how to do sums, averages, counts, and other weird things.
· We did a Sales by State example – a Contact list example.
· We worked on a Sales Report.
· We built our Work Log where we tracked hourly employees and how much money to pay them on our timesheet.
Tell us what you think. Log on to www.AccessLearningZone.com/Survey and take a short survey about this course.
RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!
Take your skills check quiz at www.AccessLearningZone.com/Test. If you pass, you can print out a Certificate of Completion.
What’s next? Visit www.AccessLearningZone.com for our complete list of Microsoft Access courses.
Need Help? Visit www.AccessLearningZone.com/TechHelp for Microsoft assistance.
Make sure you’re on our Mailing List. Go to www.AccessLearningZone.com/Mailing for details.
What’s New? Visit www.AccessLearningZone.com/WhatsNew for a list of what’s been added..
Contact Us. If you have any questions, go to www.AccessLearningZone.com/Contact for information on how you can contact us by phone, email, or live online chat.
Don’t forget to visit our Microsoft Access Forum online at: www.AccessLearningZone.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your Access questions, get answers, and tell us what you thought of our class.
This course, handbook, videos, and other materials are copyright 2002-06 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.
This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: