ASP (classic) Frontend Access 2007 Backend SQL Join headache

[ben2203]

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

[HiTechCoach]

I am not sure that through ODBC you can join to another query on an Access database like ab SQL Server View.

I would convert the query qrycondetailsextendedtotals into a sub query.

[ben2203]

Oh, forgot to update this, actually I sorted it in the end:

strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.idate, tbl_ShipOrders.receiver_id, tbl_ShipOrders.truck_id, tbl_ShipOrders.refused, " & _
"tbl_ShipOrders.ship_order_id, tbl_ShipOrders.sender_id, tbl_ShipOrders.provcred, Truck.Truck_No AS Truck, Truck.Truck_Registration AS TruckReg, " & _
"tbl_ShipOrders.loaddate, tbl_ShipOrders.Payment_id, Total.[sumofextended price] AS Total," & _
"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 Sender.custid = tbl_ShipOrders.Sender_ID) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON Receiver.custid = tbl_ShipOrders.Receiver_ID) " & _
"INNER JOIN qrycondetailsextendedtotals AS Total " & _
"ON Total.ship_ID = tbl_shipOrders.ship_order_ID) " & _
"INNER JOIN tbl_Truck_Details AS Truck " & _
"ON Truck.TruckID = tbl_ShipOrders.Truck_ID " & _
"WHERE tbl_ShipOrders.Loaddate Is Not Null " & _
"AND tbl_ShipOrders.Del=0 " & _
"AND tbl_ShipOrders.Payment_ID=4 " & _
"AND tbl_ShipOrders.delete =0 " & _
"AND tbl_ShipOrders.refused=0 " & _
"AND tbl_ShipOrders.Branch_ID = " & Branchid

If I remember correctly, I think the problem was with my bracketing but works ok at the moment.

Thanks again Boyd, nice to see you here as well as UA.