1 / 42

Access Tables

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

munin
Download Presentation

Access Tables

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

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

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

  40. Right click on relationship line

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

More Related