I have a report that selects Invoices and Credit notes for a particular range and shows zero for Credit notes for Invoices outside of the range (and visa versa). The problem is that where several credit notes exist for the same invoice in the same period, the invoice is duplicated and included in the totals. How can I show the duplicate as zero or exclude it from the totals?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Exclude Duplicate Values from Formula
- Thread starter chungfah
- Start date
chelseatech
Instructor
Your problem is actually the way you have joind the DB tables together.
Solution 1: Get the invoices in the main report, and use a subreport to get the Credits.
Solution 2: Use a SQL UNION join in a View to append the Credits and Invoices together to create a transaction table, and then join that to your Customer Masterfile. This requries a DB that supports SQL, so Access, Oracle, SQl server are fine, BTrieve, Paradox, etc. are not.
Editor and Publisher of Crystal Clear
Solution 1: Get the invoices in the main report, and use a subreport to get the Credits.
Solution 2: Use a SQL UNION join in a View to append the Credits and Invoices together to create a transaction table, and then join that to your Customer Masterfile. This requries a DB that supports SQL, so Access, Oracle, SQl server are fine, BTrieve, Paradox, etc. are not.
Editor and Publisher of Crystal Clear
- Status
Similar threads
- Locked
- Question
- Locked
- Question