Presentation Transcript

1. Access Chapter 3

   Cody Cutright CS 101 Cody.Cutright@mail.wvu.edu
2. Calculated Fields Remember the Order of Operations: Parentheses () Exponentiation ^ Multiplication * Division / Addition + Subtraction -
3. Calculated Fields
4. Calculated Fields A calculated field is created using calculations based on fields from one or more tables. Calculated fields are created in the Design View of a query. The formula used to create the calculated field is referred to as an expression. Form: FieldName: expression
5. Calculated Fields The expressions *must* be written in a similar syntax. Evaluate the following expression: MonthlyInterest: [Balance] * .035 / 12 ***Calculated fields can NOT be updated in datasheet view Let’s make some of our own.
6. Calculated Fields A SecondsSince field that calculates how many seconds a Year field is from the Year 0. A field that calculates a ratio of the Average Number of People to a Home.
7. Expression Builder Contains all of the built in functions, operators, and common expressions you need!
8. Expression Builder The Expression Builder is a great tool to help with more complicated expressions. In a query field:
9. Expression Builder for Calculated Fields 1. Open the query in Design view. 2. Click in the top row of the first blank column. 3. Verify the Design tab is displayed. 4. Click Builder in the Query Setup group to launch the Expression Builder (or right-click in the blank column and select Build from the list). 5. Type the name of the calculated field followed by a colon (:). 6. If the expression requires a field from a table, select a table from the Expression Elements box, and then double-click field names as needed to add them to the expression. 7. Type an arithmetic operator (such as + or *), or click Operators in the Expression Elements box, and then double-click a symbol in the Expression Values box. 8. If the expression requires a function, double-click the Functions folder, click Built-In Functions to see the function categories displayed in the Expression Categories box, and then select the function category. From the Expression Values box, double-click the specific function needed, and then fill in the correct arguments. 9. Click OK to exit the Builder box and place the expression in the field cell. 10. Run the query. 11. Examine and verify the output. 12. Return to the Design view. 13. Modify the new expression if necessary. 14. Run the query. 15. Save the query.
10. Functions Access has several built-in functions, which produce a result based on inputs (aka arguments) These reduce the amount of work you need to put into a calculated field.
11. Functions For a real estate agency, suppose you wanted to create a calculated field to group houses based on the year they were listed. Right click within the field, and using the Expression Builder…
12. Functions
13. Functions
14. Functions http://www.techonthenet.com/access/functions/date/datepart.php
15. IIF Function The IIF function evaluates an expression and displays one value when the expression is true and another value when the expression is false. Example: IIF(Balance >= 10000, .035, .015) General: IIF(expression, truepart, falsepart)
16. IIF Function The function *must* evaluate as a true or false only. Example: balance >= 10000 or City = “Sarasota” are valid DateListed + 90 is not a valid expression as it returns a date
17. Upcoming Due Dates MyITLab Lesson C: Monday3/17 Homework IV: Friday 3/29 MyITLab Lesson D: Monday 3/31
18. Aggregate Functions An aggregate function performs calculations on an entire column of data and returns a single value. The functions are similar to the Total Row functions in Excel and include Sum, Average, and Min to name a few.
19. Why aggregate data? Aggregate information is important because it helps summarize an entire column of data. Most databases use some form of aggregate data.
20. Aggregate Functions What is a null value?
21. Aggregate Functions – Cont’d Table 3.4
22. Adding Aggregate Functions Method 1: Add a Total Row A Total Row displays as the last row in the Datasheet view of a table or query and provides a number of aggregate functions.
23. Adding a Total Row (Datasheet View) Home Tab -> Records Group-> Totals
24. Select the desired function In the intersection of the desired column and the Total Row, select the aggregate function!
25. Aggregate Functions Method 2: Create a Total Query A totals query contains an additional row in the design grid and is used to display only aggregate data when the query is run.
26. Creating a Total Query The first column of a totals query is typically a grouping field, (whichever you want to group by, Name, Item) The second and subsequent columns will usually contain the Count, Sum, or Avg function (Two to five columns is the general limit for a Total Query)
27. Totals Query Create Tab -> Query Design -> Show/Hide Group -> Totals Notice the Total Row appears in Design View:
28. Drag ’n Drop
29. What about aggregates in the expression? Example: The average of a field multiplied by another field. (Average of Price) * [Item] So how do we do this in expression builder?
30. SQL Aggregate The functions are available, but they are hidden under the Built-In Functions -> SQL Aggregate
31. Selecting Aggregates In this example, substitute the table field (or query field) for <<expression>>. Ex: [Teams.Wins] [Query1.Field1]
32. Advanced Query Demo