CODEWITHSUNDEEP
sqlms-access
David
David

Reputation: 129

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)

Upvotes: 0

Views: 32

Answers (2)

Parfait
Parfait

Reputation: 107652

Fundamentally, you have a database design issue where you currently store data in wide format as shown with the suffixed columns H01-H24. Consider normalizing all tables (I90DIA26, i90dia03, i90dia09, PreciosMD) into long format as shown below which you can build with UNION or multiple INSERT queries to new tables. 

Fecha       Unidad_de_Programacion H_INDICATOR H_VALUE
YYYY-MM-DD                  Item 1           1     ### 
YYYY-MM-DD                  Item 1           2     ### 
YYYY-MM-DD                  Item 1           3     ### 
...
YYYY-MM-DD                  Item 1          24     ### 
YYYY-MM-DD                  Item 2           1     ### 
...

Doing so querying will be much easier in addition to employing scalability (added rows do not re-structure table) and efficiency (indexing, storage, etc.). In MS Access, there is a 255-column limit but no limit to rows (i.e., columns are expensive but rows are cheap).

PBF Query (using long tables and new join with table aliases)

SELECT i.[Unidad_de_Programacion], 
       SUM(NZ(i.H_VALUE) * NZ(p.H_VALUE)) AS EURO, 
       SUM(NZ(i.H_VALUE) AS MWh
FROM I90DIA26_Long i 
LEFT JOIN PreciosMD_Long p 
       ON i.[Fecha] = p.[Fecha]
      AND i.[H_INDICATOR] = p.[H_INDICATOR]
WHERE YEAR(i.[Fecha])=2018
GROUP BY i.[Unidad_de_Programacion];

RT MD Bajar Query (using long tables and new join with table aliases)

SELECT i3.[unidad_de_programacion],
       SUM(Nz(i3.[H_VALUE]) * ( Nz(i9.[H_VALUE]) - Nz(p.[H_VALUE]) )) AS EURO_Bajar,
       SUM(NZ(i3.H_VALUE) AS MWh

FROM (i90dia03_Long i3
INNER JOIN i90dia09_Long i9
        ON i3.[fecha] = i9.[fecha] 
       AND i3.[unidad_de_programacion] = i9.[unidad_de_programacion] 
       AND i3.[H_COLUMN] = i9.[H_COLUMN]) 
INNER JOIN preciosmd_Long p
        ON p.[fecha] = i9.[fecha] 
       AND p.[H_INDICATOR] = i9.[H_INDICATOR] 
WHERE YEAR(i3.[fecha]) = 2018 
  AND i3.[sentido] = 'bajar' 
GROUP  BY i3.[unidad_de_programacion];

While this redesign may pose an issue with current process or application, really and truly consider working on it today. MS Access is a rapid development tool and not a full-scale, enterprise server relational database so you should be able to rebuild without too much challenge in setup. In the concluding words of database guru regarding a too many columns error, @RolandoMySQLDBA:

There is no substitute or band-aid that can make up for bad design. Please, for your sake of your sanity in the future, normalize that table today !!!

Upvotes: 1

GMB
GMB

Reputation: 222492

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).

Upvotes: 1

Related Questions