suppress duplicates in formula
I have a database where I need to get totals from 2 different tables. There's a "credits" table and an "adjustments" table. Each has an amount column that I need to total up on the same report. The problem is that when I link them together, I get duplicate records.
Field explanations:
'adjustment-ID' is the unique identifier for the adjustments table.
'Credit-ID' is the unique identifier for the credit table.
'Total Adjustment Amount' is a running total field with "{adjustments.adjustment_i
'Credit Total' field is a running total field with "{credits.credit_id} <> Previous({credits.credit_i
If I sort by 'Credit-ID' then that column works, but throws off the Adjustment Amount. If I sort by Adjustment-ID the opposite is true.
The $-819.50 at the bottom right is this formula: "{#Adjustment Amount}-{#Credit Total}." Ultimately I need to subtract the total credits from the total adjustments.
The full report is grouped by name.
Any ideas on how to suppress the duplicates so the formulas calculate correctly?
Thanks,
Steve
Field explanations:
'adjustment-ID' is the unique identifier for the adjustments table.
'Credit-ID' is the unique identifier for the credit table.
'Total Adjustment Amount' is a running total field with "{adjustments.adjustment_i
'Credit Total' field is a running total field with "{credits.credit_id} <> Previous({credits.credit_i
If I sort by 'Credit-ID' then that column works, but throws off the Adjustment Amount. If I sort by Adjustment-ID the opposite is true.
The $-819.50 at the bottom right is this formula: "{#Adjustment Amount}-{#Credit Total}." Ultimately I need to subtract the total credits from the total adjustments.
The full report is grouped by name.
Any ideas on how to suppress the duplicates so the formulas calculate correctly?
Thanks,
Steve
ASKER
I think the answer to your first question would be 'yes' but here's a little more explanation...
I need the "total adjustment amount" to not use duplicate "adjustment IDs" in the calculation. (This is working in the example) and the "credit total" to not use duplicate "credit IDs" in the calculation. (This isn't working in the example)
The amounts that need to be shown are...
"Total Adj Amount" ($409.75 in the example)
"Credit Total" (Should be $409.75 if working correctly)
"Balance" (Should be $0.00)
Can you give me a little more explanation on your recommendation? How would a running total field count the unique IDs?
Thanks for the quick reply. I really appreciate it.
Steve
I need the "total adjustment amount" to not use duplicate "adjustment IDs" in the calculation. (This is working in the example) and the "credit total" to not use duplicate "credit IDs" in the calculation. (This isn't working in the example)
The amounts that need to be shown are...
"Total Adj Amount" ($409.75 in the example)
"Credit Total" (Should be $409.75 if working correctly)
"Balance" (Should be $0.00)
Can you give me a little more explanation on your recommendation? How would a running total field count the unique IDs?
Thanks for the quick reply. I really appreciate it.
Steve
So credit total must only sum the first adjustment id's?
to clarify on th running total fields:
make a new running total field, set field to summarize to count adjustment id's, evaluate on change of field adjustment id's and reset on change of field adjustment id's.
Now you should get 1,2 1,2 1,2 in your example.
with these figures you could suppress details or object (suppress where @running_total_XXX >1)
to clarify on th running total fields:
make a new running total field, set field to summarize to count adjustment id's, evaluate on change of field adjustment id's and reset on change of field adjustment id's.
Now you should get 1,2 1,2 1,2 in your example.
with these figures you could suppress details or object (suppress where @running_total_XXX >1)
ASKER
No. Credit total must only sum the unique "Credit IDs." In my example...
Credit total should sum
Credit ID Credit Amount
22,914 $100.00
23,047 $309.75
I tried your suggestion and only got 1's instead of 1, 2, etc. to show on the report. But even if it did work, wouldn't the Adjustment ID and the Credit ID have to both sorted in order to have the @running_total_XXX>1 suppression work?
Thanks,
Steve
Credit total should sum
Credit ID Credit Amount
22,914 $100.00
23,047 $309.75
I tried your suggestion and only got 1's instead of 1, 2, etc. to show on the report. But even if it did work, wouldn't the Adjustment ID and the Credit ID have to both sorted in order to have the @running_total_XXX>1 suppression work?
Thanks,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've had some database issues that have required I go in a completely different direction with this problem. If I'd have pursued either of your suggestions, I probably would have arrived at a solution.
So I'm splitting the points and closing the question.
Thanks,
Steve
So I'm splitting the points and closing the question.
Thanks,
Steve
Which amounts have to be shown and calculated of that ID?
I recommend you to use a running total field on the adjustment ID, which counts the ID's like 1,2,3 and then suppress fields or details which are > 1.
I have used such constructions very often. But maybe to get this working you will need several running totals and formula's. But for starts try this and clarify what you would like to get.