SQL 一个选择查询选择一个选择语句

A select query selecting a select statement

提问by Tony L.

I don't even know if I am doing this query the right way. There is a Sandwichestable that has some 7 fields and 2 of them are comboboxes (Typeand Bread).

我什至不知道我是否以正确的方式执行此查询。有一个Sandwiches表有大约 7 个字段，其中 2 个是组合框（Type和Bread）。

So I made a query that combines all of the comboboxes values into one query, like this:

所以我做了一个查询，将所有的组合框值组合到一个查询中，如下所示：

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;

I get the flat values of the tables now I want to count all the sandwiches under each TypesAndBreads.TBName. I have this, just to make sure it works with all the Sandwiches:

我现在得到了表格的平面值，我想计算每个TypesAndBreads.TBName. 我有这个，只是为了确保它适用于所有三明治：

SELECT TypesAndBread.Type, TypesAndBread.TBName,
       (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM Sandwiches) As SandwichCount
FROM TypesAndBread;

But I want to reference the current Type and TBName inside the subquery. Something like this:

但我想在子查询中引用当前的 Type 和 TBName。像这样的东西：

SELECT TypesAndBread.Type, TypesAndBread.TBName,
       (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM Sandwiches
        WHERE Sandwiches.[TypesAndBread.Type] = Sandwiches.[TypesAndBread.TBName]) As SandwichCount
FROM TypesAndBread;

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.

但这当然行不通。没想到，就想试一试。当他们打开此查询将基于的报告时，我正在考虑使用 VBA 构建查询。

So I guess my question is:Is there a way to reference the current selected fields in a subquery? Or is there a different way to approach this?

所以我想我的问题是：有没有办法在子查询中引用当前选定的字段？或者有没有不同的方法来解决这个问题？

Thanks for the help

谢谢您的帮助

EDIT:My table structure is like this:

编辑：我的表结构是这样的：

Sandwiches's fields

Sandwiches的字段

| SandwichID | Name | Date Added | Chef | Sandwich Type | Bread | Reviewed By |

where Sandwich Typeand Breadare Lookup fields for these tables:

其中Sandwich Type和Bread是这些表的查找字段：

Sandwiches Types's fields

Sandwiches Types的字段

| Sandwich Type |

Breads's fields

Breads的字段

| Bread |

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:

TypesAndBreads 查询结合了 Sandwiches Types 和 Breads 表，但这样做的原因是我可以获取具有该类型或面包的所有三明治的数量。结果是这样的：

+=============================================+
|      Type     |    TBName   | SandwichCount |
+=============================================+
| Sandwich Type | Turkey Club |            10 |
| Bread         | Italian     |             5 |
| Bread         | Garlic      |             8 |
+---------------------------------------------+

the example result's first row basicly says there are 10 sandwiches in record with the Sandwich Type field equal to Turkey Club.

示例结果的第一行基本上说有 10 个三明治在记录中，三明治类型字段等于土耳其俱乐部。

I hope that explains it better.

我希望这能更好地解释它。

回答by Quassnoi

Not sure if Access supports it, but in most engines (including SQL Server) this is called a correlated subquery and works fine:

不确定 Access 是否支持它，但在大多数引擎（包括SQL Server）中，这称为相关子查询并且工作正常：

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

This can be made more efficient by indexing Typeand Breadand distributing the subqueries over the UNION:

这可以通过索引更有效率Type，并Bread和分布在子查询UNION：

SELECT  [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Type = [Sandwiches Types].[Sandwich Type]
        )
FROM    [Sandwiches Types]
UNION ALL
SELECT  [Breads].[Bread] As TBName, "Bread" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Bread = [Breads].[Bread]
        )
FROM    [Breads]

回答by Tony L.

I was over-complicating myself. After taking a long break and coming back, the desired output could be accomplished by this simple query:

我把自己弄得太复杂了。经过长时间的休息并回来后，可以通过这个简单的查询来完成所需的输出：

SELECT Sandwiches.[Sandwich Type], Sandwich.Bread, Count(Sandwiches.[SandwichID]) AS [Total Sandwiches]
FROM Sandwiches
GROUP BY Sandwiches.[Sandwiches Type], Sandwiches.Bread;

Thanks for answering, it helped my train of thought.

感谢您的回答，它帮助了我的思路。