Show results from different queries in 1 overview in MS-ACCESS

I have different relational tables and I perform different queries on these tables to get some results.

This is 1 example of a query called PBF

SELECT I90DIA26.[Unidad_de_Programacion], 
       SUM(NZ(I90DIA26.[H01],0) * NZ(PreciosMD.[H01],0) +
           NZ(I90DIA26.[H02],0) * NZ(PreciosMD.[H02],0) +
           NZ(I90DIA26.[H03],0) * NZ(PreciosMD.[H03],0) +
           NZ(I90DIA26.[H04],0) * NZ(PreciosMD.[H04],0) +
           NZ(I90DIA26.[H05],0) * NZ(PreciosMD.[H05],0) +
           NZ(I90DIA26.[H06],0) * NZ(PreciosMD.[H06],0) +
           NZ(I90DIA26.[H07],0) * NZ(PreciosMD.[H07],0) +
           NZ(I90DIA26.[H08],0) * NZ(PreciosMD.[H08],0) +
           NZ(I90DIA26.[H09],0) * NZ(PreciosMD.[H09],0) +
           NZ(I90DIA26.[H10],0) * NZ(PreciosMD.[H10],0) +
           NZ(I90DIA26.[H11],0) * NZ(PreciosMD.[H11],0) +
           NZ(I90DIA26.[H12],0) * NZ(PreciosMD.[H12],0) + 
           NZ(I90DIA26.[H13],0) * NZ(PreciosMD.[H13],0) +
           NZ(I90DIA26.[H14],0) * NZ(PreciosMD.[H14],0) +
           NZ(I90DIA26.[H15],0) * NZ(PreciosMD.[H15],0) +
           NZ(I90DIA26.[H16],0) * NZ(PreciosMD.[H16],0) +
           NZ(I90DIA26.[H17],0) * NZ(PreciosMD.[H17],0) +
           NZ(I90DIA26.[H18],0) * NZ(PreciosMD.[H18],0) +
           NZ(I90DIA26.[H19],0) * NZ(PreciosMD.[H19],0) + 
           NZ(I90DIA26.[H20],0) * NZ(PreciosMD.[H20],0) +
           NZ(I90DIA26.[H21],0) * NZ(PreciosMD.[H21],0) +
           NZ(I90DIA26.[H22],0) * NZ(PreciosMD.[H22],0) +
           NZ(I90DIA26.[H23],0) * NZ(PreciosMD.[H23],0) +
           NZ(I90DIA26.[H24],0) * NZ(PreciosMD.[H24],0)) AS EURO, 

       SUM(NZ(I90DIA26.[H01],0) + NZ(I90DIA26.[H02],0) + NZ(I90DIA26.[H03],0) + 
           NZ(I90DIA26.[H04],0) + NZ(I90DIA26.[H05],0) + NZ(I90DIA26.[H06],0) +
           NZ(I90DIA26.[H07],0) + NZ(I90DIA26.[H08],0) + NZ(I90DIA26.[H09],0) + 
           NZ(I90DIA26.[H10],0) + NZ(I90DIA26.[H11],0) + NZ(I90DIA26.[H12],0) + 
           NZ(I90DIA26.[H13],0) + NZ(I90DIA26.[H14],0) + NZ(I90DIA26.[H15],0) +
           NZ(I90DIA26.[H16],0) + NZ(I90DIA26.[H17],0) + NZ(I90DIA26.[H18],0) + 
           NZ(I90DIA26.[H19],0) + NZ(I90DIA26.[H20],0) + NZ(I90DIA26.[H21],0) +
           NZ(I90DIA26.[H22],0) + NZ(I90DIA26.[H23],0) + NZ(I90DIA26.[H24],0)) AS MWh

FROM I90DIA26 LEFT JOIN PreciosMD ON I90DIA26.[Fecha] = PreciosMD.[Fecha]
WHERE year(I90DIA26.[Fecha])=2018
GROUP BY I90DIA26.[Unidad_de_Programacion];

And this is another one called RT MD Bajar

