Create a Query with SQL

[Ben_Entrew]

Dear all,
somehow this syntax is wrong:

Public Sub My_TNS_Query()
Dim strSQL As String
Dim qdf As QueryDef
strSQL = "SELECT [Profit Center].Division, Customers.[Customer Split], TEMP.Customer, Customers.[Reporting Names], TEMP.[Total Net Sales], TEMP.Quantity" & _
"FROM (TEMP LEFT JOIN [Profit Center] ON TEMP.[Profit Ctr] = [Profit Center].[Profit Center]) LEFT JOIN Customers ON TEMP.Customer = Customers.[Customer NO]" & _
"GROUP BY [Profit Center].Division,Customers.[Customer Split], TEMP.Customer"
With CurrentDb
'.QueryDefs.Delete ("TNS_Query")
Set qdf = CurrentDb.CreateQueryDef("TNS_QUERY", strSQL)
.Close
End With

End Sub


I attach you the error message.

Can someone help me please?

Thanks in advance.

Regards,
Behzat

 

[pr2-eugin]

No space between Quantity and FROM.. Also the same with Customer No and GROUP BY..

Public Sub My_TNS_Query()
    Dim strSQL As String
    Dim qdf As QueryDef
    
    strSQL = "SELECT [Profit Center].Division, Customers.[Customer Split], TEMP.Customer, " & _
             "Customers.[Reporting Names], TEMP.[Total Net Sales], TEMP.[COLOR=Red][B]Quantity " & _
             "FROM[/B][/COLOR] (TEMP LEFT JOIN [Profit Center] ON TEMP.[Profit Ctr] = [Profit Center].[Profit Center]) " & _
             "LEFT JOIN Customers ON TEMP.Customer = Customers.[COLOR=Red][B][Customer NO] " & _
             "GROUP BY[/B][/COLOR] [Profit Center].Division,Customers.[Customer Split], TEMP.Customer"
    
    With CurrentDb
        '.QueryDefs.Delete ("TNS_Query")
        Set qdf = .CreateQueryDef("TNS_QUERY", strSQL)
        .Close
    End With
End Sub

 

[Ben_Entrew]

Thanks Paul, now it creates the query.

But I can't open it. I guess the Group BY is somehow wrong.

I have to include Reporting Month in the Group By statement.

I guess I also have to write some kind of SUM for the Total Net Sales and Quantity part in the SELECT Statement?

 

[namliam]

Always easier if you keep things readable and logical....
Easy solution when continuing lines is to always make sure you add a space in front and at the back.... UNLESS you are really Really REALLY sure you dont need them

strSQL = " SELECT [Profit Center].Division, Customers.[Customer Split], TEMP.Customer, Customers.[Reporting Names], TEMP.[Total Net Sales], TEMP.Quantity" & _
         " FROM     (TEMP " & _
         " LEFT JOIN [Profit Center]     ON TEMP.[Profit Ctr] = [Profit Center].[Profit Center])  " & _
         " LEFT JOIN Customers           ON TEMP.Customer = Customers.[Customer NO] " & _
         " GROUP BY [Profit Center].Division,Customers.[Customer Split], TEMP.Customer"

I will forgo the normal "dont use spaces in names, bla bla" for now

 

[namliam]

Damn shouldnt leave replies on your screen for 20 mins I guess :/

Yes you need sum (or some other thing like count, average, etc) for the columns you do not include in your group by line ... like:

strSQL = "SELECT [Profit Center].Division, Customers.[Customer Split], TEMP.Customer, Customers.[Reporting Names], Sum(TEMP.[Total " & _
         " FROM     (TEMP " & _
         " LEFT JOIN [Profit Center]     ON TEMP.[Profit Ctr] = [Profit Center].[Profit Center])  " & _
         " LEFT JOIN Customers           ON TEMP.Customer = Customers.[Customer NO] " & _
         " GROUP BY [Profit Center].Division,Customers.[Customer Split], TEMP.Customer"

 

[pr2-eugin]

Using a Total Query, main thing to Note. Every field in the SELECT part should be either Grouped By or performed an Aggregate function upon. So Include the other three fields in the GROUP BY part.

I am pretty sure it will not be the result you are looking for. If that is correct, provide a bit more information (with example of dataset in table and the expected result) so we can try get you the help. If the result is correct then don't worry about it

 

[Ben_Entrew]

Thanks a lot for your help namliam. Now it worked.

I will use this syntax in the future, it's really easier to read.