access full join on 3 tables

Question

I'm trying to perform a full outer join with 3 tables in MS access, but the problem is that access doesn't support it.

I've tried to do it with 3 left joins, but i get duplcated results for certain cases.

i am trying to join data where the name and date are the same in all 3 tables,

my query looks something like this:

 SELECT
    a.date, a.name, a.whatewer,
    b.date, b.name, b.whatewer,
    c.date, c.name, c.whatewer

    FROM (a LEFT JOIN b ON a.date = b.date AND a.name = b.name)
    LEFT JOIN c ON c.date = a.date AND c.name = a.name

 UNION

 SELECT
    a.date, a.name, a.whatewer,
    b.date, b.name, b.whatewer,
    c.date, c.name, c.whatewer

 FROM (b LEFT JOIN a ON a.date = b.date AND a.name = b.name)
 LEFT JOIN c ON c.date = a.date AND c.name = a.name


SELECT
   a.date, a.name, a.whatewer,
   b.date, b.name, b.whatewer,
   c.date, c.name, c.whatewer

FROM (c LEFT JOIN a ON a.date = c.date AND a.name = c.name)
LEFT JOIN b ON c.date = b.date AND c.name = b.name

The result i get is mostly correct but i get duplicated results when there is data in table b and c but not in a.

Instead of it all getting joined under a single name and date the result i get is one line with data from table b and then another one with combined data from b and c, as it should be.

How do i get rif of these unnecesary results?

Thank you!

EDIT:

Full code (names aren't in english, i hope you can stil understand enough to help):

SELECT
Nz (Nz (zajem.Datum, ak.Datum ), nk.Datum ) AS Datum,
Nz (Nz (zajem.Operater, ak.Operater ), nk.Operater ) AS Operater,
zajem.Skupni_cas_zajema AS [Skupni cas zajema (min)], 
zajem.Povprecni_cas_zajema_na_PZ AS [Povprecni cas zajema na PZ (min)], 
zajem.Stevilo_PZ AS [Stevilo zajetih PZ], 
zajem.Stevilo_poligonov AS [Stevilo poligonov], 

ak.Skupni_cas_zajema AS [Skupni cas AK (min)], 
ak.Povprecni_cas_zajema_na_PZ AS [Povprecni cas AK na PZ], 
ak.Stevilo_PZ AS [Stevilo PZ (AK)], 
ak.Stevilo_poligonov AS [Stevilo poligonov (AK)],

nk.Skupni_cas_zajema AS [Skupni cas NK (min)], 
nk.Povprecni_cas_zajema_na_PZ AS [Povprecni cas NK na PZ], 
nk.Stevilo_PZ AS [Stevilo PZ (NK)], 
nk.Stevilo_poligonov AS [Stevilo poligonov (NK)]

FROM
(tabela_ves_zajem AS zajem LEFT OUTER JOIN tabela_vse_ak AS ak ON zajem.datum = ak.datum AND zajem.operater = ak.operater) LEFT OUTER JOIN tabela_vse_nk as nk ON nk.datum = zajem.datum AND nk.operater = zajem.operater


UNION


SELECT
Nz (Nz (zajem.Datum, ak.Datum ), nk.Datum ) AS Datum,
Nz (Nz (zajem.Operater, ak.Operater ), nk.Operater ) AS Operater,
zajem.Skupni_cas_zajema AS [Skupni cas zajema (min)], 
zajem.Povprecni_cas_zajema_na_PZ AS [Povprecni cas zajema na PZ (min)], 
zajem.Stevilo_PZ AS [Stevilo zajetih PZ], 
zajem.Stevilo_poligonov AS [Stevilo poligonov], 

ak.Skupni_cas_zajema AS [Skupni cas AK (min)], 
ak.Povprecni_cas_zajema_na_PZ AS [Povprecni cas AK na PZ], 
ak.Stevilo_PZ AS [Stevilo PZ (AK)], 
ak.Stevilo_poligonov AS [Stevilo poligonov (AK)],

nk.Skupni_cas_zajema AS [Skupni cas NK (min)], 
nk.Povprecni_cas_zajema_na_PZ AS [Povprecni cas NK na PZ], 
nk.Stevilo_PZ AS [Stevilo PZ (NK)], 
nk.Stevilo_poligonov AS [Stevilo poligonov (NK)]

FROM
(tabela_vse_ak AS ak LEFT OUTER JOIN tabela_ves_zajem AS zajem ON zajem.datum = ak.datum AND zajem.operater = ak.operater) LEFT OUTER JOIN tabela_vse_nk as nk ON nk.datum = zajem.datum AND nk.operater = zajem.operater


UNION

SELECT
Nz (Nz (zajem.Datum, ak.Datum ), nk.Datum ) AS Datum,
Nz (Nz (zajem.Operater, ak.Operater ), nk.Operater ) AS Operater,
zajem.Skupni_cas_zajema AS [Skupni cas zajema (min)], 
zajem.Povprecni_cas_zajema_na_PZ AS [Povprecni cas zajema na PZ (min)], 
zajem.Stevilo_PZ AS [Stevilo zajetih PZ], 
zajem.Stevilo_poligonov AS [Stevilo poligonov], 

ak.Skupni_cas_zajema AS [Skupni cas AK (min)], 
ak.Povprecni_cas_zajema_na_PZ AS [Povprecni cas AK na PZ], 
ak.Stevilo_PZ AS [Stevilo PZ (AK)], 
ak.Stevilo_poligonov AS [Stevilo poligonov (AK)],

nk.Skupni_cas_zajema AS [Skupni cas NK (min)], 
nk.Povprecni_cas_zajema_na_PZ AS [Povprecni cas NK na PZ], 
nk.Stevilo_PZ AS [Stevilo PZ (NK)], 
nk.Stevilo_poligonov AS [Stevilo poligonov (NK)]

FROM
(tabela_vse_nk AS nk LEFT OUTER JOIN tabela_ves_zajem AS zajem ON zajem.datum = nk.datum AND zajem.operater = nk.operater) LEFT OUTER JOIN tabela_vse_ak as ak ON nk.datum = ak.datum AND nk.operater = ak.operater;

I am using nz(nz(zajem.datum.... to select only one date.

Data sample

All other tables are almost the same, they contain statistical data for tasks completed on certain day, each table for different task. I need to display all the data for a single day from a certain user ("operater").

I have tried the first answer but i have problems with presenting data in a nice form with it.

Thanks.

Would you provide sample data. – Cetin Basoz Aug 27 '18 at 12:05 — Cetin Basoz, Aug 27 '18 at 12:05

score 2 · Answer 1 · answered Aug 27 '18 at 12:07

Generate the list of ids first. Then use left join:

select . . . 
from (((select name, date from a
        union -- on purpose to remove duplicates
        select name, date from b
        union -- on purpose to remove duplicates
        select name, date from b
       ) nd left join
       a
       on a.name = nd.name and a.date = nd.date
      ) left join
      b
      on b.name = nd.name and b.date = nd.date
     ) left join
     c
     on c.name = nd.name and c.date = nd.date;

access full join on 3 tables

1 Answers1