Database Systems: Access Review

1. Review: ACCESS Database Systems Chapter 1: Finding Your Way Through a Database Chapter 2: Relational Databases and Multi-Table Queries Chapter 3: Customize, Analyze, and Summarize Query Data Chapter 4: Create, Edit, and Perform Calculations in Reports

2. Chapter 1 • Tables • Queries • Reports • Forms • Macros • Modules Objects

3. A database is made up of one or more tables • Field • Record • Table • Database Individual fields Individual tables in a database Records

4. Work with Table Views Design View Datasheet View

5. Datasheet View Primary key field Navigation buttons Scroll bar Navigation bar

6. Design View Key symbol identifies primary key field • Click F6 to switch between the upper and lower panes Set field properties in the lower pane

7. Primary & Foreign Keys • Primary Key (PK)- Uniquely identifies each record in a table. It is needed for searching the database. • Some times if there is no unique value in a table the database designer may use a auto number in Access to use a unique serialized number as the primary key. • Examples: SIN, Student Number, heath card number.

8. Primary & Foreign Keys • Foreign Key (FK)- A field in one table that is a primary key in another table. It is with these values we can build relationships between tables. • The values of an FK must satisfy one of two conditions: - any FK-value is a value appearing in the referenced table or a null value. • Non Key- a regular field in a database.

9. Forms, Queries, and Reports Report Query Form

10. Filters • Create a subset of records • Do not change underlying table data • Two types • Filter by Selection • Filter by Form

11. Filter by Selection Table before filter by selection Results of filter Filter by selection being applied from pre-determined criteria

12. Filter By Form Inequity setting used in a Filter by Form process Selection of criteria during Filter by form process

13. Sorting Table Data Last Name field sorted descending Last Name field sorted ascending

14. Establishing Relationships Click and drag to create a relationship Primary Key Foreign Key

15. Establishing Relationships Infinity symbol notes referential integrity has been applied

16. Referential Integrity Enforce Referential Integrity

17. Chapter 2 Five different ways to create tables • Using Table Wizard • Using data sheet view • Using design view • Creating tables with queries • Creating tables by importing external data sources

