Hi,
Here is the query I am trying to execute. As far as I can tell it is a perfectly valid SQL query:
SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description, T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM tblPayment AS T1, tblStockOut AS T2, tblCustomer AS T3 INNER JOIN T2 ON T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654
This causes the Access ODBC Driver to report:
PHP Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID'., SQL state 37000 in SQLExecDirect in C:\test.php on line 26
Here's what I'm trying to do:
I have three tables: tblPayment, tblStockOut & tblCustomer. Three fields are common to both tblPayment and tblStockOut - TillID, BranchID & TransactionNumber. These three fields are indexed but are not primary keys, they all have duplicate entries. However, there can only be one TillID, BranchID and TransactionNumber that match, i.e. only one record in each table will have a TillID of A12, a BranchID of WW and a TransactionNumber of 9181. I'm also using the CustomerID from the record found in tblStockOut to fetch the customers' details from tblCustomer. tblPayment and tblStockOut are not related to each other inside Access (actually none of the tables in the database are).
I cannot make any changes to the database, it is part of a third-party off-the-shelf application and uses the Access 97 runtime.
Here is the query I am trying to execute. As far as I can tell it is a perfectly valid SQL query:
SELECT T1.PaymentNumber, T1.TillID, T1.BranchID, T1.TransactionNumber, T1.Payment, T1.CreationDate, T2.SalesNumber, T2.CustomerID, T2.PLU, T2.Description, T2.Retail, T3.FirstName, T3.LastName, T3.Street, T3.District, T3.Town, T3.County, T3.Postcode FROM tblPayment AS T1, tblStockOut AS T2, tblCustomer AS T3 INNER JOIN T2 ON T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654
This causes the Access ODBC Driver to report:
PHP Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.TillID = T2.TillID INNER JOIN T2 ON T1.BranchID = T2.BranchID INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber INNER JOIN T2 ON T3.CustomerID = T2.CustomerID'., SQL state 37000 in SQLExecDirect in C:\test.php on line 26
Here's what I'm trying to do:
I have three tables: tblPayment, tblStockOut & tblCustomer. Three fields are common to both tblPayment and tblStockOut - TillID, BranchID & TransactionNumber. These three fields are indexed but are not primary keys, they all have duplicate entries. However, there can only be one TillID, BranchID and TransactionNumber that match, i.e. only one record in each table will have a TillID of A12, a BranchID of WW and a TransactionNumber of 9181. I'm also using the CustomerID from the record found in tblStockOut to fetch the customers' details from tblCustomer. tblPayment and tblStockOut are not related to each other inside Access (actually none of the tables in the database are).
I cannot make any changes to the database, it is part of a third-party off-the-shelf application and uses the Access 97 runtime.