CERTIFICATE COURSE IN M.I.S

 Prof. MS EXCEL

  Prof. MS Access (RDBMS)

Understanding Databases 

• Starting and Opening an Existing Database
• Moving Around in Access
• Understanding Datasheet View & Design View
• Using the Mouse Pointer to Navigate
• Using the Keyboard to Navigate
•  

Creating Tables

• Creating a Database
• Creating a Table Using the Wizard
• Creating and Modifying a Table
• Adding Fields to Tables
• Adding and Editing Records
• Printing Tables
• Moving and Deleting Fields
• Deleting Records
•  

Working with Tables

• Formatting a Table
• Modifying Field Properties
• Sorting Records in a Table
• Finding Records in a Table
• Using Filters with a Table
• Establishing Relationships Between Tables
• Creating Subdatasheets
• Importing Records From an External Source
•  

Designing a Form

• Creating a Form Using Auto Form
• Creating a Form Using the Form Wizard
• Adding Controls to a Form
• Modifying Control Properties
• Resizing and Moving Controls
• Entering Records into a Form
• Creating Calculated Controls
•  

Designing a Report

• Creating a Report Using Auto Report
• Creating a Report Using Report Wizard
• Adding a Control to a Report
• Formatting a Report
• Resizing and Moving Controls
• Creating Calculated Controls
•  

Creating and Using Queries

• Creating and Running a Query
• Specifying Criteria in a Query
• Using Comparison Operators
• Creating a Calculated Field
• Creating a Multiple-Table Query
• Printing a Query

Automating Tasks

• Creating an Auto Keys Macro
• Using Controls to Run a Macro
• Assigning a Macro to an Event
• Assigning a Macro to a Condition
• Testing and Debugging a Macro

 SQL

SQL ─ Overview

• What is SQL?
• SQL Process
• SQL Commands
•  

SQL – Operators

• What is an Operator in SQL?
• SQL Arithmetic Operators
• Arithmetic Operators – Examples
• SQL Comparison Operators
• Operators – Examples
• SQL Logical Operators
• Logical Operators – Examples

SQL – Expressions

Boolean Expressions

Numeric Expressions

Date Expressions

SQL – CREATE Database

• SQL ─ DROP or DELETE Database
• SQL ─ SELECT Database, USE Statement
• SQL ─ CREATE Table
• SQL – Creating a Table from an Existing Table
• SQL ─ DROP or DELETE Table
• SQL ─ INSERT Query
• SQL ─ SELECT Query
• SQL ─ WHERE Clause
• SQL ─ AND & OR Conjunctive Operators
• The AND Operator
• The OR Operator
• SQL ─ UPDATE Query
• SQL ─ DELETE Query.
• SQL ─ LIKE Clause.
• SQL ─ TOP, LIMIT or ROWNUM Clause
• SQL ─ ORDER BY Clause
• SQL ─ Group By
• SQL ─ Distinct Keyword
• SQL ─ SORTING Results
•  

SQL ─ Constraints

• SQL ─ Constraints
• SQL – NOT NULL Constraint
• SQL – DEFAULT Constraint
• SQL – UNIQUE Constraint
• SQL ─ Primary Key
• SQL ─ Foreign Key
• SQL ─ CHECK Constraint
• SQL ─ INDEX Constraint
• Dropping Constraints
• Integrity Constraints
•  

SQL ─ Using Joins

• JOIN
• SQL ─ LEFT JOIN
• SQL – RIGHT JOIN
• SQL ─ FULL JOIN
• SQL ─ SELF JOIN
• SQL ─ CARTESIAN or CROSS JOIN
•  

SQL ─ UNIONS CLAUSE

• The UNION ALL Clause
• SQL ─ INTERSECT Clause
• SQL ─ EXCEPT Clause
•  

SQL ─ NULL Values

SQL ─ Alias Syntax

SQL – Indexes

• The CREATE INDEX Command
• The DROP INDEX Command
• SQL – INDEX Constraint
•  

SQL ─ ALTER TABLE Command

SQL – TRUNCATE TABLE Command

