Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Access to SQL

Can someone please convert these three Access queries (two subqueries and a final top query) to one SQL statement?  Thank you in advance.

SUBQUERY1
SELECT Material.Material, Sum(Material_Location.On_Hand_Qty) AS OH
FROM Material LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE (((Material.Class) Like "fghwkb"))
GROUP BY Material.Material
ORDER BY Material.Material;

SUBQUERY2
SELECT Material.Material, Material.Class, Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Sum(Job.Order_Quantity) AS SumOfOrder_Quantity
FROM (Material LEFT JOIN Job ON Material.Material = Job.Part_Number) LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
WHERE (((Job.Status) Like "active"))
GROUP BY Material.Material, Material.Class
HAVING (((Material.Class) Like "fghwkb"))
ORDER BY Material.Material;

FINALQUERY
SELECT Material.Material, [SUBQUERY1].OH, [SUBQUERY2].SumOfOrder_Quantity
FROM (Material LEFT JOIN [SUBQUERY2] ON Material.Material = [SUBQUERY2].Material) LEFT JOIN [SUBQUERY1] ON Material.Material = [SUBQUERY1].Material
WHERE (((Material.Class)="fghwkb"))
ORDER BY Material.Material;
0
PreciseITDept
Asked:
PreciseITDept
1 Solution
 
Scott PletcherSenior DBACommented:
Just change the double quotes " to single quotes '.  The rest is acceptable to SQL Server, even if it's not the way it's typically coded there.
0
 
Alex MatzingerDatabase AdministratorCommented:
Is this what you're looking for:

SELECT Material.Material, SUBQUERY1.OH, SUBQUERY2.SumOfOrder_Quantity
FROM (Material 
  LEFT JOIN 
   (SELECT Material.Material, Material.Class, Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Sum(Job.Order_Quantity) AS SumOfOrder_Quantity
    FROM (Material LEFT JOIN Job ON Material.Material = Job.Part_Number) LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
    WHERE (((Job.Status) Like "active"))
    GROUP BY Material.Material, Material.Class
    HAVING (((Material.Class) Like "fghwkb"))) SUBQUERY2
ON Material.Material = SUBQUERY2.Material) 
  LEFT JOIN 
   (SELECT Material.Material, Sum(Material_Location.On_Hand_Qty) AS OH
    FROM Material LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
    WHERE (((Material.Class) Like "fghwkb"))
    GROUP BY Material.Material) SUBQUERY1
  ON Material.Material = SUBQUERY1.Material
WHERE (((Material.Class)="fghwkb"))
ORDER BY Material.Material;

Open in new window

0
 
Alex MatzingerDatabase AdministratorCommented:
Cleaned it up a bit

SELECT Material.Material, SUBQUERY1.OH, SUBQUERY2.SumOfOrder_Quantity
FROM Material
  LEFT JOIN
   (SELECT Material.Material, Material.Class, Sum(Material_Location.On_Hand_Qty) AS SumOfOn_Hand_Qty, Sum(Job.Order_Quantity) AS SumOfOrder_Quantity
    FROM (Material LEFT JOIN Job ON Material.Material = Job.Part_Number) LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
    WHERE Job.Status Like ('active')
    GROUP BY Material.Material, Material.Class
    HAVING Material.Class Like ('%fghwkb%')) SUBQUERY2
ON Material.Material = SUBQUERY2.Material
  LEFT JOIN
   (SELECT Material.Material, Sum(Material_Location.On_Hand_Qty) AS OH
    FROM Material LEFT JOIN Material_Location ON Material.Material = Material_Location.Material
    WHERE Material.Class Like ('%fghwkb%')
    GROUP BY Material.Material) SUBQUERY1
  ON Material.Material = SUBQUERY1.Material
WHERE Material.Class='fghwkb'
ORDER BY Material.Material;

Open in new window


the % in the like statements act as a wild card, so any CLASS with "fghwkb" anywhere in it will show up.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
This:
HAVING (((Material.Class) Like "fghwkb"))
Would be best in a WHERE clause and use a wildcard, as in:
WHERE Material.Class LIKE 'fghwkb%'
0
 
PreciseITDeptAuthor Commented:
Alex, your 'cleaned up' version works great!  THANKS!
Thanks too to Scott and Anthony for their contributions.
0
 
PreciseITDeptAuthor Commented:
Alex, unfortunately, I spoke too soon.  The Job.Order_Quantity field is being summed once for every new material_location.on_hand_qty record that is found.  Can you please solve and rewrite the query complete?   Thanks.  Greg
0
 
PreciseITDeptAuthor Commented:
Thank you eenookami.  And thanks to Alex too for his anticipated assistance.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now