union query problem

[Marik]

I have aproblem with the following union query:
SELECT DISTINCTROW tblInvoices.lngCustomerNumber,
tblInvoices.dtmInvoiceDate, tblInvoices.lngInvoiceID,
tblInvoiceDetails.strInvoiceDescription,
IIf([lngJournalNumber]=116,[curTotalAmount],0) AS
Charges, IIf([lngJournalNumber]=110,
[curAmountReceived],0) AS Cash
FROM tblCUSTOMER INNER JOIN (tblInvoices INNER JOIN
tblInvoiceDetails ON tblInvoices.lngInvoiceID =
tblInvoiceDetails.lngInvoiceID) ON
tblCUSTOMER.lngCustomerNumber =
tblInvoices.lngCustomerNumber


UNION SELECT tblPayments.CustomerID,
tblPayments.PaymentDate, tblPayments.PaymentID,
tblPaymentDetails.strDescription,
tblPayments.PaymentAmount, NULL
FROM tblPayments
ORDER BY tblInvoices.dtmInvoiceDate;
I use this to print EndOfMonth statements. It is part of
another query pulling customer info. The problem came up
when I put the "description" fields in. I wanted it to
print a description of the transaction on the statement,
but if they purchesed more then 1 item, it prints all of
them. Is there a way I can limit it to use only the
first entry of the invoice?
Thanks for any ideas!

 

[Tom Ellison]

Dear Marik:

Here are some ways I'd suggest for handling this:

- You can supress duplicates within the report.

- You can build the report with a subreport to show the detailed
portion of the invoice separately.

- To handle this within the query, use a subquery that counts the
number of lines within the invoice that precede the current line, and
set the Description to NULL when this is not 0.

Of these, the second one is recommended, while the first is probably
the quick and easy way.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts