Databases,Tables and Forms

1. Databases,Tables and Forms Access Text by Grauer Chapters 1 & 2

2. Introduction to a Database • Defining a database • Creating a database in Access • Creating forms • Using forms to filter data

3. Name: ________________ Address: ________________ City: ________ State: ___ Zip: ______ Member since: ______________ Dues paid? ______ Database • Provide a way to store a related set of data • Simplest form is a card file • Each card a record • Each item a field • Data may be text, dates, numbers, web links, pictures, etc.

4. Two Types of Database • Flat File - Spreadsheet • Works well until data repeats • Must enter data repeatedly • Data must match exactly

5. Parts Part No. Description Supplier Units In Stock Link Key Field Two Types of Databases Relational – 2 or more linked tables Customers Customer ID Name Address City State Zip Invoices Invoice No. Part No. Unit Price Units Customer ID One-to-Many Relationship Many-to-Many Relationship

6. Menu Bar Toolbar Database Window Object Buttons Database Window

7. Creating a Table • Using Table Wizard • Designing a table yourself • Field names • Data types • Primary key • Properties • Views--Datasheet or Design

8. Primary Key Field Names Data Type Description Field Properties Design View

9. Number Text Memo Date/Time Currency Yes/No AutoNumber OLE Hyperlink Data Types

10. Properties • Field size • Format • Input Mask • Caption • Default Value • Validation Rule • Validation Text • Required • Allow Zero Length • Indexed

11. You fill in the underscored blanks Format vs. Input Mask • Format - Defines how data will be displayed • Data stored: 123456789 • Format: ###\-##\-#### • Displays: 123-45-6789 • Input Mask - Displays formatting for input • Causes format to appear on forms, tables • Input Mask: SSN 000-00-0000 • Form field shows: SSN ___ - __ - ____ Only the info in the blanks is stored

12. Table Design Basics • Include all necessary data • Use descriptive names • Store data in smallest parts • Will you ever need to sort by part? • Store address, city, state, zip separately. • Don’t store info based on other data • Don’t store “Age” – age changes! • Store DOB, calculate Age

13. Triangle indicates data has been saved to disk Each column is a field Each row is a record Current Record Total Number of Records Datasheet View

14. Database Forms • Each page shows one record • Field data appears in text boxes • Other controls can be added as in Excel user forms

15. Creating a Form • Controls--objects • Bound control has data source • Unbound control: no data source • Calculated control: expression as data source • Properties • Form Wizard

16. Click and drag to move Toolbox Sizing handles Label (unbound) Text Box(bound to field) Go To First Record Go To Next Record Go To Last Record Form Wizard

17. Combo Box(Drop Down List) Command Button Option Group Check box Advanced Forms Advanced forms use other controls

18. Filter by Form • No query needed • Allows user to define a limited subset