Repeating information on report fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jburris@estimating.org - 20 Feb 2006 06:58 GMT

In my current database, I have a single table which contains order
information such as product, unit price and payment. The payment for
each product is entered in and subtotals and totals are calculated
fields on reports. With this setup our customers pay for each product
in full.

I am trying to divide the table to move payment information to a
related table such that customers can make partial or installment
payments. I managed to divide the tables just fine but all of my
reports then began to show duplicated detail information. If a
customer ordered two products, then each of the products is listed two
time, (or three for three . . . )

When I delete the payment table from the query builder of the report,
the information doesn't repeat anymore, but of course I cannot send out
invoices without the info in the payment table. How do you stop the
repeating details in reports?

Thanks in advance for help.
Judy

Reply to this Message

Anthony England - 20 Feb 2006 10:37 GMT

> In my current database, I have a single table which contains order
> information such as product, unit price and payment. The payment for
[quoted text clipped - 16 lines]
> Thanks in advance for help.
> Judy

You haven't said what you would like to appear on the report. One line per
product with the sum of the payments against each, or do you want to see the
complit listing of all payments.
Are you really listing payments against products? Normally you have an
order which may have many products and after the invoice is raised, you can
make part payments against the invoice, rather than the individual products.
But if that's how you want it, then fine.
In any case, the answer to your question may well be subreports - have you
used these?

Reply to this Message

jburris@estimating.org - 27 Feb 2006 02:24 GMT

Thanks so much for your reply. You were right.

To answer your questions, yes all products were paid for in full and
were listed against the products in the original db but I wanted to
redesign such that partial payments could be made, so the payment info
was seperated from the orders into another, related table.

So, my report (invoice) listed all things ususal such as name, address
....

The the detail section listed all products ordered, quantity, price,
discount; but as long as the report's underlying query had the payment
table in it, then the products ordered were repeated in the detail
section X number of times. Additionally, calculated payment fields
were also processing the payments X number of times. I haven't the
faintest clue as to why but, X=number of items ordered.

A subreport containing the payment information solved all problems. I
didn't want anything from the subreport to show in the invoice so I set
Visible=Off on all items in the subreport. I calculated a total in the
subreport footer, then called that value to the main report.

Thanks again Anthony!

Judy

Reply to this Message