• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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