M
MariK
I have the following union query that I use to print statements at the end of the month. I wanted to add the description line from the Invoice/Payment so the customer knows what the charge was for. It works fine if there is only 1 line, however the Invoices at times have several lines. I want to limit the results of the query to either only print the first description from the invoice or, if there is more then 1 line, print the text INVOICE. I am fairly new to this so there may be a better way then this. Would appreciate any input
SQL
SELECT DISTINCTROW tblInvoices.lngCustomerNumber, tblInvoices.dtmInvoiceDate, tblInvoices.lngInvoiceID,IIF(COUNT.[tblInvoiceDetails.strInvoiceDescription]>1, "INVOICE",[tblInvoiceDetails.strInvoiceDescription])
IIf([lngJournalNumber]=116,[curTotalAmount],0) AS Charges, IIf([lngJournalNumber]=110,[curAmountReceived],0) AS Cas
FROM tblCUSTOMER INNER JOIN (tblInvoices INNER JOIN tblInvoiceDetails ON tblInvoices.lngInvoiceID = tblInvoiceDetails.lngInvoiceID) ON tblCUSTOMER.lngCustomerNumber = tblInvoices.lngCustomerNumbe
UNION SELECT tblPayments.CustomerID, tblPayments.PaymentDate, tblPayments.PaymentID, tblPaymentDetails.strDescription, tblPayments.PaymentAmount, NUL
FROM tblPayment
ORDER BY tblInvoices.dtmInvoiceDate
SQL
SELECT DISTINCTROW tblInvoices.lngCustomerNumber, tblInvoices.dtmInvoiceDate, tblInvoices.lngInvoiceID,IIF(COUNT.[tblInvoiceDetails.strInvoiceDescription]>1, "INVOICE",[tblInvoiceDetails.strInvoiceDescription])
IIf([lngJournalNumber]=116,[curTotalAmount],0) AS Charges, IIf([lngJournalNumber]=110,[curAmountReceived],0) AS Cas
FROM tblCUSTOMER INNER JOIN (tblInvoices INNER JOIN tblInvoiceDetails ON tblInvoices.lngInvoiceID = tblInvoiceDetails.lngInvoiceID) ON tblCUSTOMER.lngCustomerNumber = tblInvoices.lngCustomerNumbe
UNION SELECT tblPayments.CustomerID, tblPayments.PaymentDate, tblPayments.PaymentID, tblPaymentDetails.strDescription, tblPayments.PaymentAmount, NUL
FROM tblPayment
ORDER BY tblInvoices.dtmInvoiceDate