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

 BUSY

General Features

• Completely Codeless
• Multi-Company Accounting
• Context-Sensitive On-Line Help
• Flexible Voucher Numbering
• Multiple Voucher Series for each Voucher Type
• Voucher entry while viewing reports and Vice-versa
• User-definable Optional Fields in Masters and Vouchers
• Unlimited level of Grouping of Accounts, Items, Material Centers and Cost Centers
• Option for adding Images & Notes in Account & Item Masters
• Draft Mode Printing for every Report and Document
• Voucher Cancellation
• Powerful Query System
• SQL Query Executor
• Print Preview
• Landscape Mode Printing
• Bird's Eye View of Account
• ENTERPRISE FEATURES
• Voucher / Master Approval
• Message centre
•  

Financial Accounting

• All books of Accounts & Final Results
• Balance Sheet with various Schedules
• Columnar Account Registers (Configurable)
• Daily / Monthly Summaries of Accounts, Account Groups and Cost Centers
• Bill-wise Outstanding with Ageing Analysis
• Payment Reminder Letters (Configurable)
• Statement of Account (Configurable)
• Handling of Post-dated Cheques
• Broker-wise Reporting
• Royalty Calculation
• Interest Calculation (Multiple Slabs based on Amt. & Days)
• Bank Interest Calculation
• Bank Re-conciliation
• Depreciation Chart (As per Companies Act / IT Act)
• Multi-Currency Accounting
• Cost Centre-wise Reporting
•  

Multi-Location Inventory

• All books of Inventory
• Stock Status (Multi-location)
• Sales / Purchase Challans
• Columnar Inventory Registers (Configurable)
• Stock Valuation on Multiple Methods
• Separate Stock Valuation Method for each Item
• Item Serial No. Tracking
• Stock Ageing Analysis
• Allocation of Expenses to Items
• Daily / Monthly Summaries of Items and Item Groups
• Automatic Round-off in Vouchers
• Primary and Alternate Unit for each Item
• Batch-wise Inventory with Expiry Date
• Provision for Free Qty in Inventory Vouchers
• Provision for entering Sales / Purchase Return in Sales / Purchase Vouchers
• Production / Un-assemble
• BOM / Production Planning
• Critical Level Reporting
• Multiple Price-list for Items
• Multiple Price Structures (Party / Qty / Category-wise)
• Provision to update Item Prices on various Criterion
•  

Sales / Purchase Order Processing

• Pending Sales Orders (Party / Item-wise)
• Pending Purchase Orders (Party / Item-wise)
• Order Cancellation
• Invoice / Challan Generation from Orders
• USER-CONFIGURABLE INVOICING
•  

Fully User-Configurable

• Use Plain / Pre-printed Stationery
• Option for Fonts, Colors& Logos
• Option to Print Outer Box, Horizontal & Vertical Lines
• Multiple-taxes in Single Invoice
• Tax Inclusive / MRP Billing
• Point-of-Sale (POS) Billing
• Mfg. Excise Invoicing
• Trading Excise Invoicing
•  

User-Configurable Documents

• Accounting Vouchers
• Purchase Invoice
• Sales Return / Purchase Return Invoice
• Receipt / Payment Advice
• Debit / Credit Note
• Sales / Purchase Order
• Delivery Note / Goods Receipt Note
• Stock Transfer Memo
•  

Sales Tax Reports

• Sales Tax Forms Receivables / Issuable
• Sales Tax Forms Reminder Letters
• Forms Received / Issued Register
• Sales Tax / Purchase Tax Summary
• Sales Tax Surcharge
• Sales Tax Registers
•  

GST Reports

• GST Computation / Summary
• GST Invoicing (Tax / Retail)
• GST Returns (State-wise)
• GST Registers (State-wise)
• Columnar GST Registers (Configurable)
• Party-wise / Item-wise GST Summary
• Party-wise / Item-wise GST Summary
• Sales Re-conciliation as per Accounts and GST
• GST R1
• GST R2
• GST 3B
•  

Service Tax

• Input Service Tax Register
• Output Service Tax Register
• Service Tax Summary
• ST-3 (Service Tax Return)
•  

TDS Reports

