Jan's Working with Databases

Step-by-Step: Calculated Values - Totals

---

What you will learn:

to use the Total row to count records to open a second database while the first remains open to use the Total row to sum and to average to group a total's results on a calculated field

---

Start with: , Projects database (projects2-Lastname-Firstname.accdb) from previous lesson open, mytrips-Lastname-Firstname.accdb available (created in Project 2: Access Basics)

Calculate Total: Count

You will create a query to answer the question "How many staff members are assigned to each project?" You will need a Total query that uses the Count function to count employees. You will group the results on ProjectName or ProjectID.

1. If necessary, open the database projects2-Lastname-Firstname.accdb .

2. Start a new query using the Query Design button on the Create ribbon tab.
   The Query Design View appears with the Show Table dialog open.
   

3. Select the tables Projects and Project Staff and click Add.
   The two tables are added to the upper section of the Query Design View with a join line based on the relationship that you defined between them earlier.
   

4. Close the Show Table dialog.
   

5. From the table Projects drag the field ProjectName and drop it on the grid at the bottom.
   

6. From the table Project Staff drag the field EmployeeID and drop it on the grid.  

7. Click the Totals button in the toolbar to show the Totals row.
   

8. In the column for ProjectName  select Group By.
   In the columns for EmployeeID select Count.
   

9. Run the query.
   

10. If necessary, drag the right edge of each column until the column is wide enough to read the values.
    

    The datasheet shows how many employees are assigned to each project in the database. The projects you recently added have not had staff assigned yet.
    

11. Save the query as QCount Project Staff.
    

12. Close the query but do not close the database yet.

---

Open a Second Database

The Projects database does not yet have any number fields that are suitable for adding up or averaging. The MyTrips database that you created in Project 2 does. Recall that to open a second database without closing the first one requires you to first open a new instance of Access. Various methods were discussed in Project 2, Access Basics: Open Access.

1. Do not close the Projects database.
   Open a new Access window.
   Method 1: Start > All Programs or Programs > Microsoft Office > Microsoft Access
   Method 2: Right click the Access icon on the Taskbar. Because you left Access open, you have such an icon even if Access is not pinned to the Taskbar. Click on Access in the popup menu.
   A  new Access window appears.

2. Access 2007: Click on the Office button and then on Recent file.

   Access 2010: Click on Recent files

   Access 2013, 2016: Shows recently used files on the left when Access first opens.

3. Click on mytrips-Lastname-Firstname.accdb, if it is in the list.
   
   Problem: File is not in list of Recent files or you see an error message
   You have opened too many databases for this one to show.
   Or you recently removed the Class disk and reconnected it, resulting in a different drive letter being assigned to it.
   Solution: Click on the link Open and navigate to where you saved it in the databases project2 folder on your Class disk at the end of Project 2 of these lessons. Click the file and then the button Open to open it.

   Problem: List has more than one mytrips-Lastname-Firstname.accdb
   Cause: You have copies in more than one location and opened them all in Access recently. Or you removed and reconnected the drive and a different drive letter was assigned to it.
   Solution: Hover over each of the entries in turn. A screen tip shows the path to that file. Decide which copy is your current copy. You can easily find yourself with two very different versions, none of which are fully correct. So confusing!
   You may need to use the Open command and navigate to the most current location.

   Problem: Computer slows down with two copies of Access open
   If your computer responds very slowly with a second copy of Access open, you may not have enough memory to handle two copies at once.
   Solution: Close the Projects database to allow you to work in the MyTrips database.

---

Calculate Totals: Sum and Average

You will create a new Totals query in the MyTrips database and experiment a bit with it. You will make changes to answer the following questions:

• What is the total cost of all the trips?
• What is the average cost of a trip?
• What is the total cost of trips by each travel agent?

1. From the Create ribbon tab, click on the Query Design button.
2. Add the Trips table.  
3. Drag the Cost field down to the grid and drop it.
   
4. Click on the Totals button to show the Totals row.
    
5. In the Totals row for the Cost field, select Sum.
   
6. Run the query.
   The datasheet has only 1 cell, which shows the sum of all the values in the Cost field.
   
7. Return to Query Design View and change the Totals row to Avg, for average.
   
8. Run the query.
   Again there is only one cell showing, with the average of the Cost value for all trips.
   
9. Return to Query Design View and drag the field TravelAgent down to the grid.
   It automatically shows Group By in the Totals row.
   
10. Change the Total choice for the Cost field back to Sum.
    
11. Run the query.
    This time you see values for each travel agent and one for trips when no agent was used.
    The TravelAgent field shows the last name of the agent instead of the ID number because this field is a Lookup field and you had not learned how to make it show the whole name back in Project 2.
    
12. Save the query with the name QTotals-Trips by Agent.
13. Experiment with different choices for the Total row and with different fields. What happens when you:
    • Sum on a text field.
    • Group By more than one field.
    • Group By a different field.
    • Drag the * from the table to the grid and do a total.
    • How could you restrict the query to not show a sum when there was no travel agent?
      
14. Close the query without saving your changes.

---

Group Total Results on a Calculated Field

You can combine what you have learned about the Total feature and the IIf conditional expression to create a calculated field that will group your results in a new way. This is starting to get fun! (Or complicated, depending on your point of view!)

First you will create a Totals query that will Sum the values in the Cost field.

Then you will create a calculated field that will group the trips based on whether they were before the year 2000 or not.
Syntax for IIf expression:
IIf(<expression to evaluate>, <what to show if expression is true>, <what to show if expression is false>)

1. In the MyTrips database, create a new query based on the table Trips.

2. Drag the field Cost from the list of fields in the Trips table down to the grid.  

3. Show the Totals row, by clicking the Totals button .
   

4. For the Cost column, select Sum for the Total row.
   

5. To create a calculated field, type the following into the Field row of the second column:
   
   Before2000: IIf([DateStarted]<#1/1/2000#,"Before","2000 and later")

   Be sure to include all of the punctuation, the hash marks (#) around the date, and the double quote marks around the text strings.
   This calculation returns the value "Before" if the DateStarted is before the year 2000 and a "2000 and later" otherwise.
   The Total row shows Group By since that is the default choice.
   

6. Run the query.
   The Cost values are added together for two groups, start dates before 2000 and those in 2000 and later.
   

7. Save your query as QTotals-Trips-2000
   

8. Close the database MyTrips and then the blank Access window, if there is one.
   The Projects database is still open.