Access Query Planned vs Realised

Question

I have created 2 Queries in Access and now I want to combine them.

1 of them returns sample data (Planned) :

EmployeId	TrainingId	Planned Hours
1	1	5
1	2	6
1	3	7

Other returns sample data (Completed) :

EmployeId	TrainingId	Completed Hours
1	2	6
1	3	8
1	4	10

The required Result from the 3rd query is :

EmployeId	TrainingId	Planned Hours	Completed Hours
1	1	5
1	2	6	6
1	3	7	8
1	4		10

I created a UNION ALL query like below but this return extra rows :

SELECT *
    FROM [Qry-Completed]
        LEFT JOIN [Qry-Planned]
        ON [Qry-Completed].TrainingId = [Qry-Planned].TrainingId AND 
              [Qry-Completed].EmployeId  = [Qry-Planned].EmployeId 
UNION ALL SELECT *
    FROM [Qry-Completed]
        RIGHT JOIN [Qry-Planned]
        ON [Qry-Completed].TrainingId = [Qry-Planned].TrainingId AND
             [Qry-Completed].EmployeId  = [Qry-Planned].EmployeId ;

How can I join these 2 queries to get the desired result?

Answer 1

This is for SQL Server, but that same strategy should hold in Access.

This takes advantage of the fact that UNION will remove duplicates. So it'll get rid of the extra rows except for the orphans in either direction.

https://dbfiddle.uk/Ch9Y7vkd

create table planned 
  (
    employee_id integer,
    training_id integer,
    planned_hours integer
  );

create table actual
  (
    employee_id integer,
    training_id integer,
    completed_hours integer

  );

insert into planned values 
  (1,1,5),
  (1,2,6),
  (1,3,7);

insert into actual values 
  (1,2,6),
  (1,3,8),
  (1,4,10);

select p.employee_id,
       p.training_id,
       p.planned_hours,
       a.completed_hours
  from planned p
  left
  join actual a
    on p.employee_id = a.employee_id
   and p.training_id = a.training_id
 union
select a.employee_id,
       a.training_id,
       p.planned_hours,
       a.completed_hours
  from actual a
  left
  join planned p
    on p.employee_id = a.employee_id
   and p.training_id = a.training_id