• TDS Calculation
• TDS Warning Alarms
• TDS Challans
• TDS Certificate (Form 16-A)
• TDS Return (Form 26-Q)
• Annexures to Form 26-Q
• e-TDS with TDS 27-A
• TDS Audit Report
• TDS Deduction Details Report
•  

Fringe Benefit Tax

• Mark Expenses as FBT
• Expense-wise Taxable Value
• Option to charge FBT on Account Groups
• Fringe Benefit Tax Report
•  

MIS Reports

• Cash Flow / Funds Flow
• Budgets / Targets / Credit Limits
• Ratio Analysis
• Sales / Purchase Analysis on various combinations
• Issue / Receipt Analysis
• Consumption Analysis
• Data Check List
• Profitability Reports (Item / Bill / Party / Batch / MC wise)
• Multi-Company Final Results / Reports
•  

Utilities

• Data Freezing
• Pop-up Calculator
• Notes / Task Manager
• User-definable Access Rights

 TALLY

Introduction of Tally

• Company information
• Create a new company
• Current period, current date
• Company creation (accounts maintain, financial year, security control, tally vault)
• Accounts info
• Group ledger
• Ledger

Book Keeping

• Entries with dr. And cr.
• Using cash account in journal
• Debit & Credit note
• Voucher types
• Select company
• Shut company
• Alter company
• Delete company
• Group company
• Change tally vault

Financial Reports & Consolidate Ledger Accounts

• Balance sheet
• Profit & loss a/c
• Ratio analysis

Fund management with list of account

• Trial balance
• Accounts book
• Statement of account
• Cash/fund flow
• Day book
• List of account
•  

Mode of Invoice Punching/billing

• Bill wise detail with (new reference, against ref, advance, on account)
• Display (statement of a/c
• Bills payable, Receivables
• outstanding ledger, group)

Inventory Management

• Stock group
• Stock item
• Unit of measure
• Voucher entries –goods in, goods out
• Alternate unit and standard rate
• Opening balance, stock summary

Purchase & Sales Order Processing

• Purchase and sale order
• Create purchase order
• Alter purchase order
• Delete purchase order
• Create sale order
• Alter sale order
• Delete sale order
• How can we do reject in reject our entry & use option
• Delivery note & Receipt note
• Give challan number in item
• How can we do reject in reject our entry & use option
• Delivery note & Receipt note
• Give challan number in item
•  

Internal Report of Finance

• Display (inventory books, day book, list of account)
• Accounts with inventory (balance sheet. P&l a/c, stock summary, ratio display)

Controlling (Cost Center & Cost Categories)

• Cost centre
• Creation of cost centre
• Alteration of cost centre
• Cost categories
• Create cost categories
• Alter cost categories

Multi godown creation

• Godown/location
• Create godown
• Alter godown
• Removing companies
• Copy company
•  

Tax Deducted at Source (TDS)

• Tds
• Transaction of tds
• Ledger for tds
• Payment voucher
• Preparing tds challan
• Deposit of tds to govt
•  

Tax Collected at Source (TCS)

• Understanding TCS
• Creating TCS Masters & Practical Examples
• TCS Payment, Reports, & Forms
•  

Goods and Services Tax (GST)

• GST
• Ledger for GST
• Transaction of GST
• Adjustment of GST
• Payment of GST

Budgets and Controls in Tally

• Budget
• Types of budget
•  

Scenarios and Optional Vouchers

• Creation of scenario
• Alter or display scenario information
•  

Export, Backup and Splitting company data

• Splitting company data
• Steps of splitting
• From tally to excel
• Importing into MS-excel
• Removing companies
• Copy companies

Printing Reports

• Printout of balance sheet, p&l a/c, ledger
• Printing Different Reports
• Sales Invoice
•  

Multi-Currency

