day 17: access chapter 3 tazin afrin [email protected] [email protected] october 17,...
TRANSCRIPT
![Page 2: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/2.jpg)
2
OBJECTIVES
• Order of operation• Calculated field• Expression syntax• Expression builder• Built-in finctions• Date arithmetic
![Page 3: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/3.jpg)
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
![Page 4: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/4.jpg)
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)
![Page 5: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/5.jpg)
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]
![Page 6: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/6.jpg)
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
![Page 7: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/7.jpg)
7
BUILT-IN FUNCTION
• PMT• IIF• Date, DatePart, DateDiff
![Page 8: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/8.jpg)
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)
![Page 9: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/9.jpg)
9
IIF
• The IIf function 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)
![Page 10: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/10.jpg)
10
DATE ARITHMETIC
• The DatePart function enables you to isolate a specific part of a date, such as the year.– DatePart(“yyyy”, [Employees]![HireDate])
![Page 11: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/11.jpg)
11
NEXT CLASS
• Access Chapter 3– Aggregate function
• Access Chapter 4
![Page 12: DAY 17: ACCESS CHAPTER 3 Tazin Afrin Tazin.Afrin@mail.wvu.edu Tazin.Afrin@mail.wvu.edu October 17, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062519/5697c0211a28abf838cd2e9f/html5/thumbnails/12.jpg)
THANK YOU LOG OFF