Solved

Access to SQL

Posted on 2014-04-18
8
322 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 69

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 500 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

624 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