title: A select query selecting a select statement
body: <p>I don't even know if I am doing this query the right way.
  There is a <code>Sandwiches</code> table that has some 7 fields and 2 of them are comboboxes (<code>Type</code> and <code>Bread</code>).</p>
  
  <p>So I made a query that combines all of the comboboxes values into one query, like this:</p>
  
  <pre><code>SELECT TypesAndBreads.TBName, TypesAndBreads.Type
  FROM (SELECT [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type
      FROM [Sandwiches Types]
  UNION ALL
      SELECT Breads.Bread As TBName, "Bread" As Type 
      FROM Breads)  AS TypesAndBreads;
  </code></pre>
  
  <p>I get the flat values of the tables now I want to count all the sandwiches under each <code>TypesAndBreads.TBName</code>. I have this, just to make sure it works with all the Sandwiches:</p>
  
  <pre><code>SELECT TypesAndBread.Type, TypesAndBread.TBName,
         (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
          FROM Sandwiches) As SandwichCount
  FROM TypesAndBread;
  </code></pre>
  
  <p>But I want to reference the current Type and TBName inside the subquery. Something like this:</p>
  
  <pre><code>SELECT TypesAndBread.Type, TypesAndBread.TBName,
         (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
          FROM Sandwiches
          WHERE Sandwiches.[TypesAndBread.Type] = Sandwiches.[TypesAndBread.TBName]) As SandwichCount
  FROM TypesAndBread;
  </code></pre>
  
  <p>But of course this doesn't work. I didn't think it will, just thought of giving it a try. I was thinking of maybe constructing the query with VBA when they open the Report that this query is going to be based of.</p>
  
  <p><strong>So I guess my question is:</strong> Is there a way to reference the current selected fields in a subquery? Or is there a different way to approach this?</p>
  
  <p>Thanks for the help</p>
  
  <p><strong>EDIT:</strong>
  My table structure is like this:</p>
  
  <p><code>Sandwiches</code>'s fields</p>
  
  <pre><code>| SandwichID | Name | Date Added | Chef | Sandwich Type | Bread | Reviewed By |
  </code></pre>
  
  <p>where <code>Sandwich Type</code> and <code>Bread</code> are Lookup fields for these tables:</p>
  
  <p><code>Sandwiches Types</code>'s fields</p>
  
  <pre><code>| Sandwich Type |
  </code></pre>
  
  <p><code>Breads</code>'s fields</p>
  
  <pre><code>| Bread |
  </code></pre>
  
  <p>The TypesAndBreads query combined the Sandwiches Types and Breads tables, but the reason for that is so that I can get the count of all the sandwiches that have that Type or bread. A result like this:</p>
  
  <pre><code>+=============================================+
  |      Type     |    TBName   | SandwichCount |
  +=============================================+
  | Sandwich Type | Turkey Club |            10 |
  | Bread         | Italian     |             5 |
  | Bread         | Garlic      |             8 |
  +---------------------------------------------+
  </code></pre>
  
  <p>the example result's first row basicly says there are 10 sandwiches in record with the Sandwich Type field equal to Turkey Club.</p>
  
  <p>I hope that explains it better.</p>