Day 17: Access Chapter 2-3

1. Day 17:Access Chapter 2-3 RAHUL KAVIRahul.Kavi@mail.wvu.edu October 17, 2013

2. Last class • Relationship Types • Many-to-one • One-to-one • Many-to-many • Calculated Fields • Relationship Manager

3. Today’s class • Queries • Calculated Fields in Queries • Basic Queries • Multi-table queries

4. Queries • A Query is a question one asks about the data stored in a database • Access responds by displaying specific records that answer the question • In creating a query, we tell access which fields are needed(in one or more tables) and what criteria needs to be met

5. Calculated Fields in queries • New fields can be calculated from existing fields using an expression • Expressions can include the following: • Identifiers (names of fields) • Arithmetic operators (*, +, etc.) • Functions (Date(), IIf()) • Constants (numbers such as 12 or 0.35)

6. Expression Syntax • You should give calculated fields a name by prefixing the expression with the name followed by a colon • Example: • NewBalance: [Balance] + [MonthlyInterest] • After the colon, you can use any functions or mathematical operators to calculate the value based on existing fields and/or constants

7. Expression builder • Right click the field->Builder • Select the field, then • Query Tools->Design->Query Setup->Builder • Expression builder provides graphical tools for looking up operators (functions and arithmetic operators) and operands (existing fields, constants)

8. Functions • Like Excel, Access has many functions for performing calculations • PMT • IIF • DateDiff

9. PMt • Identical to PMT in Excel • Pmt(rate, num_periods, present_value) • Rate is the interest rate per payment period • Num_periods is the number of payment periods • Present_value is the present value of the loan (which should be negative for most situations)

10. IIF • IIF is the Access equivalent of IF in Excel • Iif(expression, truepart, falsepart) • Expression is a comparison that is evaluated • If expression is true, Iif returns the value of truepart • If expression is false, Iif returns the value of falsepart

11. Date Arithmetic • Date()- returns the current date • Now()- returns the current date and time • DateDiff(format, date1, date2) • Format: Result in years, seconds, quarters, etc • Date1 and Date2 specifies the date for which the difference has to be calculated

12. Aggregation (summary queries) • Summary queries allow you to calculate summary statistics from your data • Avg, Count, Max, Min, StDev, Var, Sum • These functions can be used as calculated fields

13. Totals Queries • Aggregation can also be done as a totals query • Query Tools->Design->Show/Hide->Totals • Select how you would like to aggregate each field

14. GROUp by • Group By groups the data before the aggregation functions are applied • This allows you to find summary statistics for each group

15. Lookup fields • Lookup fields are a nice way of hiding the foreign keys • Instead of displaying the foreign key, the lookup field looks up the record for that key and displays whatever fields you set up • Lookup fields also provide a dropdown to select the record you want to establish the relationship with • Internally, it is still just stored as the foreign key

16. Calculated Fields • Calculated fields can also be added to table definitions for commonly used calculations • Simply select Calculated as the field type and input your expression into expression builder

17. Next Class • Expression Builder • Forms • Create, modify • Sorting • Form Sections • Control Types • Reports • Create, modify • Sorting • Report sections • Control Types