Data Analysis Using Excel and Access

Excel is highly favored by end users because of its rich set of features and ease of use. Access is a RDBMS (Relational Database Management System) with faster processing speed on massive data and better performance on managing multiple tables. It is also good for routine work as well. They can supplement to each other forming an ideal hybrid system for data analysts who are looking for speed and flexibility.

During the class, students can learn the advanced Excel techniques, such as data consolidation, advanced lookup and database functions. They will know how to import data into Access, and how to link up tables and to crunch data using Access’s queries. The result can then be exported back to Excel for the other processing such as plotting chart, creating pivot tables or constructing dashboards.

Example of linking Excel Tables under Access

Assuming you want to copy the information from customer table into the orders table if the company names in customer table is identical to customer name in orders table.

In Excel, you will write lookup formulas even though you know this is not an efficeint way because the number of formula will grow with the size of tables. Below is an alternative method done under Access. The steps or work will be the same disregarding the size of the tables.

1 : Import Excel tables into Access

Excel tables can be imported into Access by chooing External Data > Excel. Import wizard will then be displayed to guide you through the rest of the steps.

2 : Construct the relationship

Once the tables are in Access, the next step is to link up tables by dragging the field from one table to the other. The relationship is shown by a line joining the relating fields.

3 : Choose fields to be shown

Then you can start choosing fields to be displayed in the final table. All the work including filtering and sorting are done under query builder.

4 : Show the result

Finally, you click the run button to execute the query, result of the table will be displayed as shown in above.The query can be saved for later use if you want to repeat the same task in future.

What you’ll learn

Data lookup in Excel

  • Vlookup()
  • Hlookup()
  • Advanced lookup techniques

Consolidation with multiple Excel worksheets

  • Consolidating and combining multiple spreadsheets
  • Synchronizing the consolidated tables with the source data

Using Excel Database Functions

  • Dsum()
  • Daverage()
  • Dmax()
  • Dmin()
  • Dcount()

Methods of sharing data between Excel and Access

  • Importing and Linking Excel data
  • Export Access data to Excel tables

Setting Up Table with Existing Data

  • Attach Table
  • Import Data from external sources

Relational Database

  • Concept of relational database
  • Excel and the Flat-File Format
  • Primary keys and foreign keys
  • One to one / one to many / many to many relational
  • Constructing relationship among tables

Analysing data using Access queries

  • Query overview
  • Running a Query
  • Types of Queries
  • Creating Queries using Query Wizard
  • Creating multiple tables Queries
  • Creating parametric Queries
  • Other Options-specify criteria, sort records, calculate totals, set properties
  • Creating Aggregate Queries
  • Creating Action Queries ( make-table / delete / append records)
SCHEDULES
 
WEA4111 - 廣東話 14 Nov enrol
 
WEA4121 - 廣東話 23 Dec enrol
 
WEA5011 - 廣東話 21 Jan enrol
RELATING COURSES
  Access
  Access SQL
  Excel Dashboards and Reports
  Excel VBA X ChatGPT/GPT