Microsoft Access-根据另一个查询的结果创建一个查询 [英] Microsoft Access - Creating a Query based on the Results of Another Query

问题描述

此查询生成客户ID的表:

This query generates a table of customer id's:

SELECT Transactions.customerId
FROM Transactions 
WHERE Transactions.purchaseDate <#9/1/2012#
GROUP BY Transactions.customerId
HAVING  Sum(Transactions.amountPaid)<>0;

此查询生成交易表:

SELECT Customers.firstName, Customers.lastName, Transactions.transactionId, Transactions.product, Transactions.purchaseDate, Transactions.amountPaid, Customers.customerId
FROM Customers INNER JOIN Transactions ON Customers.customerId = Transactions.customerId
WHERE (((Transactions.refundTransaction)=False));

我只想从第一个查询生成的用户中选择所有交易(第二个查询).

I want to select all of the transactions (second query) from only users generated by the first query.

推荐答案

使用MS Access，可以保存这两个查询，然后按查询名称将它们添加到查询设计窗口中.添加后，您可以通过将相关字段名称从一个查询拖放到另一个查询之间，在两个查询之间创建一个INNER JOIN.如果可以继续，下一步是将查询名称替换为派生表，即查询的sql内容.

Using MS Access, it is possible to save both of these queries and then add them by query name to the query design window. Once added, you can create an INNER JOIN between the two queries by dragging and dropping the relevant field name from one to the other. The next stage, if you can to move on, is to substitute the query name for a derived table, that is, the sql content of the query.

例如:

Query1

SELECT ID, CustName FROM Table1 WHERE SomeField > 2

Query2

SELECT CustID, TranName FROM Table2 WHERE SomeField > Date()

Query3

SELECT * FROM Query1 INNER JOIN Query2 ON Query1.ID = Query2.CustID

替换:

SELECT * FROM (
   SELECT ID, CustName 
   FROM Table1 
   WHERE SomeField > 2) As Q1
INNER JOIN (
   SELECT CustID, TranName 
   FROM Table2 WHERE SomeField > Date()) As Q2
ON Q1.ID = Q2.CustID

这篇关于Microsoft Access-根据另一个查询的结果创建一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！