Search code examples
sqlms-accessms-access-97

Does Microsoft Access 97 support derived tables?

I'm working on a query which is getting disgustingly large, and believe I can improve it by using a derived query; a more ornate and useful form of something like this:-

SELECT Id,
       RateId,
       FactorId
    FROM (SELECT Id,
                 RateId,
                 FactorId
              FROM SomeTable
              WHERE FactorId <> 0);

But when I try this it grizzles with an error message "Syntax Error in FROM Clause".

Before I start swearing at it to make it work, does Microsoft Access 97 support derived tables? If it doesn't, there's no point in continuing on these lines.

Solution

  • You probably need the old Access SQL sub-select syntax:

    SELECT Id,
       RateId,
       FactorId
    FROM [SELECT Id,
                 RateId,
                 FactorId
              FROM SomeTable
              WHERE FactorId <> 0]. AS T;