vba 为另一个查询的每个结果运行查询 - Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6961137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run Query for each result of another query - Access
提问by Alec
I am trying to use the results of another query to use as a criteria for another. In my specific example, I might have four houses that are 'A', 'B', 'C', 'D' (the unique values of a field in a table called Homes).
我正在尝试使用另一个查询的结果作为另一个查询的条件。在我的具体示例中，我可能有四个房子，分别是“A”、“B”、“C”、“D”（称为 Homes 的表中字段的唯一值）。
I want to go through another query and say for each house type, what percent of residents (in Residents table) are married, which I want to do by using Count() to count the number for eachHome type.
我想通过另一个查询并说对于每种房屋类型，有多少居民（在居民表中）已婚，我想通过使用 Count() 来计算每种房屋类型的数量。
Do I need to loop through the results using VBA? Asking on a higher level, is there a way to use the results from a query as inputs into another - more than just limit the results of the new query to the results of the prior query?
我是否需要使用 VBA 遍历结果？在更高层次上询问，有没有办法将查询的结果用作另一个查询的输入 - 不仅仅是将新查询的结果限制为先前查询的结果？
Edit:
编辑：
In semi-pseudo code:
在半伪代码中：
For each (result of previous query) Do
New query WHERE field1 = (row of previous query)
End Do
What I am trying to ask, is there a way to accomplish this in Access using SQL? Or is this something that has to be done in VBA?
我想问的是，有没有办法使用 SQL 在 Access 中完成此操作？或者这是必须在VBA中完成的事情？
I know that if it can be done in SQL that would be the best performing and best practice, but I'm relatively inexperienced in SQL and online resources aren't always helpful because Access has it's own particular flavor of SQL.
我知道如果它可以在 SQL 中完成，那将是最佳性能和最佳实践，但我在 SQL 方面相对缺乏经验，而且在线资源并不总是有帮助，因为 Access 有它自己特殊的 SQL 风格。
回答by Taryn
Since you are using VBA to run this, you can loop through your recordsets and yes you can use a value from one query in the next query. There are alot of resources out there to help.
由于您使用 VBA 来运行它，因此您可以遍历记录集，是的，您可以在下一个查询中使用一个查询中的值。有很多资源可以提供帮助。
回答by Brian - TechToolbox
To answer your general question, yes there is. You can do a nested query i.e. select column a from table a where column a = (select column b from table b where column b=x)
要回答您的一般问题，是的。您可以执行嵌套查询，即从表 a 中选择列 a，其中列 a =（从表 b 中选择列 b，其中列 b=x）
You can go as many levels deep as you want, but the caveat is the nested query can only return one column and with a specific answer set. You can also use select statements as your columns i.e
您可以根据需要深入到多个级别，但需要注意的是嵌套查询只能返回一列并具有特定的答案集。您还可以使用 select 语句作为您的列，即
select (select column b from table b) col b from table a ..... Not the exact syntax but I would have to dig out some examples from an old project to find that.
select (select column b from table b) col b from table a ..... 不是确切的语法，但我必须从旧项目中挖掘一些示例才能找到它。
Nested queries are useful, but for the level of precision you are looking for, a stored procedure or a view is probably a better option. Just for ease of use, I would look at creating a view of the data that you want and then querying from that to start with. More flexible than a nested query.
嵌套查询很有用，但对于您正在寻找的精度级别，存储过程或视图可能是更好的选择。只是为了便于使用，我会考虑创建您想要的数据视图，然后从该视图开始查询。比嵌套查询更灵活。
