1 / 63

Lab 4

Lab 4. Working with Multiple Tables. Objectives. Create a table using the Table Wizard. Create an input mask. Create a lookup field. Set required properties. Copy and import data between tables and applications. Add a calculated field to a query. Create a crosstab query.

cree
Download Presentation

Lab 4

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. Lab 4 Working with Multiple Tables

  2. Objectives • Create a table using the Table Wizard. • Create an input mask. • Create a lookup field. • Set required properties. • Copy and import data between tables and applications. • Add a calculated field to a query. • Create a crosstab query. • Insert a subdatasheet in a table. • Define table relationships and enforce referential integrity. • Print a database relationships report.

  3. Concepts • Input Mask - An input mask controls how data is entered in a field of a table, query, or form. • Lookup Field - A lookup field provides a list of values from which the user can choose to enter data into that field. • Copy and Import - Selections can be copied or duplicated to new locations in a database, between databases, or to other applications or imported from another application and converted into a format that can be used in a table. • Required Property - The Required property specifies whether a value is required in a field.

  4. Concepts • Calculated Field - A calculated field displays the result of a calculation in a query. • Crosstab Query - A crosstab query summarizes table data and displays it in a tabular format. • Subdatasheet - A subdatasheet is a data table nested in another data table that contains data related or joined to the table where it resides. • Relationship - A relationship establishes the association between common fields in two tables.

  5. Outline • Creating a Table with the Table Wizard • Controlling Field Input • Changing Field Properties • Creating an Input Mask • Creating a Lookup Field

  6. Outline • Copying and Importing Data • Copying Data from Another Table • Importing Data from another Application • Making Fields Required • Using Calculations in Queries • Creating a Multi-table Query • Adding a Calculated Field

  7. Outline • Creating a Crosstab Query • Using the Crosstab Query Wizard • Refining the Query Design • Using Subdatasheets • Inserting a Subdatasheet into a Master Table • Displaying and Printing a Subdatasheet

  8. Outline • Defining Table Relationships • Establishing a One-to-One Relationship • Establishing a One-to-Many Relationship • Enforcing Referential Integrity • Printing a Relationships Report • Backing up and Restoring a Database • Lab Review

  9. Creating a Table with the Table Wizard • Helps you create a new data table • Leads you step by step through process • Can select … • Type or category of table • Table design • Fields • Assigns a primary key • Creates a related data-entry form

  10. Table Wizard

  11. Table Wizard Prompts

  12. Controlling Field Input • Field Properties • Field characteristics, for example … • Name • Size • Data type • Input Mask • Controls where data is entered • Makes the task of entering data easy • Lookup Values

  13. Input Mask Social Security Number • Access automatically inserts hyphens for a social security number • If user enters hyphens, input mask will correct error

  14. Characters for Input Masks

  15. Characters for Input Masks

  16. Concept 2: LOOKUP FIELD • Provides a list of values • Values can be from … • A Lookup List - an existing table • A Value List - a fixed set of values that are defined

  17. Lookup List • An association is created when a lookup field uses an existing table • Foreign key – a field in one table that refers to the primary key in another table • Indicates how tables are related • Field names do not have to match • Data types must be the same

  18. Value List • Values entered when lookup field is created • Values should be static • Examples - Mr., Mr., or Miss • Selected value stored in the record • No association to a related table

  19. Creating a Value List with the Wizard

  20. Lookup Tab Entry

  21. Concept 3: COPY AND IMPORT • To copy - duplicate to another location • New locations in a database • Between databases • To other locations • To import - retrieve data that has been saved in another format

  22. Copy and Import • Source • Destination • System Clipboard • Office Clipboard

  23. Importing • File/Get/External Data/Import • Select type of import file • Import Spreadsheet Wizard is started

  24. Importing Data from Another Application

  25. Concept 4: REQUIRED PROPERTY • Value is required in a field • Property can be set to yes or no • If yes, value cannot be null (zero) • Can be set for any type of field except AutoNumber • Primary key will not accept null values

  26. Required Property Set to Yes

  27. Using Calculations in Queries • Create query • Select fields • Enter expression in design grid • Run query

  28. Concept 5: CALCULATED FIELD • Displays the results of a calculation • Examples • Sum of all inventory • Average salary for a department • Highest sale figures • Create your own calculation • Use a function

  29. Function Predefined Calculations

  30. Calculated gross pay Using a Calculated Field Calculated field formula

  31. Concept 6: CROSSTAB QUERY • Summarizes table data • Displays data in a tabular format • Field values • Calculated by sum, average, and count • Grouped along left side and across the top of the datasheet Why are crosstab queries useful?

  32. Using the Crosstab Query Wizard • Open the Crosstab Query Wizard • Select a table or a query for the crosstab results • Select fields to use as row headings • Displayed along left side of data sheet • Select fields to use as column headings • Displayed along top of the data sheet • Select fields to be calculated and function

  33. Creating a Crosstab Query • Create a regular query • Use to create a crosstab query that will show the average number of hours per employee per week • Run query

  34. Crosstab Query Result • Crosstab query result • Automatically calculated the average number of hours per employee per week

  35. Refining the Query Design • Rename columns • Change heading to "Average Hours" • Move columns • Columns are in date order

  36. Subdatasheet Concept 7: SUBDATASHEET • A data table that is nested in another data table • Contains data related or joined to the table where it resides • Can view and edit data easily

  37. Subdatasheet • Master table • Child fields • Master fields

  38. Creating a Subdatasheet

  39. Subdatasheet Results

  40. Concept 8: RELATIONSHIP • Establishes the association between common fields in two tables • Related fields • Same data type • Contains same kind of information • Can have different field names

  41. Defining Table Relationships • One-to-one • One-to-many • Many-to-many Can you give examples of each of these relationships?

  42. Referential Integrity • Rules • Ensures that relationships are valid • Ensures that related data is not accidentally changed or deleted

  43. Establishing a One-to-One Relationship • Association between two tables • Primary key in each record of one table corresponds to the matching value in the related table

  44. Establishing a One-to-Many Relationship • Association between two tables • Primary key field value in each record corresponds to the value in the matching field of many records in the related table

  45. Enforcing Referential Integrity • An erroneous change to a record in one table could adversely affect its relations • Prevent this with referential integrity • Once set, warning message displayed if rules broken

  46. Referential Integrity

  47. Printing a Relationship Report • Choose File/Print Relationships • A preview of report is displayed • Database name and creation date are automatically used as the report header. • Print report and save for future reference

  48. Backing up and Restoring a Database • Perform periodic backups • Power outage or system failure • Lost or corrupted file • Backup • Saves a copy of the database • Backup programs • Windows Explorer • My Computer • Microsoft Backup

  49. Restore Operation • Copies a backup file from storage medium • Use same method to restore as you used to backup • Tools/Database Utilities/Compact and Repair Database • Performance enhancement

  50. Key Terms • Backup - Operation that saves a copy of the database or other type of file to a storage medium other than the computer's hard disk. • Calculated Field - Field based on information contained in multiple fields of the database, or the results of an expression rather than actual data. • Child Field - One or more fields in the embedded subdatasheet that will be linked to master fields in the master table. • Copy – To duplicate a selection to another location. • Crosstab Query - Summarizes large amounts of data in an easy-to-read, row-and-column format.

More Related