How to JOIN three tables in SQL MS Access database

Data and Databases
1

I’m trying to merge data from three different tables in MS Access but I’m getting duplicate rows instead of unique records. Here’s my database structure:

Tables:

  • Task_Matrix - stores task info (ProjectName, Department, Level)
  • Timeline_Tracking - tracks project phases (ProjectName, Period, Stage)
  • Resource_Requirements - contains staffing needs (Department, Level, Stage, Required)

Sample Data Creation:

CREATE TABLE Task_Matrix
    (`ProjectName` varchar(31), `Department` varchar(10), `Level` varchar(6));

INSERT INTO Task_Matrix
    (`ProjectName`, `Department`, `Level`)
VALUES
    ('Mobile App - Phoenix', 'Design', 'High'),
    ('Mobile App - Phoenix', 'Testing', 'Low'),
    ('Mobile App - Phoenix', 'Backend', 'High'),
    ('Website Portal - Alpha', 'Design', 'Medium'),
    ('Website Portal - Alpha', 'Testing', 'Low'),
    ('Website Portal - Alpha', 'Backend', 'Medium');

CREATE TABLE Timeline_Tracking
    (`ProjectName` varchar(31), `Period` datetime, `Stage` varchar(1));

INSERT INTO Timeline_Tracking
    (`ProjectName`, `Period`, `Stage`)
VALUES
    ('Mobile App - Phoenix', '2023-01-01', 'A'),
    ('Mobile App - Phoenix', '2023-02-01', 'B'),
    ('Mobile App - Phoenix', '2023-03-01', 'C'),
    ('Website Portal - Alpha', '2023-01-01', 'A'),
    ('Website Portal - Alpha', '2023-02-01', 'A');

CREATE TABLE Resource_Requirements
    (`Department` varchar(8), `Level` varchar(6), `Stage` varchar(1), `Required` int);

INSERT INTO Resource_Requirements
    (`Department`, `Level`, `Stage`, `Required`)
VALUES
    ('Design', 'High', 'A', 2),
    ('Design', 'High', 'B', 3),
    ('Design', 'Medium', 'A', 1),
    ('Testing', 'Low', 'A', 1),
    ('Backend', 'High', 'A', 3);

My Current Query:

SELECT DISTINCT tm.ProjectName, tt.Period, tt.Stage, tm.Department, tm.Level, rr.Required
FROM (Timeline_Tracking tt 
INNER JOIN Resource_Requirements rr ON tt.Stage = rr.Stage) 
INNER JOIN Task_Matrix tm ON (rr.Level = tm.Level AND rr.Department = tm.Department)
ORDER BY tm.ProjectName, tt.Period;

I want results showing: ProjectName, Department, Period, Stage, Level, Required - but only for matching records. Right now I’m getting way more rows than expected. What’s wrong with my join logic?

2

You’re getting a Cartesian product because of your join order. When you join Timeline_Tracking with Resource_Requirements on just Stage, you’re matching every timeline record with every resource record that has the same stage - doesn’t matter what project it’s from. This explodes your dataset before you even get to Task_Matrix. I hit the same issue with inventory databases. Fix it by establishing project context first - join Timeline_Tracking and Task_Matrix on ProjectName, then pull in Resource_Requirements: sql SELECT DISTINCT tm.ProjectName, tm.Department, tt.Period, tt.Stage, tm.Level, rr.Required FROM (Task_Matrix tm INNER JOIN Timeline_Tracking tt ON tm.ProjectName = tt.ProjectName) INNER JOIN Resource_Requirements rr ON (tt.Stage = rr.Stage AND tm.Department = rr.Department AND tm.Level = rr.Level) ORDER BY tm.ProjectName, tt.Period; This way each timeline entry connects to its actual project tasks before matching resources. No more duplicate records.

3

Interesting problem! Try adding a projectname field to your resource_Requirements table - that might be the missing piece. Right now you can’t directly link resources to specific projects. Are you assuming all stages need the same resources regardless of project, or should each project get its own resource allocation?