Access and Excel 2010/13 & VBA 第一期 8:00 Sun 03/15 to 04/19, Total 24 Hours
Excel, Access, VBA skills are used in every companies, specially in large bank and insurance companies.
This course is opening to meet students demand.
After you find a high pay job as a database analyst, you want yourself to be armed with advanced Excel, Access and VBA skills as well.
The teachers are Excel Access and VBA Expert with many years Database Analyst experience in USA companies.
Course Outline
Week One: Introduction -Install and Import ( 4 Hours)
• Introduction
• Excel &Access 2010 Integration
• Excel: spreadsheet analysis & Reporting
• Vlookup ( Hookups and Match Index) Function in Excel
• Power of Access
• Open a Blank Access Database
• Table Datasheet view
• Different Types of Data
• Import an Excel Worksheet to Access
• Linking an Excel Worksheet to Access
• Lab1:Importing a Worksheet into a new Table
• Lab2: Interview Test- Dynamic Excel Report
Week Two: Query and Report ( 4 Hours)
• Access query
• Select one single query from one table
• Select query from multiple tables
• Query operation for calculation
• Define a Query for Report
• Export a Access report to Excel
• Select command buttons opens a dialog box
• Creating and Modifying Grouped Reports
• Customizing Reports with Formatting
• Page Layout Techniques
• Pivot table and chart in Excel
• Lab1 Join Two Tables
• Lab2 Use The Find Duplicates Query Wizard
• Lab3 Find UnMatched Rows
Week Three: Export and PV Table in Excel ( 4 Hours)
• From Excel link to Access
• Exporting the Data from Access
• Using the Get External Data Menu
• Using Microsoft Query
• From Excel link to SQL server, Oracle
• Pivot table and chart in Access and Excel
• Lab Get real time standard Excel reports With charts from Access-- Sales and Order Analysis
Week Four: Access Forms
Access Form
1. Create a single form (bound/unbound). (Lab 1)
• Create a form by from design.
• Create a form by wizard.
• Create a form by copy and paste
2. Set a combo box control with a list of values (Lab 2)and a calculated text box control on a form (Lab 3).
3. Create a form with an embedded sub-form (Lab 4).
• Manually
• By sub-form wizard
• By form wizard
4. Create a navigation form.
• Three ways to set up a navigation form. (Lab 5)
• Set the navigation from as default displayed form.
• Add more mechanism for exploring purpose (Lab 6).
Week Five: Macro and VBA ( 4 Hours)
• Use Macros in Excel and Access
• Lab1 Excel Macro for Absolute Reference
• Lab2 Recording Macros with Relative References
• Lab3: Excel Macro for DashBoard
• Lab4 Macro for Formatting
• Lab5 Macro for TopRep
• Lab6 Lab Dashboard
• VBA Code: Introduction -Basic for Applications (VBA)
• What is VBA Procedure
• Using VBA to Move Data Between Excel and Access
• Module is an object that houses code for the workbook.
• Lab7 VBA-Get Access Data into Excel
• Lab8 VBA_GetData_from_Excel_sheet.
• Lab9 VBA-Get Data_With_Databaswe SQL Server
Week Six: Automation Report , PowerPivot and PowerView
• Learn how to create your own VBA program for Search
• Lab User friendly report with Automation chart
• Lab My Search Macro step by step
• Excel Add in feature
• Data Modeling : Excel 2013
• PowerPivot 2013
• Powerview and Dynamic Dashboard
Prerequsition: None.
Price: Will publish soon.