suppress duplicates in formula

stkoontz
stkoontz used Ask the Experts™
on
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.

Snipit of report showing duplicates
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_id} <> Previous({adjustments.adjustment_id})" in the 'evaluate' formula

'Credit Total' field is a running total field with "{credits.credit_id} <> Previous({credits.credit_id})" in the 'evaluate' formula

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Am I correct you would like only one unique adjustment id?
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.

Author

Commented:
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

Commented:
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)
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
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

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The problem is credits and adjustments aren't really related except the they belong to the same account.
What you have is 3 adjustments and 2 credits so you end up with 6 records
There is no easy way to do a "distinct" sum since the adjustment ID or the credit id could be in any order

Try using a union query for the report.  You can then determine which column the amounts go in.  You will not be able to get them side-by-side as you have them above.

SELECT AccountTable.*, Adjustments.AdjusmentId, Adjustments.AdjustmentAmount, "A" as myType
FROM AccountTable INNER JOIN Adjustments ON AccountTable.CustomerId = Adjustments.CustomerId
UNION
SELECT AccountTable.*, Credits.CreditId, Credits.CreditAmount, "C" as myType
FROM AccountTable INNER JOIN CreditsON AccountTable.CustomerId = Credits.CustomerId


The other way would be to use subreports for one of the types or for both.  Here you can get them side by side as above and by using shared variables you can get the overall total.

mlmcc

Commented:
You are right about sorting. You have to sort on credit id. Then make make a running total:
set field to summarize to count adjustment id, evaluate on change of field adjustment id and reset on change of field credit id.

then you should get

7825.....22914....$100     1
7830.....22914....$100     2
8053.....22914....$100     3
7825.....23047....$309     1
7830.....23047....$309     2
8053.....23047....$309     3

Is that the desired situation sou can supress all but running total=1?


Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial