Day 17: Access Chapter 3

1. Day 17:Access Chapter 3 Tazin AfrinTazin.Afrin@mail.wvu.edu March 6, 2013

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

3. Order of operation • Determines the sequence by which operations are calculated in an expression. • Parentheses ( ) • Exponentiation ˆ • Multiplication * • Division / • Addition + • Subtraction – 2+3^2 = 11 (2+3)^2 =25

4. 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)

5. 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]

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

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

8. PMT • The Pmt function calculates the monthly loan payment given the interest rate (monthly), term of the loan (in months), and the original value of the loan (the principal). • Pmt(rate, num_periods, present value, future value, type) • Pmt(0.06/12, 4*12, -12500)

9. IIF • The IIffunction evaluates an expression and displays one value when the expression is true and another value when the expression is false. • IIf (expression, truepart, falsepart) • IIf (Balance >=10000, .035, .015)

10. Date arithmetic • The DatePartfunction enables you to isolate a specific part of a date, such as the year. • DatePart(“yyyy”, [Employees]![HireDate])

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

12. Create a calculated field • File -> Save Object as -> PricePerSqFeet • 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

13. 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’

14. New query with builder • Copy PricePerSqFt and save as ListPriceCalculations • Go to design view • Delete WrongPrice field • Select PricePerSqFt and click on builder • Change PricePerSqFt to PricePerBR • Change SqField to Beds • Run

15. Use Builder • Add a new calculated field • PricePerRoom: [ListPrice]/([Beds]+[Bath]+3) • Set as currency • Caption: Price Per Room • Run

16. Use functions • Copy PricePerSqFt query and save as MortgagePayments • Go to design view • Delete WrongPrice • Add a new calculated Field • Payment: PMT(0.06/12, 360, -[ListPrice]*0.9) • Set as Currency • Run

17. More calculated Fields • DaysOnMarket: [DateListed] - #12/1/2011# • MonthsOnMarket: ([DateListed] - #12/1/2011#)/30

18. Next class • Access Chapter 3 • Aggregate function • Access Chapter 4

19. Thank You Log Off