access tables n.
Skip this Video
Loading SlideShow in 5 Seconds..
Access Tables PowerPoint Presentation
Download Presentation
Access Tables

Loading in 2 Seconds...

play fullscreen
1 / 42

Access Tables - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Access Tables' - munin

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
creating a table
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
design view access 2k7 2k10
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)
design view access 2007 2010
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

Define Data Type

Enter Fields

Enter Description for documentation purposes

Define additional Properties for each Field

common field properties available properties function of data type
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
field properties include
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
field properties include1
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.
field properties include2
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.
primary key
Primary Key

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


Click on Save symbol

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

data sheet view access 2007 2010
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
entering data into table
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)
table relation properties
Table (relation) Properties
  • Each row(record) is unique
  • Columns(fields) for any record(row)
    • Single valued(no repeating groups)
    • Values are like data type
primary key attributes
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
primary key example
Primary Key Example

Sale Table

Primary Key

foreign key attribute
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
foreign key example
Foreign Key example

Sale Table

Sales Rep Table

creating relationships
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
referential integrity
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
referential integrity1
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
referential integrity2
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)

Creating Relationships(2k7 & 2K10)

Select database Tools tab and then select relationships


Enforce referential integrity

  • Cascade updates
  • Select create
primary key foreign key
Primary key/Foreign Key
  • Establishing relationship
  • Primary key/Foreign key must be like type data types
    • Auto# cannot be FK
edit relationship
Edit Relationship
  • Enter relationship window
  • Right click on the relationship line between tables
  • Select from the pop-up box
    • Edit relationship or
    • delete
database demos exhibits
Database demos & exhibits
  • On class web site