Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access to SQL

Posted on 2014-04-18
8
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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