Day 18: Access Chapter 3

1. Day 18:Access Chapter 3 Tazin AfrinTazin.Afrin@mail.wvu.edu March 18, 2014

2. objectives • Chapter 3 • Order of operation • Calculated field • Expression syntax • Expression builder • Built-in functions • Date arithmetic • Aggregate function

3. Calculated field • Create a calculation based on the fields from one or more tables. • Use expressions – • Identifiers (the names of fields, controls, or properties) • Arithmetic operators (e.g., * , / , + , or –) • Functions (built-in functions like Date() or IIf()) • Constants (numbers such as 30 or .5)

4. Expression syntax • Entered in the first row of the query design grid. • Must follow the correct syntax. • Create expressions to perform calculations using field names, constants, and functions. • Assign descriptive field names to the calculated fields. • MonthlyInterest: [Balance] * .035 / 12 • NewBalance: [Balance] + [MonthlyInterest]

5. Expression builder • Expression builder provides graphical tools for looking up operators (functions and arithmetic operators) and operands (existing fields, constants). • Open query in design view -> design tab-> Query setup group -> builder • Right click on field -> build

6. Built-in function • PMT • IIF • Date, DatePart, DateDiff

7. Create new query • Create a query that shows Agent’s FirstName, LastName, Properties’ DateListed, ListPrice, SqFeet, and Sold fields. • Show only properties Not sold. • Sort ListPrice in ascending order. • Run • Save as TargetHouses

8. Create a calculated field • Go to design view • Click on top row of first blank column • Type PricePerSqFt: ListPrice/SqFeet • Press enter • Set as Currency in Property sheet • Caption – Price Per Sq Ft • Run

9. Wrong Calculated field • Add another calculated field • WrongPrice: xListPrice/xSqFeet (shortcut : Shift+F2) • Run • xListPrice– 100000 • xSqFeet – 1000 • Fix the calculated fields by removing ‘x’

10. Functions with builder • Add a new calculated Field • Open expression builder • Query toold design -> Builder in query setup group • Payment: • PMT(0.06/12, 360, -[ListPrice]*0.9) • Set as Currency • Run

11. Aggregate Functions • An aggregate function performs calculations on an entire column of data and returns a single value. • Sum, Avg, Min etc. • Almost every database need to use aggregate function.

12. Adding Aggregate Functions • Two methods – • Add a Total Row in a Query or Table • Create a Totals Query

13. Add a Total Row • Displays as the last row in the Datasheet view of a table or query. • Provides a number of aggregate functions. • Go to datasheet view ->Home -> Records group -> Totals

14. Create a Totals Query • Contains an additional row in the design grid. • Used to display only aggregate data when the query is run. • Create a query -> Query Tools -> Show/Hide group -> Totals

15. Thank You Log Off