1 / 19

Day 17: Access Chapter 3

Day 17: Access Chapter 3. Tazin Afrin Tazin.Afrin@mail.wvu.edu March 6, 2013. objectives. Order of operation Calculated field Expression syntax Expression builder Built-in functions Date arithmetic. Order of operation.

onan
Télécharger la présentation

Day 17: Access Chapter 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related