Reporting Automation using excel, VBA and MS Access

I worked on creating a Reporting Automation using MS Excel, VBA and MS Access...Used Access to create data model. Created Tables/views using SQL queries and then VBA to return required data from Access to Excel based on criteria put in excel sheet...

Creating a reporting automation system using Excel, VBA, and MS Access. The system involves:

Creating a data model in MS Access using SQL queries.

Using VBA to return required data based on criteria specified in an Excel sheet.

Calculating and comparing specific player statistics (e.g., Driving Distance) against the average of all players in a round of a tournament.

The primary business problem is to automate the reporting process for calculating and comparing player performance metrics in golf tournaments. This involves:

Identifying individual player statistics for each round.

Calculating the average statistics for all players in the same round.

Comparing individual player statistics against the average to determine performance gains or losses.

Example: Grant Forrest in Rd1 of the Hero Open drove the ball an average distance of 306.0 yards (cell DB2).

Example: The average driving distance of all players in Rd1 of the Hero Open was 302.39 yards (AVERAGE(DB2:DB144)).

Formula: Player’s average driving distance - Average driving distance of all players.

Example: Grant Forrest’s driving distance gained = 306 - 302.39 = +3.61 yards.

Calculate the driving distance gained for each round and aggregate the results for the last 1, 2, 3, or 4 rounds as needed.

Data Model in MS Access:

Create tables for storing player statistics and tournament data.

Write SQL queries to organize and retrieve data.

VBA in Excel:

Write VBA scripts to connect to the Access database and fetch data based on user input.

Populate Excel sheets with the retrieved data.

Excel Formulas:

Use Excel formulas to calculate averages and differences.

Create dynamic reports to display the calculated metrics.