SQL ─ Using Views

• Creating Views
• The WITH CHECK OPTIONSQL ─ Having Clause
•  

SQL – Transactions

• Properties of Transactions
• Transactional Control Commands
•  

SQL ─ Wildcard Operators

SQL ─ Date Functions

SQL ─ Temporary Tables

• What are Temporary Tables?
• Dropping Temporary Tables
•  

SQL – Clone Tables

SQL – Sub Queries

• Subqueries with the SELECT Statement
• Subqueries with the INSERT Statement
• Subqueries with the UPDATE Statement
• Subqueries with the DELETE Statement
•  

SQL – Using Sequences

• Using AUTO_INCREMENT column
• Obtain AUTO_INCREMENT Values

 VBA MACROS

What Is Vba & How Vba Works With Excel

• Quick Review of Macros
• Introducing the Visual Basic Editor
• Uses of record Macros
• Understanding and creating modules
•  

Programming Concepts

• VBA Sub and Function Procedures
• How to create a message box
• Write a Program to update and retrieve information using Input Box
• Understanding and using Select Case statement
• How do I define a variables and Rules for defining a Variables Name and Type
• Creating And using Variables
• Working with range Objects
• How to save and Protect Modules
•  

Decision Makers

• If……Then……Else
• If……Then……Else If……If
•  

Other Kinds of Loops

• Working with Do While u. Loop Procedure
• Do…… Until Loop and Do…… Loop Until
• Do……While loop and Do…… Loop While
• For each…… Next
• For……Next
•  

Workbook Objects Create or Add Single and Multiple Workbooks

• Workbook Save and Save AS
• Open Single and Multiple Workbooks
• Close Specify and Multiple Workbooks
• Activate From one workbook to another Workbook
• Open Workbook from Specific Path
• Get Workbook Name and Paths
• Hide and Unhide for Single and Multiple Workbooks
• Protect and Unprotect Worksheets
•  

Worksheet Objects insert a single and Multiple Worksheets

• Delete Specific and multiple worksheets
• Get Count of Worksheets
• Select a Specific and all Worksheets
• Get All Worksheets Name
• Hide and Unhide For Single and Multiple Worksheets
• Rename for Single and Multiple Worksheets
• Protect and unprotect worksheets
• Sort and Move worksheets
• Calculate entire worksheet
• Using VBA and worksheet Function

Cell objects insert Single and multiple Row, Column and Cells

• Delete Single and Multiple rows, Column and Cells
• Get Range or Address of Cell and Selection
• Navigate from one Cell to another Cell
• Select specific Range, Cell, Rows and Column
• Types of Selection and Offset method
• Insert Function In cell
•  

Reading and Writing Arrays

• Defining Arrays
• Arrays as Outputs
• Arrays as inputs
• The Arrays as a Collection
• The Array as an Array
•  

Form Controls and User Forms (Create and Design an user Form)

• Working with User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
• How to Create Dynamic Dashboard on User Form with Different Controls
• How to Link Various User Form With Each Other To Create A Complete Interface Between User and System
•  

VBA Programming Functions

• Create a Sum Functions
• Create Multiply Function
• Create Count Function
• Extract Text & Number
• Proper Function
• Vlookup Function
• Square root Function
• Public or Private function
•  

Excel VBA Power Programming for VBA Macros

• Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files
• How to Analyze Data On multi Worksheets and Build Summary sheets
• how to Access the Windows File and Folder System to Open and Close Workbooks
• How to protect your code Against Errors
• How to use Excel And VBA to Create Basic Dash Boards
• How to create Your own custom Business Worksheet Function in VBA
• How to create Basic Report Generation Tools Using Excel Vba, Microsoft Word and PowerPoint
• How to use the Excel Visual Basic Macro record Excel Tasks in VBA and then Interpret the code
•  

Connection between Excel VBA & other platforms

• How to Establish Connection Between VBA And Power Presentation to Create Power Point Through VBA
• How to Establish Connection Between Excel Vba And Outlooks Through Vba
• How to Establish Connection Between Excel Vba And MS Word Through Vba