ASP (classic) Frontend Access 2007 Backend SQL Join headache
Hello All,
I am getting a bit stuck on the following sql string, I have an asp classic web page connected to an access 2007 database. I am trying to get the totals from a query called qrycondetailsextended totals, the fields I want from there are sumofextended price, perhaps sumofquantity too but most importantly for now is the sumofextended price field.
Here is my code:
strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, tbl_truck_Details.truckid, qrycondetailsextendedtotals.sumofquantity" & _
"tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Total.sumofquantity AS Total, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
"Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2, Truck.Truck_Registration AS TruckReg " & _
"FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
"ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
"(tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total) " & _
"ON tbl_shipOrders.ship_order_ID = Total.ship_ID " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid
I get this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tbl_ShipOrders.Receiver_ID = Receiver.custid tbl_shipOrders INNER JOIN qrycondetailsextendedtotals AS Total ON tbl_shipOrders.ship_order_ID = Total.ship_I'."
Using just:
strSQL = "SELECT tbl_ShipOrders.idate, tbl_ShipOrders.invamt, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, tbl_shiporders.loaddate, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, " & _
"tbl_ShipOrders.del, tbl_ShipOrders.discountpr, Sender.company AS Sender_name, Sender.Tel_1 AS Sender_Tel, Sender.Tel_2 AS Sender_Tel2," & _
"Receiver.company AS Receiver_name, Receiver.Tel_1 AS Receiver_Tel, Receiver.Tel_2 AS Receiver_Tel2 " & _
"FROM (tbl_ShipOrders INNER JOIN tbl_Customers AS Sender " & _
"ON tbl_ShipOrders.Sender_ID = Sender.custid) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON tbl_ShipOrders.Receiver_ID = Receiver.custid " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid
no problems, pulls the details out nicely, the problem is trying to create a further join from the qrycondetailsextendedtotals.
Any and all help will be fully appreciated
Thanks in Advance
Regards
Ben