Need help to simplify query

I have a query which need to simplify and still give the same result but faster , This is the Result query

SELECT Table2.PoNumber
     , Nz([MaterialCode],"None") AS MatCode
     , Nz([Material],"None") AS Mat
     , Nz([MaterialCons],"None") AS MatCons
     , Nz([MasterCode],"None") AS MastCode
     , Nz([Master],"None") AS Masterr
     , Nz([MasterCons],"None") AS MasterrCons
     , Nz([StretchCode],"None") AS StrCode
     , Nz([Stretch],"None") AS Str
     , Nz([StretchCons],"None") AS StrCons
     , Nz([PackCode],"None") AS PacCode
     , Nz([Pack],"None") AS Package
     , Nz([PackCons],"None") AS PacCons
     , Nz([MasterCons]/[MaterialCons],0) AS MasterPercent
FROM (((Table2 LEFT JOIN Sub1 ON Table2.PoNumber = Sub1.Po) 
LEFT JOIN Sub2 ON Table2.PoNumber = Sub2.Po) 
LEFT JOIN Sub3 ON Table2.PoNumber = Sub3.Po) 
LEFT JOIN Sub4 ON Table2.PoNumber = Sub4.Po;

This query depend on 4 Sub queries:

Sub1

SELECT Table1.Code AS MaterialCode
     , Table1.Item AS Material
     , Table1.Cons AS MaterialCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Material"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

Sub2

SELECT Table1.Code AS MasterCode
     , Table1.Item AS Master
     , Table1.Cons AS MasterCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="MasterPatch"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

Sub3

SELECT Table1.Code AS StretchCode
     , Table1.Item AS Stretch
     , Table1.Cons AS StretchCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Stretch"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

Sub4

SELECT Table1.Code AS PackCode
     , Table1.Item AS Pack
     , Table1.Cons AS PackCons
     , Table1.Po
FROM Table1
WHERE (((Table1.Type)="Package"))
GROUP BY Table1.Code
       , Table1.Item
       , Table1.Cons
       , Table1.Po;

I need to cancel those 4 sub queries and still get the same result. Note: the main db became UGLY because of so many queries depend on the same way of joining. Basically depend on one or two tables but with multiple criteria. So I need the way to be more efficient.

Thanks.

Solution

Since you intend to develop a wide format of your data, consider conditional formatting with LEFT JOIN of the two tables. However, only one value per group will be reshaped wide. Therefore, using MAX below will pick the maximum alphabetically ordered value if string or maximum number value if number for Code, Item, and Cons fields. No data is lost if there are only one Code, Item, Cons per Type grouping.

Also, given you run a division on Cons columns, these are assumed to be number fields and hence are not wrapped in NZ and 'None' to return string column. If Cons are numbers, consider adjusting aggregation from MAX to SUM or AVG as needed. Finally, division by zero is avoided.

SELECT t2.PoNumber
     , Nz(MAX(IIF(t1.Type='Material', t1.Code, NULL)), 'None') AS MatCode
     , Nz(MAX(IIF(t1.Type='Material', t1.Item, NULL)), 'None') AS Mat
     , MAX(IIF(t1.Type='Material', t1.Cons, NULL)) AS MatCons

     , Nz(MAX(IIF(t1.Type='MasterPatch', t1.Code, NULL)), 'None') AS MastCode
     , Nz(MAX(IIF(t1.Type='MasterPatch', t1.Item, NULL)), 'None') AS Masterr
     , MAX(IIF(t1.Type='MasterPatch', t1.Cons, NULL)) AS MasterCons

     , Nz(MAX(IIF(t1.Type='Stretch', t1.Code, NULL)), 'None') AS StrCode
     , Nz(MAX(IIF(t1.Type='Stretch', t1.Item, NULL)), 'None') AS Str
     , MAX(IIF(t1.Type='Stretch', t1.Cons, NULL)) AS StrCons

     , Nz(MAX(IIF(t1.Type='Package', t1.Code, NULL)), 'None') AS PacCode
     , Nz(MAX(IIF(t1.Type='Package', t1.Item, NULL)), 'None') AS Package
     , MAX(IIF(t1.Type='Package', t1.Cons, NULL)) AS PacCons

     , Nz(IIF([MatCons] <> 0, [MasterCons] / [MatCons], NULL), 0) AS MasterPercent

FROM Table2 t2
LEFT JOIN Table1 t1 ON t2.PoNumber = t1.Po
GROUP BY t2.PoNumber

Alternatively, if there are more than one Code, Item, and Cons per Type grouping, avoid aggregation at outer level. Notice use of DISTINCT and Type added to SELECT for IIF conditionals in outer level.

SELECT t2.PoNumber
     , IIF(t1.Type='Material', t1.Code, 'None') AS MatCode
     , IIF(t1.Type='Material', t1.Item, 'None') AS Mat
     , IIF(t1.Type='Material', t1.Cons, NULL) AS MatCons

     , IIF(t1.Type='MasterPatch', t1.Code, 'None') AS MastCode
     , IIF(t1.Type='MasterPatch', t1.Item, 'None') AS Masterr
     , IIF(t1.Type='MasterPatch', t1.Cons, NULL) AS MasterCons

     , IIF(t1.Type='Stretch', t1.Code, 'None') AS StrCode
     , IIF(t1.Type='Stretch', t1.Item, 'None') AS Str
     , IIF(t1.Type='Stretch', t1.Cons, NULL) AS StrCons

     , IIF(t1.Type='Package', t1.Code, 'None') AS PacCode
     , IIF(t1.Type='Package', t1.Item, 'None') AS Package
     , IIF(t1.Type='Package', t1.Cons, NULL) AS PacCons

     , NZ(IIF([MatCons] <> 0, [MasterCons] / [MatCons], NULL), 0) AS MasterPercent

FROM Table2 t2
LEFT JOIN (
    SELECT DISTINCT [Type], [Code], [Item], [Cons], [Po]
    FROM Table1
    WHERE Table1.Type IN ('Material', 'MasterPatch', 'Stretch', 'Package')
) t1