?
Solved

Access to SQL

Posted on 2014-04-18
8
Medium Priority
?
340 Views
Last Modified: 2014-07-21
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
Comment
Question by:PreciseITDept
7 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40009698
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
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 40009701
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
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 2000 total points
ID: 40009711
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40010535
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
 

Author Comment

by:PreciseITDept
ID: 40012473
Alex, your 'cleaned up' version works great!  THANKS!
Thanks too to Scott and Anthony for their contributions.
0
 

Author Comment

by:PreciseITDept
ID: 40188422
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
 

Author Comment

by:PreciseITDept
ID: 40209080
Thank you eenookami.  And thanks to Alex too for his anticipated assistance.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question