Creating a Database in Access 2003 Lab

1. Office Access 2003Lab 1 Creating a Database

2. Objectives • Plan and create a database. • Create and save a table structure. • Define field names, data types, field properties, and primary key. • Change views.

3. Objectives continued • Enter and edit data in Datasheet view and Data Entry. • Insert a picture. • Adjust column widths. • Use the Best fit feature.

4. Objectives continued • Add records. • Delete records. • Preview and print a table. • Close and open a table and database.

5. Database Object Field Name Data Type Field Property Primary Key Graphic Column Width Concept Preview

6. Outline • Introducing Access 2003 • Starting Access • Exploring the Access Window • Creating a New Database • Planning and Designing a Database • Creating and Naming the Database

7. Outline continued • Creating a Table • Defining Field Names • Defining Data Types • Defining Field Properties • Entering a Field Description

8. Outline continued • Creating a Table continued • Defining a Primary Key Field • Defining Date formats • Defining Additional Fields • Editing Field Definitions • Saving the Table Structure

9. Outline continued • Entering and Editing Table Data • Using Table Datasheet View • Entering Data in a Datasheet View • Editing Data • Inserting a Picture • Navigating a Datasheet

10. Outline continued • Changing a Column Width • Resizing a Column • Using Best Fit • Adding Records in Data Entry • Deleting Records

11. Outline continued • Previewing and Printing a Table • Previewing the Table • Changing the Page Orientation • Printing a Selected Page • Closing and Opening a Table and Database

12. Outline continued • Exiting Access • Key Terms • FAQs • Discussion Questions

13. Introducing Access 2003 • Starting Access • New file task pane is displayed • Start by first creating a new database or opening an existing one • The Access Window features • Database toolbar • Taskpane • Workspace • Status bar

14. DBMS • Three most widely used database programs • Microsoft Access • Corel Paradox • Lotus Approach

15. Lotus Approach Corel Paradox

16. Title bar Menu bar Getting started task pane Database toolbar Mouse Pointer Workspace The Access Window

17. Creating a New Database • Plan • Design • Develop • Implement • Review

18. DATABASE Concept 1 Database Database Management Systems DBMS (Database Manager, relational Tables) • Database – Organized collection of related information • Table – Vertical columns & horizontal rows where information is stored • Record • A row • Information about one person, place, or thing • Field • A column • Smallest unit of information • Access database can contain multiple tables that can be linked to produce combined output from all tables. • This type of database is called relational database.

19. Planning and Designing a Database • Analyze current record-keeping system • What forms are currently used for data entry? • What reports are needed? • What data will be in the tables? • How will the tables be related?

20. Creating and Naming the Database File • File New Database dialog box • Database file name • Default is db1 • Extension is .mdb • Replace with new name • Location of file • Default is My Documents folder • Database window • Opens in workspace • Name of database displayed in title bar

21. Places bar Default location Default name is selected Restricts to database files Creating and Naming the Database

22. Concept 2 Object

23. Database Objects • There are seven main components of a database in MS Access: • Tables. Use tables to store database information. Table is the heart of the Database, since it contains the actual data and all other objects are base on the Table. We use Tables to store all the data in the database. Each row of the table contains one Record. Records are made up of Fields that contain a particular piece of data in the table • Forms. Use forms to enter or edit the information in your tables. Forms let you view one record at a time. Easier to access and use when entering or modifying Data than a Table • Reports. Use reports to deliver a professional presentation or written report to your class. • Queries. Filter data so that you retrieve selected records or fields from the database. • Pages. Create HTML pages from a database quickly and efficiently. • Macros. Automate tasks that you perform on a regular basis in a database.Changes Modules. Automate a group of related procedures in Access 2000.

24. Creating a Table • The table object is the basic unit of a database • Must be created first before other objects • Cannot open more than one database file at a time

25. Datasheet View • Design View • Table Wizard Three Ways to Create a Table

26. Table Design Toolbar Two open windows Field definition grid used to define fields Design View

27. Table Design View Primary Key Field Names Data Type Field Properties Description

28. Concept 3 Field Name • Used to identify data stored in the field • Up to 64 characters long • Cannot start name with a space • Examples: • Last name • First name • Address Check page 12 (design view buttons)

29. Click to open drop-down list Field name Default data type Help information on data types Defining Field Names

30. Default data type Data types Defining Data Types

31. HW • Please download the new homework from my website ://hani/hani

32. Concept 4 Data Type • Data type defines the type of data field will contain • Used to ensure right kind of data is entered • Important to choose appropriate data type

33. Text (words, combination of words and numbers, numbers that are not used in calculation, names and phone numbers) up to 255 Memo Text that is too long to be stored in a text field, up to 65536 Number Used when you want to perform calculations on the values in the field. Date/Time Currency AutoNumber Sequential number that is automatically incremented by one when ever a new record is added to a table. Yes/No Accepts only yes or no, T or F, OLE Object An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) inked to or embedded in a Microsoft Access table. - insert object options Hyperlink Accepts hyperlink entries that are paths to an object. The easiest way to insert a hyperlink address in a field or control is to clickHyperlink on the Insert menu. Lookup Wizard Creates a field that allows you to choose a value from another table or from a list of values by using a list box . Data Types

34. Concept 5 Field Property • Characteristic that defines a field • Each data type has a set of properties • Enhances the way table works

35. Field Size Format Input Mask Caption Default Value Validation Rule Validation Text Required Allow Zero Length Indexed Field Properties

36. Properties (1 of 2) • Default Value Specifies a String value that is automatically entered in a field when a new record is created • Validation Rule (student file) M or F in gender • Validation Text • You must specify M or F • Field size Number of character • Format ‘<‘ , ‘>’ lower and upper case in text • Input Mask You can use the InputMask property to make data entry easier and to control the values (000) 000-0000 • Caption Specifies a field label other than the field name

37. Properties (2 of 2) • Required Rejects any record that does not have a value entered for this field. (put required field= yes) • Allow Zero Length Allows text or memo strings of zero length (“ ”),null • Indexed Increase the efficiency of a search on the designated field • Unicode Compression To store the data more efficiency , set to “yes”

38. Field description Help information on current task Entering a Field Description

39. Concept 6 Primary Key • A field that uniquely identifies each record • Most tables have a primary key • Key data MUST be unique • Prevents duplicate records • Used to control sorting order • Used to create a link between tables One example of a field that should not used as the primary key is ………. Field.

40. Defines field as primary key Indicates field as primary key Duplicate values in field not allowed Defining a Primary Key

41. Date/time data type formats Format sample Format name Defining Date Format

42. 11 fields defined Defining Additional Fields

43. More on Creating Tables • Editing field definitions • Saving the table structure

44. ENTERING AND EDITING TABLE DATAViews

45. Datasheet containing one blank record Using Table Datasheet View • Datasheet • Field selector • Record selector • Current record • Record number indicator • Navigation button Table datasheet toolbar Record number indicator

46. Identifies end of table or where new record can be entered or edited Informs you that entry is not valid Entering Data in a Datasheet View

47. Datasheet View • Entering data • Should be accurate and consistent • Typed exactly as you want it to appear • Editing data • Position insertion point in field • Use ESC to cancel changes

48. We are pleased to announce the grand opening of Tom’s Deli Photograph Drawing Object Clipart Concept 7 Graphic

49. Graphics • Graphic: is anon text element or object such as a drawing. • Drawing object: consisting of shapes such as lines and boxes • Picture: is an illustration such as a scanned photograph • Bound object: is stored in a table and connected to specific record and field. • Unbound object: is associated with the table as a whole , not with the specific record.

50. Create a new object Insert existing object Inserting a Picture