BUSINESS DRIVEN TECHNOLOGY Plug-In T5 Touring Access

1. BUSINESS DRIVEN TECHNOLOGY Plug-In T5 Touring Access

2. LEARNING OUTCOMES • Describe the primary functions of Microsoft Access • List and describe the steps for creating a table in Microsoft Access • List and describe the steps for creating relationships in Microsoft Access

3. LEARNING OUTCOMES • List and describe the steps for creating a form in Microsoft Access • List and describe the steps for creating a query in Microsoft Access • List and describe the steps for creating a report in Microsoft Access

4. Introduction • Database - maintains information about various types of objects (products), events (transactions), people (employees), and places (warehouses) • Relational database model - a type of database that stores its information in the form of logically-related two-dimensional tables

5. Introduction • Thus plug-in details how to build a relational database applications and it associated tools • This plug-in focuses on the four basic modules of Access : • Tables • Forms • Queries • Reports

6. Creating an Access Database • An Access database file contains several different types of database objects: • Saved queries for retrieving and organizing data • Forms for entering and displaying data on screen • Reports for printing table data or the results of queries

7. Creating an Access Database • There are four steps in producing and using a database: • Define the tables in which to: • Store the data • Store the data integrity constraints (such as primary keys and validation rules) • Store the relationships between the tables (including enforcing referential integrity) • Add data to the database, either directly into the table or using a form • Extract information from the database using a query • Create a report to present the information

8. TRY IT - CREATING A NEW DATABASE • To create a blank database: • Click the New toolbar button, select File, New, or pressing Ctrl + N • Click the Blank Database command in the New area of the New File task pane • Select a location to save the file and enter Slopeside Bikes for the database file name

9. TRY IT - CREATING A NEW DATABASE

10. SAVING A DATABASES IN ACCESS • Access is saved different from typical Office documents: • When the data is added, changed, or deleted — Access automatically saves the changes • Due to automatic data-saving feature, when a new database is created, Access saves it to a file before starting work

11. USING THE DATABASE WINDOW AND OBJECT VIEWS • Whenever a database is open, Access displays the Database window • This serves as the central location for working with the database objects (tables, queries, forms, reports, etc.)

12. USING THE DATABASE WINDOW AND OBJECT VIEWS • Important ways to work with database objects: • Click the corresponding object button in the left column of the Database window • Tables, Queries, Forms, Reports • To view a database object, select it and then click the Open button • To change the design of a database object, select it and click the Design View button • To create a new database object of the type currently displayed in the Database window, click the New button • New Table, New Query, New Form, New Report

13. USING THE DATABASE WINDOW AND OBJECT VIEWS • Important ways to work with database objects cont…: • To make a copy of a database object, right-click it and choose Copy from the shortcut menu • Right-click a blank spot in the Database window and choose Paste from the shortcut menu • To rename a database object, select it and press F2 • To delete a database object, select it and press the Delete key • To close the current database click the Close button in the upper-right corner of the window

14. Designing a Database • Use the Design view to define the design of a new blank table • Design view is used to: • Add, remove, or rearrange fields • Define the name, the data type, and other properties of each field • Designate a primary key for the table

15. CREATE A TABLE • To create a new, blank table in Design view and to add fields and properties, select the Table object, click the Design button

16. Adding a Field • To add a new field to a table at the end of the list, click in the Field Name column of the first blank row in the field list • There are up to 64 characters for the field name, including spaces

17. Setting the Field Properties • Each of the fields in a table is described by a set of properties • The field's properties determine how the field's data is stored, handled, or displayed • The properties include: • The field name • The data type • The description • Field size • Format • Validation rule • Caption

18. Setting the Data Type • Every field is assigned a specific data type • The default data type of a new field is Text • To change a field's data type, click in the Data Type column for that field, and select a new Data Type from the drop-down list

19. Setting the Field Properties

20. Field Size Property • The Field Size property controls the amount of space that is allocated for a particular field • Available only for a field that has the Text or Number Data Type • For a Text field, the Field Size specifies the maximum number of characters • A value between 1 and 255, the default value is 50 • For a Number field, select a value from the drop-down list

21. Format Property • The Format property determines how the data is displayed on the screen or how it is printed • Example: • For a field that has the Date/Time data type, choose: • Long Date to display the date as Saturday, January 1, 2005 • Short Date to display a date as 1/1/2005

22. Decimal Places Property • The Decimal Places property sets the number of decimal places for a field that has the Number or Currency data type • It affects only the way the number is displayed • Choose a specific number of decimal places from the drop-down list

23. Input Mask Property • An Input Mask assists in entering valid data into a field • It displays placeholder characters displaying the number of characters that need to be entered • It includes separator characters such as the parenthesis and dash in a telephone number so they do not have to be typed

24. Caption Property • Use the Caption property to label the field in Datasheet view, at the top of the field's column • If the Caption box is left empty (the default value), Access labels the field using the field name

