Microsoft Access



The International College of Economics and Finance

Lecturers: Anatoly A. Akinshin, Irina A. Bessonova

Teachers: Anatoly A. Akinshin, Seraphima N. Belousova, Irina A. Bessonova

Programming and Databases

Course Overview

The course is consists of two parts.

The first part is teaching at advanced level the specific features of spreadsheet software MS Excel for economic and financial tasks as well as with data and output analysis. This part also considers the Visual Basic for Applications (VBA) programming, which facilitates processing spreadsheet data and increases the productivity in MS Excel.

The second part is teaching databases. It deals with Microsoft Access, which is used to create and manage small and midsize computer-based databases on desktop computers and/or on network connected computers for the Microsoft Windows family of operating systems. This part also covered SQL to communicate through the server.

Goals & Aims

• To give students a holistic view of modern information technologies and their role in professional activities.

• To develop programming skills in using the object-oriented language aiming on expanding the functionality of MS Excel and MS Access.

• To show how integrate MS Access with SQL Server and other Business Intelligence (BI) applications.

• To teach how to build business applications that integrate local data, web-generated data, and data available from SQL Server and other sources

Distribution of hours for topics

|Topics |Total hours |Classes |Self-study |

| | |Lectures |Practice |Total class hours| |

|PART1 Advanced Excel |80 | |20 |22 |58 |

|Topic 1.Using built-in functions for data |14 | |2 |2 |12 |

|analysis | | | | | |

|Topic 2. Graphical analysis in MS Excel |10 | |2 |2 |8 |

|Topic 3. Working with large series of data |12 | |4 |4 |8 |

|Topic 4. MS Excel Add-ins for solving |8 | |2 |2 |6 |

|economic tasks | | | | | |

|Topic 5. Expanding the functionality of MS |36 | |10 |12 |24 |

|Excel by VBA | | | | | |

|PART2 Creating and Managing Computer-Based |106 | |40 |40 |66 |

|Databases | | | | | |

|Topic 6. Database concepts and terminology. |6 | |2 |2 |4 |

|Topic 7. Relational database management |6 | |2 |2 |4 |

|systems | | | | | |

|Topic 8. Access Basics. Creating Databases. |12 | |4 |4 |8 |

|Topic 9. Queries |12 | |4 |4 |8 |

|Topic 10. Forms and Reports |10 | |4 |4 |6 |

|Topic 11. Automating with Macros |12 | |4 |4 |8 |

|Topic 12. Using Other Applications with |8 | |4 |4 |4 |

|Access. Integrating Access with SharePoint | | | | | |

|and other business applications. | | | | | |

|Import/Export information. | | | | | |

|Topic 13. Advanced Tools in the Database. |8 | |4 |4 |4 |

|Customization | | | | | |

|Topic 14. Manipulate data using SQL, queries,|12 | |4 |4 |8 |

|and record sets with Data Access Objects | | | | | |

|(DAO) | | | | | |

|Topic 15. SQL Server and SQL Azure |8 | |4 |4 |4 |

|Topic 16. Microsoft BI overview: PowerPivot, |12 | |4 |4 |8 |

|PowerView, PowerMap | | | | | |

|Total |184 | |60 |60 |124 |

Main Reading

1. Walkenbach J. Excel 2010 Bible. - Wiley, 2010.

2. Walkenbach J. Excel 2010 Power Programming with VBA. - Wiley, 2010.

3. Jeff Conrad. John L. Viescas. Microsoft Office Access 2010 Inside Out. Published with the authorization of Microsoft Corporation by: O’Reilly Media Inc., 2010.

4. Andrew Couch. MS Access 2010. VBA Programming, 2011.

5.

6.

Course Outline

PART1

1. Using built-in functions for data analysis

Computational and financial Excel functions.

Conditional formatting for results highlighting.

2. Graphical Data Analysis in MS Excel

Charts, graphs, and their properties. Customizing different charts. Smoothing. Managing graphic objects in MS Office.

Graphical data analysis. Sparklines for visual representation of data.

Solving system of equations by graphing. Microsoft Equation Editor.

3. Working with large series of data.

Excel database. Creating a database from an Excel spreadsheet. Sorting, searching and editing. Filtering, AutoFilter. Creating custom filters using Excel Advanced Filter. Create two or more sets of Conditions. The use of computed criteria. Database functions.

