DAY 14 ACCESS CHAPTER 1 RAHUL KAVI Rahul

DAY 14: ACCESS CHAPTER 1 RAHUL KAVI Rahul. Kavi@mail. wvu. edu October 8, 2013 1

LAST LECTURE • Introduction to Databases 2

ACCESS INTERFACE • Navigation Pane • Table Views: – Datasheet view (similar to Excel worksheet) – Design view (used to modify table) 3

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

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 5

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 CS 101 class (https: //www. facebook. com/wvucs 101) if making a tables of unique URLs. 6

NAVIGATION BAR • Record navigation – Forward, Back – First, Last – Record number (position of the record, not primary key) • New Record • Search 7

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 8

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

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

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 11

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->Compact and Repair Database 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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 21

NEXT CLASS • Database design – Required tables – Avoiding redundancy – Data type selection – Calculated fields – Keys • Sharing data with Excel • Relationship types 22