25. Default Value Property • When creating a database in which a field contains the same value that can be assigned to the Default Value property (by default, this property is blank) • Example: • The City field in an address database in which most of the addresses are in the same city (e.g., Denver)

26. Validation Rule • A Validation Rule is an expression that can precisely define the set of values accepted in a field • Example: • A validation rule can be used on a field containing the date an employee was hired to prevent a date in the future from being entered

27. Validation Text • The Validation Text property sets the message displayed if the Validation Rule is rejected • Example: • Such as “Area code must be 303 or 720”

28. Required Property • If the Required property box is set to “Yes,” Access will require that a value be entered into the field when the record is created or modified • If the property box is set to “No” (the default value), the field can be left empty

29. Indexed Property • The Indexed property controls whether a field is indexed • Indexing a field significantly speeds up searching, sorting, or running queries on that field • It requires more space for storing the information • It can make adding, deleting, or updating records slower • The primary key for a table is automatically indexed

30. Smart Tags • Smart tags provide a way to integrate an Access database with other applications • Since Access cannot recognize smart tags dynamically, a smart tag for a field or control needs to be defined • The smart tag will be available regardless of the contents of the field or control • Smart tags are similar to hyperlinks

31. DESIGNATING A PRIMARY KEY • The primary key consists of one or more fields that Access can use to uniquely identify the records contained within the table • A table must have a primary key if it is on the “one” side of a one-to-many relationship • To designate a field as the primary key, select the field, click the Primary Key toolbar button

32. DESIGNATING A PRIMARY KEY

33. Try IT – Build Tables • In the left column of the Database window, make sure that the Tables object is selected • Click Design on the Database dialog box • Create fields for the following: • Field Name = BikeID, Data Type = Number, Field Size = Long Integer, Primary Key • Field Name = Description, Data Type = Text, Field Size = 25 • Field Name = CostPerHour, Data Type = Currency • Close the Tables dialog box, and click Yes to Save the changes • Enter Bikes as the Table name

34. Try IT – Build Tables • Create fields for each entry below, using field sizes and descriptions as appropriate

35. Try IT – Build Tables • Close the Tables dialog box, and click Yes to Save the changes • Enter Customers as the Table name • Create fields for each entry below

36. Try IT – Build Tables • Close the Tables dialog box, and click Yes to Save the changes • Enter Rentals for the Table name

37. SETTING UP TABLE RELATIONSHIPS • Relationships between associated tables must be explicitly defined • The required relationships must explicitly be defined before Access can combine the related data

38. SETTING UP TABLE RELATIONSHIPS • Referential integrity – states that every non-null foreign key value must match an existing primary key value • When one table has a foreign key to another table, the concept of referential integrity states that a record to the table that contains the foreign key can not be added unless there is a corresponding record in the linked table

39. Try IT – Create Relationships • Open the Relationships window by choosing Tools, Relationships • Select each table listed (i.e., Bikes, Customers, and Rentals) and click the Add button • To define a new relationship, click and drag the BikeID from the Bikes table and drop it on the BikeID in the Rentals table • Click the Create button in the Edit Relationships dialog box that Access shows • Select the Enforce Referential Integrity box • Close the Relationships window, and Save the layout

40. Try IT – Create Relationships

41. Try IT – Create Relationships • Complete the diagram with the Relationships shown below

42. Creating Forms And Data Access Pages • An Access form is a window that contains a set of controls to view, enter, or edit database information, typically one record at a time • Data is obtained directly from one or more tables or data that has been extracted using a query

43. TRY IT - CREATE A FORM USING THE FORM WIZARD • In the left column of the Database window, make sure that the Forms object is selected • Click New on the Database dialog box • In the New Form dialog box, select the Form Wizard option and click the OK button • The Form Wizard will start running • In the first Form Wizard dialog box, select all the fields needed to display on the form

44. TRY IT - CREATE A FORM USING THE FORM WIZARD

45. TRY IT - CREATE A FORM USING THE FORM WIZARD • Select the Bike table from the Tables/Query drop down list • Move all the Available Fields, BikeID, Description, CostPerHour, from the Available Fields list to the Selected Fields lists • Click the Next button • In the second Form Wizard dialog box, select the Columnar radio button

46. TRY IT - CREATE A FORM USING THE FORM WIZARD

47. TRY IT - CREATE A FORM USING THE FORM WIZARD • Click the Next button • In the third Form Wizard dialog box, select the Standard style • This style affects the background color or pattern, the fonts, the look of the controls, and other features of the form

48. TRY IT - CREATE A FORM USING THE FORM WIZARD

49. TRY IT - CREATE A FORM USING THE FORM WIZARD • Click the Next button to open the final wizard dialog box • In the final Form Wizard dialog box, the Bike table name is inserted into the title text box • This assigns a name to the form and chooses the way the form will initially be opened • Click the Finish button to have Access create the form

50. TRY IT - CREATE A FORM USING THE FORM WIZARD