• Currency creation (How to create
• New currency & how to set the price Lists.

Point of Sales

• POS (Point of Sale)

Payroll Accounting Functions

• Payroll (payroll info)
• Employees and salary detail
• Maintain attendance register of employees
• Employees group
• Create attendance type
• Unit of leave (payroll)

Manufacturing Vouchers

• BOM (bill of material)
• Create Item of BOM
• How to create voucher after
• Ledger of BOM
• Transaction of BOM
• Alter of BOM
• After ledger for excise duty

Batch wise Details

• Batchwise Inventory
•  

Interest Calculations

• Interest Calculations
  

Credit Limit Price List

• Creation of Price level
• Create of Price group
• Maintain Price list on items
•  

Voucher Types and Class

• Voucher Type
• Voucher Class-Sales
• Inclusive Tax Class
• Customer pay their payment
• How to create voucher with discount & freight charg

 SAP FICO

Enterprise Structure & Financial Accounting Global settings

• Overview of Organizational Elements in Accounting
• Organizational Units
• Define and Assign Organizational Units for Finance: Ex: – Country, Regions, Company, Company Codes, Business Areas, Functional Areas, etc.,
• Variant Principle
• Fiscal Year and Posting Periods
• Field Status Variants
• Document types and Number Ranges
• Posting Keys
• Define Tolerance for GL Accounts and Employees
• Global Parameters

General Ledger Accounting

• Master Data Overview
• Chart of Accounts
• Types of Chart of Accounts
• Define and Assign Chart of Accounts,
• Define Account Groups and Screen Layout for GL Accounts,
• Define Retained Earnings Account,
• Creations of GL Accounts Master Records,
• Postings, Display GL Account Balances and Document.

Accounting Documents

• Parking Document
• Holding Document
• Reference Document
• Recurring Document
• Sample Document
• Account Assignment Model
• Fast Data Entry

Reversals

• Individual Reversals
• Mass Reversals
• Reversals of Reversals
• Cleared item Reversals

Bank Accounting

• Define House Banks with Bank Accounts
• Creation of check number ranges for check lot
• Define void reason codes
• Issue of a check
• Manual payment
• Cancellation of issue check with reason codes
• Check register

Accounts Payables

• Overview on Procurement Process
• Overview on Vendor Master Data
• Define Account Groups with Screen Layout
• Define Number Ranges for Vendor Accounts
• Assign Number Ranges to Vendor Account Groups
• Reconciliation Accounts
• GL Account Creation
• Creation of Vendor Master Records
• Vendor Document Types and Number Ranges
• Posting Keys
• Define Payment Terms
• Define Tolerance for Vendors/Customers
• Posting of Purchase Invoices
• Display Vendor Line Items
• Outgoing Payments
• Vendor Clearings
• Credit Memo Postings
• Special GL Transactions (Down Payments Made)
• Posting Down Payments
• Clearing of Down Payments against Invoices
• Reason Codes for under and over Payment
• Automatic Payment Program

Accounts Receivables

• Overview on Sales Process
• Complete Customer Master Data
• Define Account Groups with the Screen Layouts
• Create Number Ranges to Customer Accounts
• Assign Number Ranges to Customer Account Groups
• Reconciliation Accounts
• Create Customer Master Records
• Document Types and Number Ranges
• Posting Keys
• Payment Terms
• Posting of Sales Invoices
• Incoming Receipt with Clearings
• Display Customer Line Items
• Display GL Account Balances
• Special GL Transactions (Down Payments Received)
• Posting of Down Payments (Advance Receipt)
• Clearing of Down Payments
• Customer Credit Memo

Dunning Procedure

• Define dunning procedure
• Define dunning level
• dunning charges
• dunning text
• Dunning Procedure with Customer

Management of Account

• Open Item Management
• Types of Payments
• Standard Payment
• Partial Payment
• Residual Payment

Payment with Term

• Residual payment
• Payment terms

Advance Payment

• Define alternative reconciliation a/c
• Vendor request
• Down payment
• Down payment clearing

Cash Journal

• Set up cash journal
• Define number range intervals for cash journal document
• Create change, delete, business transaction
• Set up print parameter for cash journal
• Cash journal posting

Closing Operations

• GL Report
• AR Report
• AP Reports
• AM Reports

Integration

• Integration of MM to FI
• Integration of SD to FI
• Integration of HR to FI

Tax on Sales/ Purchase (GST)

• Check calculation procedure
• Check & change setting for tax processing
• Assign country to calculation procedure

Maintain Tax Codes

• Define tax codes for sales & purchases
• Define tax accounts
• Assign Tax Codes For Non-Taxable Transaction

Invoice Posting with Tax

Extending Withholding Tax (TDS)

• Check withholding tax countries
• Define withholding tax keys
• Define reasons for exemption
• Define business places
• Assign factory calendar to business place

Extending Withholding Tax (TDS)

• Define section codes
• Define withholding tax type for invoice posting
• Define withholding tax type for payment posting
• Define withholding tax codes
• Check recipient types

Tax Due Dates

• Maintain tax due dates
• Assign withholding tax types to company code
• Activate extended withholding tax
• Define accounts for withholding tax to be paid over
• Maintain number groups
• Assign number range to number group
• Maintain number ranges
• Maintain company code setting

Asset Accounting

• G/L creation of Asset Accounting
• Copy reference chart of deprecation
• Assign chart of deprecation to company code
• Specify account determination
• Create Screen Layout Rules
• Define number ranges interval
• Define Asset Classes
• Assign G/L accounts
• Assign input tax indicator for non – taxable acquisition
• Determine Depreciation area in the asset classes
• Specify rounding areas in the asset classes
• Specify rounding of net book value and (or dep.)
• Define screen layout for asset master data
• Define screen layout for asset depreciation areas
• Define base method
• Define declining balance method
• Define multi-level method
• Maintain period control methods
• Maintain depreciation key, Define or assign forms
• Create asset, asset sub number
• External acquisition – asset purchase
• Depreciation run, Asset retirement by scrapping
• Asset explorer report, Retirement with customer
• Asset sale without customer

Currencies

• Maintain Exchange Rates Maintain Table
• Define Translation Ratios for Currency
• Define Accounts for Exchange Rate Differences
• Posting with Foreign Currency
• Define Methods for Foreign Currency Valuation
• Prepare Automatic Posting for foreign currency valuation
• Revaluation of foreign currency balances

CONTROLLING

General Controlling

• Maintain controlling area
• Activate components for controlling area
• Assignment of company code to controlling area
• Maintain number ranges for controlling area documents
• Maintain versions and setting for fiscal year

Cost Element Accounting 

• Introduction to Cost Element Accounting
• Primary and Secondary Cost Elements
• Define Cost Element Categories
• Manual & Automatic Creation of Primary and Secondary Cost Elements
• Define Cost Element Standard Hierarchy and  Groups

Cost Centre Accounting

• Introduction to cost centers
• Define Standard hierarchy for cost centers
• Define cost center categories
• Master data maintenance
• Cost centre groups
• Set planner profile for cost centre
• Create activity types
• Maintain statistical key figures
• Post transaction to cost centers
• Generate cost center reports
• Plan values and quantities for activity prices
• Calculate activity prices
• Periodical reposting of cost
• Create and execute assessment cycle
• Create and execute distribution cycles
• Activity allocations
• Settings for automatic posting

Cost Centre Accounting

• Introduction to cost centers
• Define Standard hierarchy for cost centers
• Define cost center categories
• Master data maintenance, Cost centre groups
• Set planner profile for cost centre
• Create activity types
• Maintain statistical key figures
• Post transaction to cost centers
• Generate cost center reports
• Plan values and quantities for activity prices
• Calculate activity prices
• Periodical reposting of cost
• Create and execute assessment cycle
• Create and execute distribution cycles
• Activity allocation, Settings for automatic posting

Profit Centre Accounting

• Introduction to Profit centre accounting
• Maintain Standard Hierarchy
• Create Dummy Profit Centre
• Maintain Profit Centre Groups
• Creation of Profit Centre
• Assignment of Profit Centers in cost Center Master Data
• Maintain Automatic Account Assignments for Revenue Elements
• Profit Center for Balance Sheet and Profit and Loss
• Planning and Postings
• Display Profit Center Reports

Profitability Analysis

• Introduction to profitability analysis
• Maintain operating concern
• Define profitability segment
• Maintain characteristics values
• Define derivation rules
• Setting for data flow from SD
• Setting for data flow from FI and MM
• Display reports in COPA
• Mapping of SD/MM Conditions to CO-PA value fields