| Author |
Message |
Odinsisfet
Newbie
Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
|
   Topic: Duplicate Values
Posted: 15 Dec 2009 at 7:19pm |
I am creating a report that uses the Sales Database for a class. I am including the InvoiceNum, InvoiceDate, and AmountBilled. I am grouping by Account Number and Invoice Number. When I add the payment amount field to the report I get duplicate invoice number fields with multiple payment amounts. How can I combine these payment amounts to reflect one payment amount? I need to subtract the payment amount from the billed amount to get the amount due field. I cannot do this effectively without combining all payment amount fields. Any help would be appreciated. Example: < ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"> <>
INUM IDATE AMOUNT PAY_AMT 211030518901
00101 12/16/2002 12:00:00AM
3,455.55 3,450.00
00102 1/18/2003 12:00:00AM
222.44 665.00
00103 3/4/2003
12:00:00AM 4,567.00 4,540.00
00202 12/26/2002 12:00:00AM
5,565.00 700.00
00203 1/30/2003 12:00:00AM
5,666.00 756.00
00204 3/7/2003 12:00:00AM
7,777.00 600.00
00204 3/7/2003 12:00:00AM
7,777.00 700.00
00205 3/23/2003 12:00:00AM
222.00 222.00
33347 11/13/2002 12:00:00AM
8,889.00 2,000.00
33347 11/13/2002 12:00:00AM
8,889.00 1,000.00
33347 11/13/2002 12:00:00AM
8,889.00 500.00
44455 2/28/2003 12:00:00AM
4,523.00 4,546.00
44456 3/16/2003 12:00:00AM
2,223.00 500.00
45646 4/17/2003 12:00:00AM
3,334.00 3,000.00
45646 4/17/2003 12:00:00AM
3,334.00 600.00
55544 1/19/2003 12:00:00AM
4,546.00 3,000.00
56342 12/21/2002 12:00:00AM
1,112.00 1,112.00
88844 12/24/2002 12:00:00AM
4,545.00 4,500.00
|
|
Jen
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 16 Dec 2009 at 10:58am |
Since this is for a class, I can't give you the exact answer. However, to point you in the right direction, you need to look at the Summary functions, in particular, SUM. You can use these in formulas in addition to inserting summaries directly into the report.
-Dell
|
|
|
|
IP Logged |
|
Odinsisfet
Newbie
Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 16 Dec 2009 at 12:28pm |
I already have a summary field that looks at the payment amount by the group invoice number. It will summarize the payment amount by invoice. I still have three fields in my details section with the exact same summarized amount under the same invoice number. How can I get it to only display one amount per invoice? I enev tried averaging the payment amount and then subtracting the sum form that. Still three fields when I only want one! Here is what it looks like now:
The duplicated fields I only want to display once, so my summary fields in my groop footer are accurate.
| INVOICE NUMBER |
INVOICE DATE |
AMOUNT |
Amount Paid |
AMOUNT DUE |
| 00101 |
12/16/02 |
$3,455.55 |
$3,450.00 |
$5.55 |
| 00102 |
1/18/03 |
$222.44 |
$665.00 |
$-442.56 |
| 00103 |
3/4/03 |
$4,567.00 |
$4,540.00 |
$27.00 |
| 00104 |
3/15/03 |
$7,776.00 |
$0.00 |
$7,776.00 |
| 00202 |
12/26/02 |
$5,565.00 |
$700.00 |
$4,865.00 |
| 00203 |
1/30/03 |
$5,666.00 |
$756.00 |
$4,910.00 |
| 00204 |
3/7/03 |
$7,777.00 |
$1,300.00 |
$6,477.00 |
| 00204 |
3/7/03 |
$7,777.00 |
$1,300.00 |
$6,477.00 |
| 00205 |
3/23/03 |
$222.00 |
$222.00 |
$0.00 |
| 23345 |
6/5/03 |
$4,445.00 |
$0.00 |
$4,445.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 33347 |
11/13/02 |
$8,889.00 |
$3,500.00 |
$5,389.00 |
| 44455 |
2/28/03 |
$4,523.00 |
$4,546.00 |
$-23.00 |
| 44456 |
3/16/03 |
$2,223.00 |
$500.00 |
$1,723.00 |
| 45646 |
4/17/03 |
$3,334.00 |
$3,600.00 |
$-266.00 |
| 45646 |
4/17/03 |
$3,334.00 |
$3,600.00 |
$-266.00 |
| 56342 |
12/21/02 |
$1,112.00 |
$1,112.00 |
$0.00 |
| 77777 |
7/25/03 |
$254.33 |
$0.00 |
$254.33 |
| 88844 |
12/24/02 |
$4,545.00 |
$4,500.00 |
$45.00 |
|
Customer Balance |
$75,789.32 |
29,391.00 |
|
Suppressing is not enough because it still sums the fields when suppressed. Thus, my totals are off.
Edited by Odinsisfet - 16 Dec 2009 at 12:30pm
|
|
Jen
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 16 Dec 2009 at 12:46pm |
On the Database menu, have you tried turning on "Select Distinct Records"?
-Dell
|
|
|
|
IP Logged |
|
Odinsisfet
Newbie
Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 16 Dec 2009 at 2:04pm |
|
Yes, I did try that. The workaround I found is to place the information from the details section into the group footer. It doesn't duplicate the data then.
|
|
Jen
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 17 Dec 2009 at 6:22am |
But your totals are still off, correct?
If so, you have to create a formula that eliminates the duplicates. It will look something like this:
If ({table.invoice#} = previous({table.invoice#})) and ({table.amount}) = previous({table.amount})) then 0 else {table.amount}
You then do the summary on this formula instead of on the field. This basically looks to see if the previous record has the same invoice number and amount as the current one, which makes the current one a duplicate. If it's a duplicate, then set the value to 0 so that it's not added in more than once.
-Dell
|
|
|
|
IP Logged |
|
Odinsisfet
Newbie
Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 17 Dec 2009 at 7:33am |
Actually, all of my totals were correct. I did a running total on the Billed amount field, a summary on the Amount paid field and then a formula on the amount due field that took the running total-amount paid summary. They all came out with appropriate numbers. I submitted the assignment and got an A, so i figured it was all good!
Thank you for your help!
Edited by Odinsisfet - 17 Dec 2009 at 7:34am
|
|
Jen
|
|
IP Logged |
|
kmeistering
Newbie
Joined: 19 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 13 Oct 2010 at 5:07am |
|
I have used this solution to help me in a report, and it worked to eliminate the duplicates, however, now I cannot sum the fields because the formula is a second pass formula. I need to eliminate the duplicates and sum the fields - can someone help?!?
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 13 Oct 2010 at 5:18am |
Try using a running total instead of a sum. Set it to evaluate based on a formula, using the same logic of looking at the previous record outlined above - using the example above the formula would look something like this:
{table.invoice} <> previous({table.invoice}) or {table.amount} <> previous({table.amount})
You don't need the "If...then..." contstruct because this formula just needs to evaluate to True or False.
-Dell
|
|
|
|
IP Logged |
|
kmeistering
Newbie
Joined: 19 May 2010
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 13 Oct 2010 at 5:29am |
|
If you were right here, I would kiss you.
|
|
IP Logged |
|
|
|
