Day 14: Access Chapter 1

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