SELECT i90dia03.[unidad_de_programacion], 
       SUM(Nz(i90dia03.[h01], 0) * ( Nz(i90dia09.[h01], 0) - Nz(preciosmd.[h01], 0) ) + 
           Nz(i90dia03.[h02], 0) * ( Nz(i90dia09.[h02], 0) - Nz(preciosmd.[h02], 0) ) + 
           Nz(i90dia03.[h03], 0) * ( Nz(i90dia09.[h03], 0) - Nz(preciosmd.[h03], 0) ) + 
           Nz(i90dia03.[h04], 0) * ( Nz(i90dia09.[h04], 0) - Nz(preciosmd.[h04], 0) ) + 
           Nz(i90dia03.[h05], 0) * ( Nz(i90dia09.[h05], 0) - Nz(preciosmd.[h05], 0) ) + 
           Nz(i90dia03.[h06], 0) * ( Nz(i90dia09.[h06], 0) - Nz(preciosmd.[h06], 0) ) + 
           Nz(i90dia03.[h07], 0) * ( Nz(i90dia09.[h07], 0) - Nz(preciosmd.[h07], 0) ) + 
           Nz(i90dia03.[h08], 0) * ( Nz(i90dia09.[h08], 0) - Nz(preciosmd.[h08], 0) ) + 
           Nz(i90dia03.[h09], 0) * ( Nz(i90dia09.[h09], 0) - Nz(preciosmd.[h09], 0) ) + 
           Nz(i90dia03.[h10], 0) * ( Nz(i90dia09.[h10], 0) - Nz(preciosmd.[h10], 0) ) + 
           Nz(i90dia03.[h11], 0) * ( Nz(i90dia09.[h11], 0) - Nz(preciosmd.[h11], 0) ) + 
           Nz(i90dia03.[h12], 0) * ( Nz(i90dia09.[h12], 0) - Nz(preciosmd.[h12], 0) ) + 
           Nz(i90dia03.[h13], 0) * ( Nz(i90dia09.[h13], 0) - Nz(preciosmd.[h13], 0) ) + 
           Nz(i90dia03.[h14], 0) * ( Nz(i90dia09.[h14], 0) - Nz(preciosmd.[h14], 0) ) + 
           Nz(i90dia03.[h15], 0) * ( Nz(i90dia09.[h15], 0) - Nz(preciosmd.[h15], 0) ) + 
           Nz(i90dia03.[h16], 0) * ( Nz(i90dia09.[h16], 0) - Nz(preciosmd.[h16], 0) ) + 
           Nz(i90dia03.[h17], 0) * ( Nz(i90dia09.[h17], 0) - Nz(preciosmd.[h17], 0) ) + 
           Nz(i90dia03.[h18], 0) * ( Nz(i90dia09.[h18], 0) - Nz(preciosmd.[h18], 0) ) + 
           Nz(i90dia03.[h19], 0) * ( Nz(i90dia09.[h19], 0) - Nz(preciosmd.[h19], 0) ) + 
           Nz(i90dia03.[h20], 0) * ( Nz(i90dia09.[h20], 0) - Nz(preciosmd.[h20], 0) ) + 
           Nz(i90dia03.[h21], 0) * ( Nz(i90dia09.[h21], 0) - Nz(preciosmd.[h21], 0) ) + 
           Nz(i90dia03.[h22], 0) * ( Nz(i90dia09.[h22], 0) - Nz(preciosmd.[h22], 0) ) + 
           Nz(i90dia03.[h23], 0) * ( Nz(i90dia09.[h23], 0) - Nz(preciosmd.[h23], 0) ) + 
           Nz(i90dia03.[h24], 0) * ( Nz(i90dia09.[h24], 0) - Nz(preciosmd.[h24], 0) )) AS EURO_Bajar, 

       SUM(Nz(i90dia03.[h01], 0) + Nz(i90dia03.[h02], 0) 
           + Nz(i90dia03.[h03], 0) + Nz(i90dia03.[h04], 0) 
           + Nz(i90dia03.[h05], 0) + Nz(i90dia03.[h06], 0) 
           + Nz(i90dia03.[h07], 0) + Nz(i90dia03.[h08], 0) 
           + Nz(i90dia03.[h09], 0) + Nz(i90dia03.[h10], 0) 
           + Nz(i90dia03.[h11], 0) + Nz(i90dia03.[h12], 0) 
           + Nz(i90dia03.[h13], 0) + Nz(i90dia03.[h14], 0) 
           + Nz(i90dia03.[h15], 0) + Nz(i90dia03.[h16], 0) 
           + Nz(i90dia03.[h17], 0) + Nz(i90dia03.[h18], 0) 
           + Nz(i90dia03.[h19], 0) + Nz(i90dia03.[h20], 0) 
           + Nz(i90dia03.[h21], 0) + Nz(i90dia03.[h22], 0) 
           + Nz(i90dia03.[h23], 0) + Nz(i90dia03.[h24], 0)) AS MWh_Bajar 
FROM   (i90dia03 
INNER JOIN i90dia09 
       ON ( i90dia03.[fecha] = i90dia09.[fecha] ) 
      AND ( i90dia03.[unidad_de_programacion] = i90dia09.[unidad_de_programacion] )) 
INNER JOIN preciosmd 
        ON preciosmd.[fecha] = i90dia09.[fecha] 
WHERE Year(i90dia03.[fecha]) = 2018 
  AND i90dia03.[sentido] = "bajar" 
GROUP  BY i90dia03.[unidad_de_programacion];

In the end what I need is 1 query showing me the results from all these queries in 1 overview. But my problem is I can’t make a UNION work because I have different where clauses in the different queries and I cant make a left join work because I have different where clauses in the different queries.

This is how far I got but it doesn’t work because of the where clauses:

SELECT PBF.Unidad_de_Programacion, 
       Round([PBF].MWh,2) AS Expr1, 
       Round([RT MD Bajar].EURO_Bajar,2) AS Expr2
FROM (PBF 
LEFT JOIN [RT MD Bajar] 
      ON PBF.Unidad_de_Programacion = [RT MD Bajar].Unidad_de_Programacion)

Answer

You turn both existing queries to subqueries, and then JOIN them.

Your query would essentially be:

SELECT 
    t1.[Unidad_de_Programacion],
    t1.EURO,
    t1.MWh
    t2.EURO_Bajar,
    t2.MWh_Bajar    
FROM (
    SELECT I90DIA26.[Unidad_de_Programacion], ... AS EURO, ... AS MWh
    FROM I90DIA26 LEFT JOIN PreciosMD ON I90DIA26.[Fecha] = PreciosMD.[Fecha]
    WHERE year(I90DIA26.[Fecha])=2018
    GROUP BY I90DIA26.[Unidad_de_Programacion] 
) INNER JOIN (
    SELECT I90DIA03.[Unidad_de_Programacion], ... AS EURO_Bajar, ... AS MWh_Bajar
    FROM ...
    WHERE year(I90DIA03.[Fecha])=2018 and I90DIA03.[Sentido]="Bajar"
    GROUP BY I90DIA03.[Unidad_de_Programacion]
) t2 ON (t1.[Unidad_de_Programacion] = t2.[Unidad_de_Programacion] )

You can change the INNER JOIN to a LEFT JOIN (and possibly change the order of the derived tables) as you need, but the logic stays the same.

Note: if you need results in rows and not in columns, you can also use UNION ALL with the same technique (turn the existing queries to subqueries).

Advertisement

Answer