Access with Excel

Access with Excel course

Access with ExcelA MS Access course with a focus on how you can interact and use Access with Excel

A comprehensive Microsoft Access course covering the basics of Access and how to interact with the best features of Excel. The course focuses on using the best features of Access with Excel products to provide a structured, safe solution to your business dilemmas.

Four new skills you will take home from the course:

  • Understanding how to design an efficient and effective database
  • How to create a MS Access database with Tables, Queries, Forms, Reports, Macros, and Modules
  • Establish relationships between the tables and queries
  • Interacting Access with Excel directly to benefit from the best features of both programs

Course Outline

INTRODUCTION TO MICROSOFT ACCESS

  • Flat-File Database
  • Relational Database
  • Fields and Records

INITIALISING A NEW DATABASE

  • Create a New Blank Database

MICROSOFT ACCESS OBJECTS

  • Tables
  • Queries
  • Forms
  • Reports
  • Macros
  • Modules

FINALISING THE TABLES

  • Salesperson Table
  • Customer Table
  • Sales Table
  • Populating the [Salesperson] Table from Excel
  • Populating the [Customers] Table from Excel
  • Completing the [Salesdata] Table

ESTABLISHING RELATIONSHIPS

  • The Relationship Window
  • Creating a Join

CREATING A FORM TO CAPTURE SALES DATA

  • Using the Form Wizard
  • Changing the Form Design

POPULATING THE SALES DATA TABLE FROM EXISTING EXCEL DATA

  • View of Access Sales Data Table
  • View of Excel Data
  • A Query with a Calculated Field
  • Preparing The Excel Workbook To Receive Data

USING LINKED EXCEL TABLES

  • Creating a Link
  • Creating an Append Query

USING EXCEL PIVOT TABLES FROM ACCESS DATA

  • Excel Pivot Table with Access Source Data

USING ACCESS PIVOT TABLES AND PIVOT CHARTS

  • Pivot Table Forms
  • Pivot Chart Forms

WORKING WITH QUERIES

  • Filtering Data
  • Working With Criteria
  • Using Parameters
  • Using a Form to Provide Criteria Input

CREATING FORMS WITH SUBFORMS

  • Manually Creating the Customers/Sales Data Form and Subform

CROSSTAB QUERIES

  • The Crosstab Row
  • The Total Row
  • Exploring Total Queries

REPORTS

  • Using the report wizard
  • Editing an existing report

EMBEDDING A MACRO IN A FORM

THE SWITCHBOARD MANAGER

  • Create A Switchboard
  • Add items to a switchboard
  • Access your switchboard
  • Edit items in a switchboard
  • Delete a Switchboard or Switchboard Item
  • Display the Main Switchboard on Startup

Feedback & Who Should Attend

  • Not only did I learn a lot about Access on this course, but I also gained quite a few useful Excel skills. The fact that Jonny is an accountant meant that he understood the daily challenges with data in a finance role. I would recommend this course to anybody that wants to learn how to combine the best of Access and Excel to use data – Quintin L- Hannover Re
  • Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course. Anton F- ABSA Capital

Typical Attendees

Anyone who is, or needs to, work with MS Access and allow for interaction with Excel. This includes:

  • Business owners, managers, financial managers, directors, bookkeepers, marketers, HR managers and anyone who deals with databases in their work related tasks.
  • Sales representatives, sales managers, sales executives or team leaders who need to learn how to manage large databases
  • All persons using, or wanting to use Access with Excel spreadsheets
  • All persons involved in the construction and use of databases, including budgets, projections, evaluations, cash flows, projects, etc.

Pre Requisite (s)

Access and Excel knowledge required:

Access Skills Required

  • None or minimal skills required as the course starts from scratch with regards MS Access

Basic Excel skills

  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  • Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  • Formatting