Limit query result on report to one instance

G

Guest

I have a join query I use to pull information for statements. I would like
to include a description from the first occurance only of the invoice
details, but when I run the report, it reflects as many lines as there are
line items and posts (and adds) the invoice total as many times as there are
line items.
Here is my 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;
and here is the result:
12-21-2004 278 12-21-2004 278


Customer Name
Address
City State Zip
Balance Forward: ($233.03)
12/5/2004 42657 BEARING $86.00 ($147.03)
12/5/2004 42657 Cup $86.00 ($61.03)
12/5/2004 42657 RIM $86.00 $24.97
12/5/2004 42664 BEARING $48.93 $73.90
12/5/2004 42664 Cross Kit $48.93 $122.83
12/5/2004 42664 CUP- BEARING $48.93 $171.76
12/5/2004 42664 FLUID GEAR $48.93 $220.69
12/5/2004 42664 LABOR $48.93 $269.62
Any one have any idea how I can pull this and not have it repeat?
Thank You!
 
A

Allen Browne

Leave tblInvoiceDetails out of the query, and use a subquery to get the
first description from tblInvoiceDetails. If you are not sure how to do
that, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Normally you would set up the invoice amount in each line item, i.e. in
tblInvoiceDetails not in the main invoice table. This would also solve the
problem.

You may find it easier to use a subreport in the lngCustomerNumber group
footer section to list the payments, instead of the UNION query, but that's
up to you.
 
G

Guest

I get your line of thought, but when I tried this, I still can not limit the
number of results. If there are 4 items, it repeats 4 times. For each item,
it is an autonumber so I can't specify it that way. The description is
always different. I need to specify 1 record only for lngInvoiceDetailID for
every instance of lngInvoiceID. Any more suggestions? Thanks a lot for your
time!

Allen Browne said:
Leave tblInvoiceDetails out of the query, and use a subquery to get the
first description from tblInvoiceDetails. If you are not sure how to do
that, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Normally you would set up the invoice amount in each line item, i.e. in
tblInvoiceDetails not in the main invoice table. This would also solve the
problem.

You may find it easier to use a subreport in the lngCustomerNumber group
footer section to list the payments, instead of the UNION query, but that's
up to you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Scheetz LLC said:
I have a join query I use to pull information for statements. I would like
to include a description from the first occurance only of the invoice
details, but when I run the report, it reflects as many lines as there are
line items and posts (and adds) the invoice total as many times as there
are
line items.
Here is my 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;
and here is the result:
12-21-2004 278 12-21-2004 278


Customer Name
Address
City State Zip
Balance Forward: ($233.03)
12/5/2004 42657 BEARING $86.00 ($147.03)
12/5/2004 42657 Cup $86.00 ($61.03)
12/5/2004 42657 RIM $86.00 $24.97
12/5/2004 42664 BEARING $48.93 $73.90
12/5/2004 42664 Cross Kit $48.93 $122.83
12/5/2004 42664 CUP- BEARING $48.93 $171.76
12/5/2004 42664 FLUID GEAR $48.93 $220.69
12/5/2004 42664 LABOR $48.93 $269.62
Any one have any idea how I can pull this and not have it repeat?
Thank You!
Click to expand...
Click to expand...
 
A

Allen Browne

If your query is giving you a row for every record in the tblInvoiceDetails
table, then you must still have the tblInvoiceDetails table in your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Scheetz LLC said:
I get your line of thought, but when I tried this, I still can not limit
the
number of results. If there are 4 items, it repeats 4 times. For each
item,
it is an autonumber so I can't specify it that way. The description is
always different. I need to specify 1 record only for lngInvoiceDetailID
for
every instance of lngInvoiceID. Any more suggestions? Thanks a lot for
your
time!

Allen Browne said:
Leave tblInvoiceDetails out of the query, and use a subquery to get the
first description from tblInvoiceDetails. If you are not sure how to do
that, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Normally you would set up the invoice amount in each line item, i.e. in
tblInvoiceDetails not in the main invoice table. This would also solve
the
problem.

You may find it easier to use a subreport in the lngCustomerNumber group
footer section to list the payments, instead of the UNION query, but
that's
up to you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Scheetz LLC said:
I have a join query I use to pull information for statements. I would
like
to include a description from the first occurance only of the invoice
details, but when I run the report, it reflects as many lines as there
are
line items and posts (and adds) the invoice total as many times as
there
are
line items.
Here is my 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;
and here is the result:
12-21-2004 278 12-21-2004 278


Customer Name
Address
City State Zip
Balance Forward: ($233.03)
12/5/2004 42657 BEARING $86.00 ($147.03)
12/5/2004 42657 Cup $86.00 ($61.03)
12/5/2004 42657 RIM $86.00 $24.97
12/5/2004 42664 BEARING $48.93 $73.90
12/5/2004 42664 Cross Kit $48.93 $122.83
12/5/2004 42664 CUP- BEARING $48.93 $171.76
12/5/2004 42664 FLUID GEAR $48.93 $220.69
12/5/2004 42664 LABOR $48.93 $269.62
Any one have any idea how I can pull this and not have it repeat?
Thank You!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

union query problem 1

Top