18. Data Types of Fields • Attachment    Files, such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access. • AutoNumber    Numbers that are automatically generated for each record. • Currency    Monetary values. • Date/Time    Dates and times. • Hyperlink    Hyperlinks, such as e-mail addresses. • Memo    Long blocks of text and text that use text formatting. A typical use of a Memo field would be a detailed product description. • Number    Numeric values, such as distances. Note that there is a separate data type for currency. • OLE Object    OLE objects (OLE object: An object supporting the OLE protocol for object linking and embedding. • Text    Short, alphanumeric values, such as a last name or a street address. • Yes/No    Boolean values.

19. Work with Properties Field Size property Caption property

20. Properties • Field size- Adjusts the size of the text field • Format- changes the way field is displayed. Does not effect the value. • Input Mask- facilitates data entry • Caption- Label used for the field • Default Value- the automatically entered value • Validation Rule- Rejects records that do not conform to rules entered. • Validation Text- Error returned when validation rule is broken. • Required- Forces user to enter in value if selected. • Allow Zero Length- Allows text length of 0. • Indexed- increases efficiency of search on the field

21. Table Design Considerations 1. Field Size Property2. Validation Rules3. Store Data in its Smallest part4. Avoid Calculated Fields5. Plan for Date Arithmetic6. Design Multiple Tables

22. Create Tables – Primary Key Primary Key icon Primary Key Field

23. Query Types • A select query retrieves data from one or more tables and displays the recordset in a datasheet. You can also use a select query to group data, and to calculate sums, counts, averages, and other types of totals. • A parameter query displays a dialog box when it runs, prompting the user to enter information to use as criteria for the query. • A cross-tab query arranges a recordset to make it more easily visible, using both row headings and column headings. Data can be seen in terms of two categories at once. • An action query creates a new table or changes an existing table by adding data to it, deleting data from it, or updating it. • An SQL query is created by using a statement in Structured Query Language (SQL).

24. Wizard/Design View • As with forms and reports, Access provides two basic ways to create queries: by using a wizard, and in Design view. • The wizard, also known as the Simple Query Wizard, gives you a head start in setting up your query's structure by making some arrangements for you. • In Design view you have total control when creating a query. You drag the fields you want to a grid, and then you enter the criteria for selecting the data to be retrieved.

25. Specifying Criteria in a Select Query Fields in design grid allow us to specify criteria for the dataset • Field row – displays the field name • Sort row – enables you to sort the dataset • Show row – controls whether or not you see a field in the dataset • Criteria row – determines the records that will be selected for display

26. Specifying Criteria – And and Or Or Criterion and resulting dataset And criterion and resulting dataset • OR finds records that can match one or more conditions • AND finds records that must match all criteria specified

27. Database Analysis Tools SELECT fname FROM customer WHERE lname=“Smith” ORDER BY fname Returns records in the fname field only where the lname field is equal to Smith. Records are sorted in ascending order by first name • Clauses - Added to statements to restrict/specify records • WHERE clause - Specifies which records to return • ORDER BY clause - Specifies sort order

28. Structured Query Language SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.Freight FROM Orders WHERE (((Orders.OrderDate)>#3/25/2010#)) OR (((Orders.EmployeeID)<7));

29. Structured Query Language SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.Freight, [Order Details].Quantity FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE (((Orders.OrderDate)>#3/25/2010#) AND (([Order Details].Quantity)>7)) OR (((Orders.EmployeeID)<7));

30. Structured Query Language SELECT DISTINCTROW Account.BID, Sum(Account.Balance) AS [Sum Of Balance] FROM Account GROUP BY Account.BID;

31. Data Aggregates Use a Totals Query to Group Grouping field Field to be totaled select Location, sum(Balance) where Account, Branch group by Location • Organizes query results into groups • Only use the field or fields that you want to total and the grouping field

32. Creating Queries – Using the Query Wizard Query Wizard icon Select Simple Query Wizard

33. Creating Queries – Using the Query Wizard: continued

34. Chapter 3 • Create a calculated field in a query • Create expressions with the Expression Builder • Perform date arithmetic • Create and work with data aggregates

35. Parts of an Expression Identifiers Value [Price] * [Quantity_On_Hand] * 0.7 Operator • Constants: a named item whose value remains • constant while Access is running • Values: literal values such as the number 1,75 or • the word “Hello”

36. What Are Expressions Used For? • You can use an expression to: • perform a calculation • retrieve the value of a field or control • supply criteria to a query • create calculated controls and fields • define a grouping level for a report Result of a calculation in a query formed by using an expression

37. What Are Expressions Used For? • You can use an expression to: • perform a calculation • retrieve the value of a field or control • supply criteria to a query • create calculated controls and fields • define a grouping level for a report Result of a calculation in a report formed by using an expression

38. Creating a Calculated Field Expression using existing fields in a database Total Value: [Price] * [Quantity_On_Hand] Descriptive name for new field preceded with colon (:) • Use correct syntax • Assign a descriptive name to the field • Enclose field names in brackets

39. Calculated Field in a Query Query Design View Calculated Field • A calculated field is added to a blank column in the design grid

40. Add a Total Row in a Query • The total row can be added to the design grid by clicking the Totals Icon Totals Icon Total row added to the query

41. Expression Builder Expand folders by clicking plus sign Fields available in current query • Use expressions to add, subtract, multiply, and divide the values in two or more fields/ controls

42. Variations of DatePart Function • =DatePart(“q”, “01/22/2007”) • Displays the quarter in which the given date falls • =DatePart(“h”, Now()) • Displays the hour part of the current date • =DatePart(“d”, Now()) • Displays the day part of the current date

43. Variations of the DateDiff Function • =DateDiff(“d”, [orderdate],[shippeddate]) • Displays the number of days between ordering and shipping • =DateDiff(“m”,#01/06/2006#, #07/23/2007#) • Displays the number of months between the two dates • =DateDiff(“d”,[dateborn], Now()) • Displays the number of days between the dateborn field and the current date

44. Chapter 4 Reports seven sections: - report header - page header - group header - details - group footer - page footer - report footer