CODEWITHSUNDEEP
sqlms-access
Srihari
Srihari

Reputation: 2429

MS Access Error: '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

My code is 

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 Table contain Id, Invoice No, Customer name, Total Paid, Balance Amount, Payment Date, Payment Amount, Payment Type,Payment Remarks, InvoiceStatus.

This is my tablePayment Table

How to get InvoiceStatus from this table ??

Upvotes: 1

Views: 6137

Answers (1)

Bohemian
Bohemian

Reputation: 424983

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 ...

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

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.

Upvotes: 2

Related Questions