SQL to join multiple tables while summing and grouping

Question

I'm looking for help in how to structure a MS Access SQL query on multiple tables and summarize the results grouped by Project and the sum of the dollar amounts for different types of financial transactions.

The Project table contains meta data about a Project. The Actuals, Budget, and Forecast tables are records of actual and/or planned financial transactions against the Project.

The tables and fields (simplified significantly for this post) are as follows:

Project
--------------
project_id
project_name
project_manager

Budget
--------------
entry_date
project_id
amount

Actuals
--------------
entry_date
project_id
amount

Forecast
--------------
entry_date
project_id
amount

SQL attempt:

SELECT
    p.[project_id],
    SUM(b.[amount]) AS budget_amount,
    SUM(a.[amount]) AS actual_amount,
    SUM(f.[amount]) AS forecast_amount
FROM
    Project AS p,
    Budget AS b,
    Actuals AS a,
    Forecast AS f
WHERE
    p.[project_id] = b.[project_id]
    AND b.[project_id] = a.[project_id]
    AND a.[project_id] = f.[project_id]
GROUP BY
    p.[project_id];

Desired Result:

project_id  budget_amount   actual_amount   forecast_amount
2021-001    $2,000.00   $2,900.00   $2,400.00
2021-002    $3,000.00   $3,500.00   $5,000.00

Actual Result:

project_id  budget_amount   actual_amount   forecast_amount
2021-001    $12,000.00  $17,400.00  $9,600.00
2021-002    $18,000.00  $21,000.00  $20,000.00

Sample Data:

Project

project_id  project_name    project_manager
2021-001    First Project of 2021   Jack Black
2021-002    Second Project of 2021  James Dean

Actuals

entry_date  project_id  amount
1/9/2021    2021-001    $1,300.00
1/23/2021   2021-001    $1,600.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $2,000.00

Budget

entry_date  project_id  amount
1/9/2021    2021-001    $1,000.00
1/23/2021   2021-001    $1,000.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $1,500.00

Forecast

entry_date  project_id  amount
1/9/2021    2021-001    $900.00
1/23/2021   2021-001    $1,000.00
2/6/2021    2021-001    $500.00
1/9/2021    2021-002    $1,500.00
1/23/2021   2021-002    $3,000.00
2/6/2021    2021-002    $500.00

Answer 1

What you need to do is to create summaries of each independently on the project granularity before you join them in a final query.

This change puts all the subqueries to run inside one query. Assuming project_id is a unique key in the project table, this means:

SELECT
    p.[project_id],
    budget_amount,
    actual_amount,
    forecast_amount
FROM
    Project AS p left join 
    (SELECT
    [project_id],
    SUM([amount]) AS budget_amount,
FROM
    Budget
GROUP BY
    [project_id]) AS b on p.project_id=b.project_id
Left join 
    (SELECT
    [project_id],
    SUM([amount]) AS actual_amount,
FROM
    Actuals
GROUP BY
    [project_id]) AS a on p.project_id = a.project_id
Left join 
    (SELECT
    [project_id],
    SUM([amount]) AS forecast_amount,
FROM
    forecast
GROUP BY
    [project_id]) AS f on p.project_id = f.project_id

The project table left joins all other summaries because it is assumed to contain a list of projects which may/may not exist in the other summaries.

Answer 2

I was really hoping for a more elegant approach. Also, I'm fairly confident there's a better way, but this works and hopefully I'll remain employed.

Certainly open to feedback.

SELECT
    project_id,
    budget_amount,
    actual_amount,
    forecast_amount
FROM (
SELECT b.[project_id] as fs_project_id, budget_amount, actual_amount
    FROM
        (   
    SELECT
            b.[project_id],
            SUM(b.[amount]) AS budget_amount
        FROM
            Budget AS b
        GROUP BY
            b.[project_id]
    ) AS q_BudgetSum
        LEFT JOIN (
    SELECT
            a.[project_id],
            SUM(a.[amount]) AS actual_amount
        FROM
            Actuals AS a
        GROUP BY
            a.[project_id]
    ) AS q_ActualSum ON q_ActualSum.[project_id] = q_BudgetSum.[project_id]
) AS FirstSet
    LEFT JOIN
    ( SELECT p.[project_id] as project_id, forecast_amount
    FROM
        (   
    SELECT *
        FROM
            Project AS p
    ) AS q_Project
        LEFT JOIN (
    SELECT
            f.[project_id],
            SUM(f.[amount]) AS forecast_amount
        FROM
            Forecast AS f
        GROUP BY
            f.[project_id]
    ) AS q_ForecastSum ON q_ForecastSum.[project_id] = q_Project.[project_id]
)
AS SecondSet
    ON FirstSet.[fs_project_id] = SecondSet.[project_id];