MS Access错误:“此子查询最多可以返回一条记录" [英] MS Access Error: 'At most one record can be returned by this subquery'

问题描述

am尝试连接3个表，但出现错误At most one record can be returned by this subquery

am try to connect 3 tables but am getting error At most one record can be returned by this subquery

我的代码是

SELECT InvoiceNumber, 
       Terms(SELECT PaymentTerms 
               FROM PSD_customerPaymentTerms 
              WHERE PSD_customerPaymentTerms.PTId = NewInvoice_1.Terms
             ) AS Terms, 
       InvoiceDate, 
       OurQuote, 
       SalesPerson(SELECT FirstName 
                     FROM Employee 
                    WHERE Employee.EmployeeId = NewInvoice_1.SalesPerson
             ) AS SalesPerson, 
       CustomerName(SELECT CustomerName 
                      FROM Customer 
                     WHERE Customer.CustomerId = NewInvoice_1.CustomerName
             ) AS CustomerName, 
       OrderNumber, 
       GrandTotal, 
       (SELECT SUM(PaymentAmount) 
          FROM Payment_Receipt 
         WHERE Payment_Receipt.InvoiceNumber=NewInvoice_1.InvoiceNumber
             ) AS AmountPaid, 
       GrandTotal-IIf(AmountPaid Is Null,0,AmountPaid) AS AmountDue, 
       (SELECT InvoiceStatus 
          FROM Payment_Receipt 
         WHERE Payment_Receipt.InvoiceNumber=NewInvoice_1.InvoiceNumber
             ) AS Status -- Error getting after adding this line.
          FROM NewInvoice_1;

Payment_Receipt表包含ID，发票编号，客户名称，已付款总额，余额金额，付款日期，付款金额，付款类型，付款备注，InvoiceStatus.

Payment_Receipt Table contain Id, Invoice No, Customer name, Total Paid, Balance Amount, Payment Date, Payment Amount, Payment Type,Payment Remarks, InvoiceStatus.

这是我的表

如何从此表中获取InvoiceStatus?

How to get InvoiceStatus from this table ??

推荐答案

解决此问题的一种通用方法是通过在列上使用max()强制子查询返回一行:

One general way to solve this problem is to force the subquery to return one row by using max() on the column:

select max(someColumn)
from someTable
where ...

如果您的数据中where子句有多行.

In case your data has multiple rows for the where clause.

虽然这种方法可以使您的查询正常工作，但可能无法提供所需的结果. where子句需要工作的可能性更大.也就是说，它在诊断问题时非常有用，特别是如果您不确定是哪个子查询导致了问题，您可以一次删除一个子查询的更改.

While this approach will get your query working, it may not give the results you want. More likely the where clause needs work. That said, it can be very useful when diagnosing the problem, especially if you aren't sure which subquery is causing the problem you can remove the change one subquery at a time.

这篇关于MS Access错误:“此子查询最多可以返回一条记录"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！