Access Tables

1. Access Tables

2. Creating a Table • Design View • Define each field and its properties • Data Sheet View • Essentially spreadsheet • Enter fields • You must go to design view to set field properties the way you want them • Templates(2k7 and earlier) • Create table using predefined templates • Use design view to change field property defaults of template

3. Design View Access 2k7 & 2K10 • Select Create Tab • Select Table Design • Enter your data fields • For each field select the data type • Based upon data type selected you can set additional properties for the field • Enter description (good systems documentation tool)

4. Design View Access 2007/2010 • Define your Primary Key(s) • Highlight field(s) that will be Primary key(s) • Select Key symbol to set primary key • Save your table • You can click on save symbol • You will be asked to give table a name • Click on close symbol • You will be asked if you want to save changes • You will be asked to give table a name

5. Table Design

6. Define Data Type Enter Fields Enter Description for documentation purposes Define additional Properties for each Field

7. Data Types

8. Common Field Properties(available properties function of data type) • Field Size: • determines the length of text and number fields • Format: • customizes the way fields including number, dates, times, and text appear when they are printed or displayed • Input Mask: • makes user data entry easier by displaying a mask (template) into which the user types data • (xxx)-xxx-xxxx

9. Field properties include: • Caption: • the caption specifies an alternative field name that appears in forms and reports as well as the datasheet view of a table or query • More user friendly • Default Value: • the value that is automatically assigned a field when you create a new record

10. Field properties include: • Validation Rule and Validation Text: • Validation Rule property specifies, in a logical expression, the value(s) or range of values that are permitted for the field. • Validation Text is an error message displayed whenever a user enters a value not allowed for the field.

11. Field properties include: • Required: • The required property is “Yes” or “No”. When set to “Yes,” the associated field must have a value—it cannot be empty • Indexed: • Containing either “Yes” or “No,” the indexed property indicates whether or not Access maintains a special, separate data area called an index that speeds searches on the field. • Indexed fields are faster to search.

12. Primary Key In design view, Highlight field you want to be primary key and select Primary key symbol

13. Key symbol indicates that Field One is Primary key

14. Key symbol indicates that Field One and Field Twoare Primary keys

15. Click on Save symbol Enter name you want to Give your table here and click on OK

16. Click on Close symbol and select yes to save changes

17. Enter name you want to Give your table here and click on OK

18. Saved Table

19. Data sheet View Access 2007/2010 • Select Create Tab • Select Table • Table with a default primary key is created • Click on Add New Field and enter Field Label for each Filed you want in table • Enter Design View to define data types and properties for each field • Change default Primary Key Field if necessary • Save your table

20. Data sheet View Access 2007 Select Table

21. Click on Add New Field and enter Field Label

22. Click on View Symbol and select Design View to define field properties

23. Define data type for each field and any properties

24. Entering Data into table • Enter data for each field in a record • Data is automatically saved as you go to enter next record • You will not be prompted to save when you exit the table • Situations where you will be prompted to save when exiting a table • Changes made in design view to field properties • Changing display characteristics(how you view it on the screen)

25. Table (relation) Properties • Each row(record) is unique • Columns(fields) for any record(row) • Single valued(no repeating groups) • Values are like data type

26. Primary Key Attributes • Primary key is a Field/column (or group of fields/columns) that uniquely identifies a given row(record) • the SaleIDcolumn, for example (on the next slide) • Primary key can not be null (entity integrity rule) • Guarantees uniqueness of entities and enables proper referencing of primary key values by foreign key values

27. Primary Key Example Sale Table Primary Key

28. Foreign Key Attribute • Foreign key is an attribute in one table that must match the primary key in another table or be null(referential integrity rule) • A primary key from a different table that has been posted into the table to create a link(relationship)between the two tables • the SalespersonIDof Sales Rep table is a foreign key in the Sale Table

29. Foreign Key example Sale Table Sales Rep Table

30. Creating Relationships • Click on the relationship symbol • From the pop up box add the tables • Establish the relationship between the tables • Enforce referential integrity • Cascade updates • When exiting relationship view, save your changes

31. Referential integrity • Referential Integrity are a set of rules that prevent addition, deletion or modification of data in a table if the changes would create a problem with a relationship • You can’t enter a value in foreign key field of a related table unless there’s a matching value in the primary key of the primary(parent) table • Example- you can’t enter an order for a CustomerID that does not exist in the customer table

32. Referential integrity • Prohibits removal of a parent table row until all the rows in another table(s) referring to the parent table are first removed • You can’t delete a customer record from the Customer table if there are associated invoices—linked back to the Customer table—in the Invoice table. • To remove a “Parent” table record, you first have to delete all occurrences of “child” table records. • Referential integrity essentially prevents “orphans”—records in related tables that have no master record in a “parent” table

33. Referential integrity • You can’t change primary key value in the primary(parent ) table if there are matching records in the related table(s) • If you select cascade update related fields option when establishing referential integrity, changes to primary key in parent table will be updated in the related table(s)

34. Creating Relationships(2k7 & 2K10) Select database Tools tab and then select relationships

35. you will see a box similar to one on right. Select each table and click on add.

36. Then, drag to matching attribute in other table First, Click on SalespersonID

37. Enforce referential integrity • Cascade updates • Select create

39. Primary key/Foreign Key • Establishing relationship • Primary key/Foreign key must be like type data types • Auto# cannot be FK

40. Edit Relationship • Enter relationship window • Right click on the relationship line between tables • Select from the pop-up box • Edit relationship or • delete

41. Right click on relationship line

42. Database demos & exhibits • On class web site • http://bergg.etsu.edu/classpages/4310/4310databasedemos.htm