title: How to use a table alias with a Union statement in Access?
body: <p>In an unpivoting operation, I would like the following:</p>
  
  <pre><code>SELECT A, B, C FROM [complex joins/where clause] As DerivedTable
  UNION
  SELECT A, B, D FROM DerivedTable
  UNION
  SELECT A, B, E FROM DerivedTable
  ...
  </code></pre>
  
  <p>but it complains that <code>DerivedTable</code> cannot be found (I use a derived table so that <code>[complex joins/where clause]</code> doesn't have to be evaluated again and again thereby slowing things).</p>
  
  <p>I know I can simply create a new query called <code>DerivedTable</code> to represent <code>[complex joins/where clause]</code> but</p>
  
  <ul>
  <li><p>The above SQL is passed from Excel - I'd rather not have to open the database to create a new query prior to running the above</p></li>
  <li><p>The <code>[complex joins/where clause]</code> is generated dynamically, and changes from user to user, two of which may be running the above SQL at the same time.</p></li>
  </ul>