Access ODBC Driver Reporting Syntax Error in Valid SQL Query 1

[Robbo79]

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.

 

[Robbo79]

I think I've some progress but still stuck. I have remembered Access requires parentheses when doing multiple joins, so I have changed the SQL to the following:

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

Now I'm getting:

PHP Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation., SQL state 37000 in SQLExecDirect in C:\test.php on line 26

So I'm still stuck.

 

[Robbo79]

I spotted an error in the previous statement and corrected it to the following, but I still get the same error:

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

 

[Robbo79]

OK made some (small) progress but still getting stuck. I now have this:

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) INNER JOIN tblStockOut AS T2 ON T1.TillID = T2.TillID) INNER JOIN T2 ON T1.BranchID = T2.BranchID) INNER JOIN T2 ON T1.TransactionNumber = T2.TransactionNumber) INNER JOIN tblCustomer AS T3 ON T3.CustomerID = T2.CustomerID) WHERE T2.TillID = 'A12' AND T2.BranchID = 'WW' AND T2.TransactionNumber = 9181 AND T2.CustomerID = 35654

But I'm getting: PHP Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Join expression not supported., SQL state S1000 in SQLExecDirect in C:\test.php
on line 26

 

[SkipVought]

Inner joins can also be coded more simply, without all the set of parentheses...

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

Where T1.TillID            = T2.TillID
  AND T1.BranchID          = T2.BranchID
  AND T1.TransactionNumber = T2.TransactionNumber
  AND T3.CustomerID        = T2.CustomerID
  AND T2.TillID            = 'A12'
  AND T2.BranchID          = 'WW'
  AND T2.TransactionNumber = 9181
  AND T2.CustomerID        = 35654

Are you sure that the TransactionNumber & CustomerID are numeric data types?

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[Robbo79]

It looks like the simple answers are always the best. Thanks, SkipVought - that did the trick!