好用的手机公司, 新年大促销: 3 month FREE! $15/Month 5GB data and text

Author Topic: Access 2013 and Excel course content  (Read 9847 times)

USA LaoTu Support Team

  • Administrator
  • Hero Member
  • *****
  • Posts: 4803
  • Karma: +35/-11
Access 2013 and Excel course content
« on: November 30, 2013, 11:11:45 PM »
USA LaoTu 课程设计以找工作为中心目标, 老师们把工作中最常用的技能用最直接的方法展示给学生.
2009 年, Bobcat 同学只学了Access2007 一门课, 就成功转行找到大银行的好工作.详情请看视频:242th 美国老土 专业求职辅导周会, 听Bobcat 同学给你分享经验.

After gain skills from our Access Excel VBA 2013 class, student can qualify following job in large companies.

Access developer :  VBA

Developing  use VBA Visual Basic for Applications in both Access and Excel.
This is a project within a large company. This group is tasked with looking at all new product that is to be manufactured and running cost analysis.

1. Taking existing financial data (capital expense and labor cost) and developing a database in Access to better extract information and make it more manageable for group members.

2. Gathering data from engineering manager and supervisors on manufacturing process and cost drivers and building it in to database.

3. Developing easy to use formular and database for other managers/supervidor to run thrie numbers.

4. Working with our Sr. manufacturing database engineer to develop new database for other smaller projects.

  • Use Interface forms design in Access
  • Table design with referencial integrity
  • Report design in both Access and Excel
  • Chart design in Excel
  • Export SQL knowledge
  • Queries using query design grid
  • Active X (formally Object Linking ans Embedding...used to control Excel through code in an Access module).
  • VBA (Not Access macros...there is a difference).
  • DAO (used for creating record sets and tables through code)

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

Excel and Access skills are used in every company. There are many job openings require these skills.

You will get two Excel e-books and two Access e-books after register this class.

------ Access 2007 class outline: ------------
Will teach Microsoft Excel & Access 2007 database.
Chapter 0: Install Access 2007 into your PC
Chapter 1: Getting Excel Data into Access
Differences Between Access and Excel
The Access Table
The Table in the Design View
Bringing Your Excel Data into Access
Importing a Worksheet into a New Table
Linking an Excel Worksheet to Access
Appending an Excel Worksheet to an Existing Table

Chapter 2: Analyzing Excel Data with Access Queries
Introduction to Access Queries
Sorting Query Results
Filtering Query Results
Querying Multiple Tables
Understanding the Concept of Relational Databases
Creating a Query that Joins Two Tables
Using Aggregate Queries
Group By, Sum, Avg, Count, Max, Min
Using Calculations in Your Analysis
Leveraging Access Query Wizards to Solve Common
Excel Problems
The Find Duplicates Query Wizard
The Find UnMatched Query Wizard
Crosstab Queries

Chapter 3: Sprucing Up Excel Data with Access Reports
A Closer Look at the Access Report
Creating Your First Report
Viewing Your Report
Report View
Layout View
Design View
Page Footer
Report Footer
Creating and Modifying Grouped Reports
Grouping
Sorting and Totaling
Customizing Reports with Formatting
Page Layout Techniques
Solving Page Break Issues
Multi-Column Report Layout Example
Formatting Techniques
Formatting to Make Reports More Readable
Formatting to Highlight Specific Results

Chapter 4: Using PivotTables and PivotCharts in Access
Working with PivotTables in Access
The Totals and Detail Area
The Row Area
The Column Area
The Filter Area
Creating a Basic PivotTable
Creating an Advanced PivotTable with Details
Saving Your PivotTable
PivotTable Options
Expanding and Collapsing Fields
Changing Field Captions
Sorting Data
Grouping Data
Using Date Groupings
Filtering for Top and Bottom Records
Adding a Calculated Total
Working with PivotCharts in Access
The Data Area
The Series Area
The Category Area
The Filter Area

Chapter 5: Getting Access Data into Excel 
Exporting the Data from Access
Using the Get External Data Menu
Using the Microsoft Query Wizard
Going Beyond the Wizard in Microsoft Query

Chapter 6: Leveraging Macros in Excel and Access
What Is a Macro?
Why Use a Macro?
Creating Macros in Microsoft Excel
Creating Macros in Microsoft Access
Introducing Excel Macros
Using the Macro Recorder
The Macro Recorder User Interface
Recording Macros with Absolute References
Recording Macros with Relative References
Excel Macro Examples
Macros in Microsoft Access
Creating your First Access Macro
The Macro Design Template
Common Actions in Access Macros
Access Macro Example

Chapter 7: VBA Fundamentals
What is VBA?
The Visual Basic Editor or VBE
Variables
Procedures and Functions
VBA Coding Funfamentals
Examples of Using VBA

Chapter 8: Using VBA to Move Data Between Excel and Access
Understanding ADO Fundamentals
The Connection String
Declaring a Recordset
Return Read Only Data from a Table or Query
Return Updateable Data from a Table or Query
Writing Your First ADO Procedure
Referencing the ADO Object Library
Writing the Code
Using the Code
Understanding SQL Fundamentals
Basic SQL Syntax to Select Data
Writing Your First ADO/SQL Data Extract
Using Criteria in Your SQL Statements
Common Scenarios Where VBA Can Help
Query Data from an Excel Workbook
Append Records to an Existing Excel Table
Append Excel Records to an Existing Access Table
Querying Text Files