Week 7: Introduction to Access

1. Week 7:Introduction to Access DivyaGanesan divya.ganesan@mail.wvu.edu February 24, 2014

2. Managing data • We have a necessity to store, manage, retrieve and access information in the form of data. • Let us take an example of student data • First Name, Last Name, Email, Major, Subjects taken, etc • This data has to be stored in some location • This is called the table (Like the ones used in Excel) • Lets call that “Student_Table”

3. table • A Table is the collection of related records. • Information about students can be stored in a table • Student_Table

4. Columns Fields and records • The Table consists of many columns • A Field is the smallest data element contained in a table. Examples of fields are first name, last name, address etc. • The Table consists of many rows • A Record is a complete set of all of the data elements(fields) about one person, place, event, or concept. Rows

5. Getting data • To retrieve data from the table, we need to be able to say • Get the list of all students in Student table • Get the list of all students with the last name Smith • This is what is called Query • We can write that as • Select * from Student_Table • Select * from Student_Table where Last_Name = ‘Smith’ • Query is a question that you ask about the data in the tables of your database

6. Getting data • The query for list of students with the a specific first name and last name, may return multiple records. • How do we know • which John Smith is Business major • and which John Smith is Math major • Use the other columns in the table to differentiate

7. Primary key • There can be more than one “John Smith” in English major • Something unique about the student record can help identify which record we are looking for • Student ID can be used as the Unique column (field) • This is called the primary key of the table. • A primary key is the field that uniquely identifies each record in a table. • This is forced to be unique and the table can be created so that no duplicate Student ID is allowed.

8. Multiple tables • There can be many fields in the table • There can be many tables to store related information • Table 1 or Student_table • Table 2 or Address_table

9. table relationship • How can we link these two tables • Using the Student_ID • This is called relationship between the tables

10. database • Many such tables can hold different information related to students. • These tables which may be related each other together make what is called the Database

11. database • A computer database is a structured collection of records or data that is stored in a computer system. • The term "database" refers to the collection of related records, and the software should be referred to as the database management system (DBMS);

12. MS access • What is Access? • It is Relational database software which is used to store data and convert it into information. • This Database software is used primarily for decision making by business that compile data from multiple records stored in tables to produce informative reports.

13. Spreadsheet vs database • These can be created in spreadsheet too. So why a database? • The database offers • Constraint and enforcement of rules • Unique, no duplication, etc • Relationship • Table 1 and Table 2 are related using Student ID

14. spreadsheet vs database

15. Working with Access table • The Navigation Pane organizes and lists the database objects in Access database. ( You can see various tables, queries, reports created on the left side) • The Datasheetview is where you add, edit, and delete the records of a table. • The Design view is where you create tables, add and delete fields, and modify field properties.

16. Managing records in the table • Add – simply add details to each field at last • Edit – move cursor to appropriate field and edit the details • Delete- Right click at starting of record and select DELETE option

17. Managing fields in the table • Go to View -> Design view • Click on last empty cell of FIELD NAME and provide the appropriate DATA TYPE. • Switch to DATA SHEET view and observe the new Field added to the records. • Note: You can observe the Field properties at bottom

18. Other access OBjects • Access – Database Management System has the following: • FORM is an object that enables you to enter, modify, or delete table data • QUERY is a question that you ask about the data in the tables of your database. While defining a query we tell access which fields are needed and what criteria needs to be met • REPORT contains professional – looking formatted information from underlying tables or queries.

19. Filtering • Filter displays a subset of records based on specified criteria • Filter by selection • Filter by form

20. Filtering by selection • Open the table • Click on any of the field • Click on Selection • Choose Equals to / not equals to/ Greater than/ Less than/ Between and have the filtered records.

21. Filtering by form • Can search for records based on multiple conditions and by operators such as: <, >, <=, >= • Click Advanced • Choose Filter by Form • Enter value in the field • Select Toggle Filter to filter by form.

22. sorting • Sorting lists the records in a specific sequence such as ascending / descending. • Sorting on single field • Sorting on multiple fields

23. Sorting on single field • Select a field and choose ascending / descending to sort records.

24. Sorting on multiple fields • Fields to be sorted must be adjacent • Select Adjacent fields (Use Shift Key) (or) • Click and Drag to highlight • Choose ascending/descending

25. Compact and repair • Reduces the size of the database • Perform this after you done working with access database. • File->info->Compact and repair database

26. Questions?