1 / 82

BUSINESS DRIVEN TECHNOLOGY Plug-In T5 Touring Access

BUSINESS DRIVEN TECHNOLOGY Plug-In T5 Touring Access. 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. LEARNING OUTCOMES.

Download Presentation

BUSINESS DRIVEN TECHNOLOGY Plug-In T5 Touring Access

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. 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

More Related