Introduction to Microsoft Access

1. Introduction to Microsoft Access Overview

2. Introduction • What is Access? • A relational database management system • What is a Relational Database? • A relational database system creates separate containers of logically related data, and stores each container in a separate table. • Tables are the key objects of a relational database • Tables form the basis for information storage and retrieval • A collection of tables that are related to one another form a database

3. Access Objects • Objects are the structures you create and methods you employ to store, manipulate and retrieve data • Objects include • tables • queries • forms • Reports • Pages (work with live data via the internet/intranet • Macros (start-up routines, Hot keys) • Modules (create apps within the database)

4. Tables • Data is entered into Tables • Tables hold all stored data • none of the other Access objects hold data. • Two dimensional • rows (records) • columns (fields/attributes of interest) • Row ordering is unimportant, • rows can be sorted and rearranged and not change the fundamental table information • Column ordering is unimportant, • A table column may be placed in any particular position

5. each column(field) holds a different characteristic (attribute) about the row (record) it describes the primary key – Attribute(s) that makes each row(record) unique

6. Queries • Data stored in tables can be filtered and sorted in queries • Data filtered and sorted by query can be displayed • Data filtered and sorted by query can be printed in reports

7. Query Types • Selection (projection) queries. • Select queries are the most common type. • They pose questions of the database and return answers in a dynaset(a virtual table) • Subsets of rows(records) are returned when selection criteria are specified to filter the data • All attributes for each record are also returned • Projection returns a subset of records and a subset of attributes for each record

8. Product Category table

9. Selection Query Selection Criteria

10. Resulting Dynaset

11. Projection Query

12. Resulting Dynaset

13. Action Queries • Alters(modifies) data in a table • Add data records to existing table from another table • Delete records in a table • Update records in a table • Creates new table using data from an existing table(s)

14. Forms • Provide a way to view table data one row at a time • Easier data entry • Easier data editing and modifications • Easier validation checks on entered data • Display data from tables or queries but do not actually hold data

15. Datasheet view of Customer Table

16. Form View for Data entry

17. Reports • Used to preview and print data for distribution • Provide formatted, hard-copy output • Displays database information that can be supplied by • tables, • queries, or • both • Can not be used to modify data

18. Example report

19. Example report

20. Creating Access Database Objects(tables, queries, forms, reports) • Design View • Create object from scratch • Wizards( forms, reports & queries) • Simplify initial creation of object • In many cases it is easier to use wizard to create the object, and make any modifications to the object created by the wizard in design view • Table Templates • Auto Form & Auto Report