MS ACCESS 2010

1. MS ACCESS 2010 Introduction to Databases

2. Instructional Objectives • Students should be able to: • Create a blank database • Set up the tables • Populate tables with data • Maintain data in tables • Add, update, delete • Create queries, forms, and reports • Other topics as time permits

3. Introduction • Databases are used to store, organize, and process data • MS Access is a relational database software package • Relational databases are based on the concept of being able to link tables together based on a common field

4. Database terminology • Field – is an attribute or characteristic of an entity; represents each column in a table • Record – is a collection or related fields; represents each row in a table • File (or Table) – is a collection of related records • Database – is a collection of related files (or tables)

5. Database concepts cont… • Entity – person, place, object, event for which data is collected, stored, and maintained • Each table should have a primary key: a field that uniquely identifies each record in a table • When linking two tables, a primary key of a table is used to link to another table, in which case it is known as a foreign key

6. Launching Access • Launch MS Access • Choose whether you want to create: • a blank database • open an existing database • Choose ‘blank’ database • Save it to disk under the name ‘Mail Order Catalog’ • Click on ‘Create’

7. Mail Order Catalog Database • Needs a database of employees. • It should contain two tables: • Employee table with data on employees • Department table with data on departments in which these employees work

8. Database Objects – Create tab • Tables – used to create, edit, and delete tables • Queries – used to create, edit and delete queries • Forms – used to create, edit, and delete forms • Reports – used to create, edit, and delete reports

9. Creating Tables • Need to specify what method we want to use to create table. • Choose ‘Table Design’

10. Design View of Tables • Design view is used to: • Set table structure. • Define attributes (or fields) for a table. • Define properties for each field. • Set primary key. • Makes changes to an existing table structure.

11. Table Structure for ‘Employee’ Table

12. Enter data in ‘Employee’ Table(datasheet view)

13. Table structure for ‘Department’ Table

14. Data for ‘Department’ table(datasheet view)

15. Linking tables via a common field • Go to ‘Database Tools’ Ribbon • Click on ‘Relationships window’ • Add both tables (one at a time) to relationship window • Close ‘show tables’ window • Point at DeptCode in ‘Employee’ table, left click and drag mouse over to DeptCode in ‘Department’ table • Enforce referential integrity, cascade update, cascade delete • Create the relationship and close the window.

16. Querying a database • What is a query? • To query means to question • Create a query (using query wizard) based on ‘Employee’ table • Fields to include: • EmpNum, EmpFirst, EmpLast, PayRate • Save query as ‘Employee Pay Rate’ • Run query • Adjust column widths to “fit” as necessary • Sort in ascending order of PayRate

17. Query based on 1 or more tables • Create Ribbon, Query Wizard • Employee table fields: • EmpNum, EmpFirst, EmpLast, PayRate • Department table fields: • DeptCode, DeptName • Save query as ‘Employee Pay and Department’ • Run query

18. Setting up a query using ‘Query Design’ • Create Ribbon, Query Design • Choose ‘Employee’ Table • Choose these fields: EmpFirst, EmpLast, PayRate • In PayRate column, in Criteria Row enter: • >=7 • Save query as ‘Employees earning 7 dollars or more’ • Run Query

19. Query #4 • Switch to design view for the previous query • Add DeptName from Department Table • In DeptName column, criteria row enter: • =Accounting • Save query as ‘Employees earning 7 dollars or more and work in Accounting’ • Run Query

20. Query #5 • Switch to design view for previous query • In DeptName column, cut and paste the criteria to the “Or” row • Save query as ‘Employees earning 7 dollars or more or Work in Accounting • Run query

21. Query #6 – setting up a calculated field • Create Ribbon, Query Design • Choose ‘Employee Table’ • Add fields: EmpFirst, EmpLast • In the next available empty column, set up a calculated field (which will be called ‘Weekly Pay) – PayRate * 40 (method will be shown in lab) • Save query as ‘Employee Weekly Pay’ • Run Query

22. Creating Forms • Create Ribbon, More Forms, ‘Form wizard’ • Create form for ‘Employee’ table • Use all fields • Columnar layout • Save form as ‘Employee Update’

23. Create form using a query • Create Ribbon, Form Wizard • Use ‘Employee Pay and Department’ query • Fields: • DeptCode, DeptName, EmpFirst, EmpLast, PayRate • Tabular layout for subform • Form name: Department; subform: Employee Subform

24. Creating Reports • Create Ribbon, Report wizard • Create a report based on ‘Employee Pay and Department’ query • Report wizard • Fields: DeptCode, DeptName, EmpFirst, EmpLast, PayRate • Sort by last name • Stepped layout, Landscape orientation • Name report ‘Employee by Department’

25. Other possibilities • Use Access database to: • Generate labels • Mail Merge • With a document in MS Word • Mail merge can also be done from within MS Word