Day 14: Access Chapter 1

Day 14:Access Chapter 1 Tazin AfrinTazin.Afrin@mail.wvu.edu October 03, 2013

Objective • Access interface • Table view • Forms • Query • Report • Saving access file • Sort & Filter • Relational database

Database examples • Check your bank account • Search on google • Buy from ebay • Open mix account

Access interface • Access Ribon • Contains the icons that enable you to perform functions to maintain your database • Home • Create • External Data • Database Tools • Navigation pane • The Navigation Pane organizes and lists the database objects in an Access database.

Table views • Datasheet view • Where you add, edit, and delete the records of a table. • Grid containing columns (fields) and rows (records), similar to an Excel spreadsheet. • Design view • Where you create tables, add and delete fields, and modify field properties.

forms • A form is an object that enables you to enter, modify, or delete table data. • Same manner that you would do in Datasheet view. • Difference is it limit the user viewing only one data at a time • Focus on data entered or modified. • Reliable for data update • Create and edit form structure in Design view.

Query • A query is a question that you ask about the data in the tables of your database. • Display only records that meet a certain criterion. • And fields that are required. • Which products does Northwind purchase from Supplier A? • It will not provides records associated with supplier B

reports • A report contains professional-looking formatted information from underlying tables or queries. • Contains more professional look than query and table. • Present database information using reports.

Saving access file • Microsoft word, powerpoint, excel • Work primarily from memory • Your work is not automatically saved untill you save them • Microsoft access • Works from storage or hard drive • Enter and update data- automatically saved to your hard-drive

Saving access file • But required to save after making change in design like tables, forms, reports • Can use undo to reverse the most recent change • Cannot use multiple undo steps • Multiple users from different computer can work on same access database. • Can work on same table as long as not same record

Compact and repair • Compact and Repair reducesthe size of the database. • Database growth may increase storage requirements and may also impact database performance • Databases that are compacted regularly are less likely to become corrupt – resulting in loss of data. • Database Tools->Tools->Compact and Repair Database

Compact and repair • Is a utility that access provides • Copy the database to a new file behind the scene and while copying – • Delete temporary objects • Delete unclaimed spaces • Results in a smaller database. • Also defragment a fragmented database file • After finished copying deletes original file and save the new one as original.

backup • Backup creates a duplicate copy of the database. • Access makes backing up database files easy • File->Save & Publish->Back Up Database

filters • A Filter displays a subset of records based on specified criteria. • Filter by Selection displays only the records that match the selected criteria. • Filter by Form displays table records based on multiple criteria. Filter by Form enables the user to apply the logical operators AND and OR.

filters • Select field you want to apply filtering • Home->Sort & Filter->Filter • Home->Sort & Filter->Selection • Home->Sort & Filter->Advanced Filter

sorting • A sort lists records in a specific sequence • Ascending order – lowest to highest • Descending order – highest to lowest • Select the field you want to sort • Home->Sort & Filter->ascending or descending • Home->Sort & Filter -> Advanced

Select access or excel • 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, such as Microsoft SQL Server. • Need to group, sort, and total data based on various parameters. • Have an application that requires multiple users to connect to one data source at the • same time.

Select access or excel • Use excel when you: • Only need one worksheet to handle all of your data. • Have mostly numeric data—for example, you need to maintain an expense statement. • Require subtotals and totals in your worksheet. • Want to primarily run a series of “what if” scenarios on your data. • Need to create complex charts and/or graphs.

Primary Key • A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key of the table. • A table can contain only one primary key constraint.

Foreign key • A foreign key is a column or combination of columns that is used to establish and enforce a link between the data in two tables. • It controls the data that can be stored in the another table. • A link is created between two tables when the column that hold the primary key value for one table are referenced by the column in another table. • This column becomes a foreign key in the second table.

Referential integrity • Make sure that relationships between records in related tables are valid. • Ensures that you do not accidentally delete or change related data. • You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. • Cannot enter new account in the account table for a customer who does not exists. • But can enter new account with NULL value in customer ID

Relational database • Access is known as a relational database management system (RDBMS) • Using an RDBMS, you can manage groups of data (tables) and then set rules (relationships) between tables.

Relationships window • Relationships in a database are represented by the lines between the tables • Connect the primary key from one table to the foreign key of another.

Create relationship • Database Tools ->Relationships • Add the two tables that you want to join together • Drag common field ->from the primary table onto the related table • The data types of the common fields must be the same. • Check the Enforce Referential Integrity check box.

Next Class • Access chapter 2 • Designing data • Creating tables • Understanding table relationship • Sharing data with excel • Establish table relationship

Thank You Log Off

Day 14: Access Chapter 1