Excel VBA SQL Join Syntax Error

Question

I am writing a sub that allows the user to select a Customer from a listbox. The selection is recorded as CustomerID (integer variable) and used to query an Access Database file. The sub should then output to an excel worksheet sales information about the specified customer, notably:

1. Order Date
2. OrderID
3. Total Order Cost (defined as qty sold * Price Sold)

The access file has 3 tables that I need: Customers, Orders, LineItems

My code below should join the customer ID to order ID fields, which is then joined against order ids, and order ids is joined on line items.

' Define SQL statement to get order info for selected product.
SQL = "SELECT O.OrderDate, COUNT(O.OrderID), SUM(L.QuantityOrdered * L.QuotedPrice) AS [TotalCost] " _
        & "FROM (((Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID) " _
        & "INNER JOIN ON O.OrderID = O.CustomerID) INNER JOIN LineItems L " _
        & "ON O.OrderID = L.OrderID)" _
        & "WHERE O.CustomerID =" & CustomerID & " " _
        & "GROUP BY O.OrderDate, O.OrderID" _
        & "ORDER BY O.OrderDate"

I keep getting a "Syntax Error in FROM clause". Are my JOIN statements are correct? I have played around with the (), "", etc without success. I have checked and Table Names are correct (Orders, Customers, LineItems) field names are also spelled correctly.

Answer 1

I like to use an array and the Join method with a space delimiter. That makes sure I don't miss any spaces (which @McAdam133 pointed out that you did).

Dim aSql(1 To 6) As String

aSql(1) = "SELECT O.OrderDate, COUNT(O.OrderID), SUM([L.QuantityOrdered]*[L.QuotedPrice]) AS TotalCost"
aSql(2) = "FROM (Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID)"
aSql(3) = "INNER JOIN LineItems L ON O.OrderID = L.OrderID"
aSql(4) = "WHERE C.CustomerID = " & CustomerID
aSql(5) = "GROUP BY O.OrderDate"
aSql(6) = "ORDER BY O.OrderDate"

Set rs = CurrentProject.Connection.Execute(Join(aSql, Space(1)))

Here's my advice for inner joins that don't work. Create a Query in Access and look at the SQL it generates. It may not be the prettiest SQL, but it may help you determine what's wrong. If you put Customers, Orders, and LineItems in a query window, draw your arrows if necessary (probably will be there by default), and put a couple of fields in there, Access will generate something like

SELECT Orders.OrderID, Orders.OrderDate, LineItems.QuantityOrdered, LineItems.QuotedPrice
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID;

That doesn't group anything or use aliases, but it gives you a working statement. Then you can modify with aliases and groupings, testing it along the way.

From your example:

& "FROM (((Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID) " _

This line is good. You're joining Customers on Orders using the primary key from Customers and presumably a foreign key in Orders.

& "INNER JOIN ON O.OrderID = O.CustomerID) INNER JOIN LineItems as L " _

I'm not sure what the first join is trying to accomplish, but as @OpiesDad commented it's not what you want. You've already successfully joined Customers and Orders in the first line, so you can take the result of that join and join it up to LineItems (aSql(3) above). The second join (to LineItems) looks fine.

You can join two tables on more than one field. Like if you had two tables of customers and you wanted to see if there is any overlap.

FROM Wholesale INNER JOIN Retail ON Wholesale.CustomerName = Retail.CustName AND Wholesale.State = Retail.StateOrProvince

Based on the structure you've shown, you have nicely unique primary keys in all your tables, so joining on more than one field isn't necessary.

Lastly, you're grouping on OrderID. It's not causing an error, but it's not doing anything either. You use OrderID in an aggregate function in the SELECT portion. You should aggregate the fields you want to aggregate and group by the fields you don't aggregate.

Answer 2

You must use AS when you create an alias for a table in your INNER JOIN's:

' Define SQL statement to get order info for selected product.
SQL = "SELECT O.OrderDate, COUNT(O.OrderID), SUM(L.QuantityOrdered * L.QuotedPrice) AS [TotalCost] " _
        & "FROM (((Customers as C INNER JOIN Orders O ON C.CustomerID = O.CustomerID) " _
        & "INNER JOIN ON O.OrderID = O.CustomerID) INNER JOIN LineItems as L " _
        & "ON O.OrderID = L.OrderID)" _
        & "WHERE O.CustomerID =" & CustomerID & " " _
        & "GROUP BY O.OrderDate, O.OrderID" _
        & "ORDER BY O.OrderDate;"

To be sure, terminate the statement with ;.

Answer 3

The problem you are having is that the join you are trying to do doesn't make sense.

Let's first just get the relevant order and then add the line items as it will make the explanation simpler.

To do this, you want the SQL:

SELECT C.CustomerID, C.CustomerName, O.OrderID, O.OrderDate
FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE C.CustomerID = 15
ORDER By O.OrderDate

Note, this assumes that the customer you are looking for has ID 15.

This will give you a list of all orders in ascending order for the requested customer.

If you want the line items, then you need to link to this table as well:

 SELECT C.CustomerID, C.CustomerName, O.OrderId, O.OrderDate
     , SUM(L.QuantityOrdered * L.QuotedPrice) AS [TotalCost]
 FROM ((Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID)
              INNER JOIN LineItems L ON O.OrderID = L.OrderID)
 WHERE C.CustomerID = 15
 GROUP BY C.CustomerID, C.CustomerName, O.OrderID, O.OrderDate
 ORDER BY O.OrderDate

This is likely the query you are looking for. The second INNER JOIN you had listed was superfluous and didn't make sense. You don't want to match the OrderID to the CustomerID, you want the list of orders that match that customer. The first INNER JOIN on C to O already creates this. The where clause limits the customer table to just the one customer.

To put this in your code, just replace the "15" with "CustomerID" from your form.

Also, per McAdam's comment, you are missing spaces in a couple of places. To fix this, I recommend putting all spaces as the beginning of the line so that you can make sure they are there (as is done below). The final code should look like this (removing the customer info from the output):

 SQL = "SELECT O.OrderDate, O.OrderID" _
    & ", SUM(L.QuantityOrdered * L.QuotedPrice) AS [TotalCost]" _
    & " FROM ((Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID)" _
    & " INNER JOIN LineItems L ON O.OrderID = L.OrderID)" _
    & " WHERE O.CustomerID =" & CustomerID _
    & " GROUP BY O.OrderDate, O.OrderID" _
    & " ORDER BY O.OrderDate"

You also don't seem to actually want the count of orderIDs so I took that out as it also didn't make too much sense.