Syntax Error in FROM Clause

[Mike Labosh]

Somebody hit me with a cluebat, because I can't see it:

SELECT q.*, t.ProdSeriesCode
FROM quniMachineTypeForTNSImportWithoutProdSeriesCode q
INNER JOIN (
SELECT DISTINCT MTCode, ProdSeriesCode
FROM Tbl_HWProducts
) t
ON t.MTCode = q.MTCode

After clicking OK on the error message, Access 97 highlights the second
SELECT keword. PLEASE don't tell me Access 97 can't do subqueries?!?

 

[Douglas J. Steele]

As you suspect, Access 97 doesn't allow you to use SQL instead of a table or
query.

Create an intermediate query

SELECT DISTINCT MTCode, ProdSeriesCode
FROM Tbl_HWProducts

and save it as quniHWProducts

Change your query to:

SELECT q.*, t.ProdSeriesCode
FROM quniMachineTypeForTNSImportWithoutProdSeriesCode q
INNER JOIN quniHWProduct AS t
ON t.MTCode = q.MTCode

(FWIW, what you have isn't actually a subquery)

 

[Mike Labosh]

As you suspect, Access 97 doesn't allow you to use SQL instead of a table

or
query.

Create an intermediate query

SELECT DISTINCT MTCode, ProdSeriesCode
FROM Tbl_HWProducts

and save it as quniHWProducts

Change your query to:

SELECT q.*, t.ProdSeriesCode
FROM quniMachineTypeForTNSImportWithoutProdSeriesCode q
INNER JOIN quniHWProduct AS t
ON t.MTCode = q.MTCode

Aw, this sucks. The intermediate query worked fine, but it just seems
stupid.