1 / 23

Day 14: Access Chapter 1

Day 14: Access Chapter 1. RAHUL KAVI Rahul.Kavi@mail.wvu.edu October 8, 2013. Last Lecture. Introduction to Databases. Access Interface. Navigation Pane Table Views: Datasheet view (similar to Excel worksheet) Design view (used to modify table). Tables.

garnet
Download Presentation

Day 14: Access Chapter 1

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. Day 14:Access Chapter 1 RAHUL KAVIRahul.Kavi@mail.wvu.edu October 8, 2013

  2. Last Lecture • Introduction to Databases

  3. Access Interface • Navigation Pane • Table Views: • Datasheet view (similar to Excel worksheet) • Design view (used to modify table)

  4. Tables • Tables store data about a particular set. • Contains fields and records. • Fields are… • Records are …

  5. Primary key • The primary key uniquely identifies each record in a table. • By default, tables have an ID field that is an autoincremented integer • The value of ID goes up by one for each new record

  6. Examples of Primary keys (depending on context) • Username in bank users table. • Email ID in a contacts table. • WVU Student ID in students table. • Country domain id (.us, .in, .ch, .cn, .uk, etc.) in internet domains table. • Facebook URL of CS101 class (https://www.facebook.com/wvucs101) if making a tables of unique URLs.

  7. Navigation Bar • Record navigation • Forward, Back • First, Last • Record number (position of the record, not primary key) • New Record • Search

  8. Forms • Allows you to enter, modify, or delete table data • This can also be done in the Datasheet view, but forms allow you to customize what fields are shown and even work with multiple tables

  9. Queries • Queries are questions you ask about the data • Returns fields, or calculations on fields for records that match the criterion given

  10. Reports • Formatted information from tables or queries • Access has are different tools for designing, modifying, and viewing reports

  11. Saving • Word, Excel, and Powerpoint all work from memory • This means all changes are only stored in temporary storage until you save • Access data works directly from storage • As soon as you finish adding/editing a record, the changes are written to disk • Access design (of tables, forms, queries, and reports) works from memory • You must manually save any database design changes

  12. Utilities- Compact and Repair • As you modify and delete records, not all of the space is reclaimed • Compact and Repair reclaims that space by creating a new database, copying all definitions over, and finally copying the data over. • Database Tools->Tools->Compact and Repair Database

  13. Utilities- Backup • File->Save & Publish->Back Up Database • This creates a copy of the database, with the date added to the filename • If you are about to try something new for the first time, especially if it involves deleting something, backup your database first so you have a good copy to fall back on if you mess up

  14. Filtering • First, select the field you want to filter on • Home->Sort & Filter->Filter • Home->Sort & Filter->Selection can make common filtering easier • Advanced filtering allows you to filter by multiple fields

  15. Sorting • Sorting changes the order of the records in a table • Home->Sort & Filter-> Ascending, Descending • Really, both filtering and sorting are special cases of queries • Other queries can incorporate sorting and filtering techniques

  16. Access Versus Excel • Excel is often easier if you are working with data with simple relationships • As the complexity of your data increases, the need for a logical organization increases • Access allows you to specify that organization

  17. Use Access When you: • Require multiple related tables to store your data • Have a large amount of data • Need to connect to and retrieve data from external databases • Need to group, sort, or total data based on multiple parameters • Need to allow multiple users to simultaneous modify the data

  18. Use Excel when you: • Only need one worksheet to handle all of your data • Have mostly numeric data • Want to run “what if” analysis on your data • Need to create complex charts and/or graphs

  19. Access is relational • This means you can manage groups of data (tables) and set rules (relationships) about how those tables interact • Relationships are defined in the Relationships Window using Join Lines

  20. Relationship Window • Database Tools->Relationships or Table Tools->Table->Relationships • Add in the tables you want to join together • Before you can create relationships, you must close the tables involved • Drag line from the primary key in one table, to the foreign key in the other table

  21. Referential Integrity • Referential Integrity causes Access to ensure the record exists for the primary table before it can be referenced in the related table • If Referential Integrity is enabled, Cascading of Updates and/or Deletes can be enabled • Delete of primary record deletes related record • Update of primary record ID updates related record’s foreign key

  22. Next Class • Database design • Required tables • Avoiding redundancy • Data type selection • Calculated fields • Keys • Sharing data with Excel • Relationship types

More Related