Vertical and horizontal lookup functions.

Subtotalling the data. Merge spreadsheets using Data Consolidation.

Pivot Tables and Charts. Creating and manipulating pivot tables. Sorting and filtering subtotals. Calculations in pivot tables: additional calculations, calculated fields and objects.

Pivot charts.

Printing pivot table reports. Printing multi-page tables.

4. MS Excel Add-ins for solving economic tasks.

Microsoft Excel add-ins for statistical tasks (Analysis ToolPak) and optimization (Solver).

Analysis ToolPak for Microsoft Excel: finance, statistics and engineering functions.

Solver Add-In.

What-If analysis. Using Solver for solving systems of linear and non linear equations.

Goal Seek. Solving system of equations..

5. Expanding the functionality of MS Excel by VBA.

Type declaration of variables. Built-in data types. Scope and lifetime of variables. Variable initialization. Arrays. Custom data types.

Dialog boxes for data input/output. Data manipulation.

Loop and Conditional Statements used in VBA Excel programming.

Object variables. Object properties and methods. Object model. Object collections. Referencing objects

Manipulating forms: constructor mode, run mode.

Event handlers, global variables.

Forms initialization.

Text controls. Choosing controls.

Manipulating form controls.

Creating and manipulating lists.

PART2

6. Database concepts and terminology.

Types of Database Management Systems: Network, Hierarchical, Relational. Object-oriented Database Management Systems.

7. Relational database management systems

Relational operations. Relational algebra. Relational calculus. Normalization. Structured Query Language. Designing a Database.

8. MSAccess Basics

Starting Access. Creating a new file. Trusting a file. The Quick Access toolbar. Backstage view. Exploring ribbons. Using the Navigation pane. Getting help.

9. Creating a Database and Using the Tools

Planning and designing your database. Creating tables using Application Parts. Creating tables in Layout view with Quick Start. Creating and editing tables in Design view. Setting a primary key. Creating a lookup field. Creating multi-value fields. Using calculated fields.

Setting field properties. Setting input masks. Setting validation rules.

Creating relationships and enforcing referential integrity. Viewing subdatasheets.

Entering data into your tables. Formatting tables. Finding, sorting, and filtering records.

10. Queries

Using the Query Wizard. Creating a query in Design view with criteria. Creating wildcard queries, reusable parameter queries, yes/no queries, "and" and "or" queries, building calculation queries, statistical queries, crosstab queries. Using update and delete queries. Manipulate data using SQL, queries, and recordsets with Data Access Objects (DAO)

11. Forms and Reports

Creating data-entry forms. Using the Form Wizard. Modifying a form in Layout view. Using Design view. Setting tab stops. Adding buttons to a form. Navigation forms.

Introduction to reports. Using the Report Wizard. Formatting reports in Layout view. Identifying report structure in Design view. Adding group and sort capabilities to a report. Adding existing fields from other tables. Adding totals and subtotals to a report. Adding conditional formatting and data bars to a report. Creating multi-table reports. Creating mailing labels. Printing reports.

12. Automating with Macros

Access Object Model and Data Access Objects (DAO).

13. Using Other Applications with MS Access

Importing Excel and text data. Exporting data into Excel. Exporting to PDF. Exporting into a Word Mail Merge. Publishing to a web browser in HTML or XML. Sharing via email. Collecting data over email. Using Package and Sign. Publishing to SharePoint. Importing and exporting with SharePoint.

14. Advanced Tools in the Database. Customization

Compacting and repairing a database. Using data analysis tools. Encrypting a database and setting a password. Splitting a database. Customizing the ribbons. Setting Access options.

15. Microsoft SQL Server

Using SQL Server. SQL Server Management Studio. SQL Server Security. Authentication, Server and Database Roles, Ownership and User-Schema Separation, Permissions in SQL Server. Upsizing Access to SQL Server.

16. Microsoft BI overview: PowerPivot, PowerView, PowerMap.

Business intelligence capabilities in Excel and Excel Services. Power BI for Office 365. PowerPivot for Excel: Import data from multiple sources, Create relationships between data from different sources, Rename columns, and create calculated columns, Create PivotTables and PivotCharts. Add Slicers, Create a measure and KPI. Creating Reports in Power View. Creating Power Map reports.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download