Adding a New Table/Field and Linking

[rwn]

Im trying to add this Table/Field; Customer.Customer and have it linked to SO_Header.Customer to the existing functional below Query.

sql = sql & "Material.Location_ID, " _
& "Round(Q1.OHQ,0) AS OHQ, " _
& "SO_Detail.Note_Text, " _
& "SO_Header.Note_Text AS sSONotes " _
& "FROM SO_Header INNER JOIN ((SO_Detail LEFT JOIN Material ON SO_Detail.Material = Material.Material) LEFT JOIN " _
& "(SELECT Material_Location.Material, Sum(Material_Location.On_Hand_Qty) AS OHQ " _
& "FROM Material_Location " _
& "GROUP BY Material_Location.Material) AS Q1 " _
& "ON SO_Detail.Material = Q1.Material) ON SO_Header.Sales_Order = SO_Detail.Sales_Order SO_Header.Customer = Customer.Customer "

 

[rwn]

My apologies.

This is the exisiting functional query below(the one above was my attempt of linking the Customer.Customer to the SO_Header.Customer).
sql = sql & "Material.Location_ID, " _
& "Round(Q1.OHQ,0) AS OHQ, " _
& "SO_Detail.Note_Text, " _
& "SO_Header.Note_Text AS sSONotes " _
& "FROM SO_Header INNER JOIN ((SO_Detail LEFT JOIN Material ON SO_Detail.Material = Material.Material) LEFT JOIN " _
& "(SELECT Material_Location.Material, Sum(Material_Location.On_Hand_Qty) AS OHQ " _
& "FROM Material_Location " _
& "GROUP BY Material_Location.Material) AS Q1 " _
& "ON SO_Detail.Material = Q1.Material) ON SO_Header.Sales_Order = SO_Detail.Sales_Order "

 

[RyanEK]

What is your question?

 

[rwn]

My question?? It was in my original post!

 

[Olaf Doschke]

No, there is part of a query in your post, nothing more.

One recommendation: Run your VB code until the sql variable is fully set. store the value of the sql variable into a file query.sql and open that in SQL Server Management Studio. Run it in a query window there to debug it. Then fix your code.

HTH

Bye, Olaf.

 

[gmmastros]

try this:

SELECT  Material.Location_ID,
        Round(Q1.OHQ,0) AS OHQ,
        SO_Detail.Note_Text, 
        SO_Header.Note_Text AS sSONotes,
        Customer.Customer
FROM    SO_Header 
        INNER JOIN SO_Detail 
          ON SO_Header.Sales_Order = SO_Detail.Sales_Order 
        LEFT JOIN Material 
          ON SO_Detail.Material = Material.Material
        LEFT JOIN (
                SELECT  Material_Location.Material, 
                        Sum(Material_Location.On_Hand_Qty) AS OHQ
                FROM    Material_Location
                GROUP BY Material_Location.Material
                ) AS Q1
	  ON SO_Detail.Material = Q1.Material
        LEFT Join Customer
          On SO_Header.Customer = Customer.Customer

There is a lot of guessing here because you haven't really given enough details to properly answer this question. For example, you show a condition "SO_Header.Customer = Customer.Customer". But then you say you want to show Customer.Customer. If SO_Header.Customer is the same as Customer.Customer, then you would simply need to add SO_Header.Customer to the select clause. Only if you want to show other columns from the customer table would you need to join to it in the FROM clause of your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom