EADVANCE TRAINING ON EXCEL, ACCESS AND MIS

Target Audience:
MS Office Specialist, MIS Personals, VBA Programmer, Accounts Personal, Project Managers, Developers, Technical/ Test Leads, Program Managers, Project Managers, Research Workers, Freelancers, Data analysis departments
At the end of training, participants will be able to know:
Various MIS technique with the help of Excel VBA Programming
Get an overview of different Analytical Process
Data handling with the help of Access
Reporting and Formatting by different angles
Macro generation and application design
Writing customize queries as per the need
Fundamentals of data handling, MIS, EXCEL and Access.
Course Contents:
EXCEL PART
Focus on real scenarios of Reporting / Data Management / Automation
Difference between basic and advance Excel
Excel Basics
Excel Settings
Advance Formulas
Name Manager
Absolute and Relative fields
Goal Seek / Scenario
Managing Lists
Cross Reference (Absolute & Relative)

ADVANCE EXCEL PART
Conditional formatting
User Settings for Excel Application Level
Data Management and Analysis
Reporting Techniques
Pivot Table, Macros
Concepts of VBA
Excel Data Handling with VBA
Use of SQL Queries
User Form, List Box, Combo Box, Buttons etc.
Application Design

Data Filtering / Data Formattting
Understanding Different Data Types
Using String / Text / Number / Long Data Types with examples.
Managing Date Time Format
Handling Long Date time Format
Data Correction
Identifying bad data
Conversion of different data types
Importing and accessing different file types in excel

ADVANCE EXCEL SECURITY
Protecting Excel File
Protecting worksheet, Workbook
Enable / Disable Range
Allow users to edit range
Protecting users to edit range
Field Controls using customised user access

FORM BASED
Database, Tables, Fields and Properties
Form Properties
Variables, Functions, Modules, Sub-routines
Conditional Validations
Using Other ActiveXTools like, Calendar, Watch, PDF...
Looping Constructs (While, Do While, For, For Each, If Else, If then else...)
Functional Parameters

DATA ACCESS PARTS
SQL Queries, Reports and Data Management
VBA Programming and Data Management
Forms, Reports & Application Design

Various MIS technique with the help of Excel VBA Programming
Get an overview of different Analytical Process
Differentiating Modules, Sub-Modules, Procedures
Data handling with the help of Access
ACCESS Forms and Reports
Writing Access Queries
Reporting and Formatting by different angles
Macro generation and application design
Writing customize queries as per the need
Fundamentals of data handling, MIS, EXCEL and Access.
Using of Developer Tools to Excel Worksheet
Using of ActiveX tools to Excel Worksheet
Using of Developer tools to VBA Programming
Enabling / Disabling ActiveX Tools to VBA

Using Charts / Graphs / Pivot Charts / Pivot table
Setting Data Source
Getting Charts / Graphs using Referential fields
Getting ACCESS Data
Getting Other Data

DATA SOURCE
Setting Data Source
Getting XML Data
Getting ACCESS Data
Getting Other Data

Retrieving Data from Single Table
>> Integration of Database
>> Connecting to a DBMS Using the SQL Procedure

Data Connectivity
>> Connecting to a excel Using the SQL Procedure
>> Connecting to a access Using the SQL Procedure
>> Connecting to a DBMS Using the DSN

ACCESS
>> Creating Tables / Queries / Forms
>> Insert / Update Statement
>> User Form / Field Updation
>> Multiple SQL Statements
>> The SQL Procedure
>> The Access Procedure
>> Requirements to Connect to a Database

ODBC
>> Understanding how to use the ODBC with Excel
>> Set up a data source for a data base driver
>> Defining the ODBC API
>> ODBC Terminology
>> Defining the Data Source Manager
>> ODBC Driver
>> Data Source