How to summarize fields from different levels?

[Jay10634]

I am trying to create a report to show a QTY for a value based on several different levels. These values come from three different records. Transaction level, a "location" level, and finally a Total value level. When I sum the "location" and compare it to the Total level, it comes out ok, but if I try to compare all three, so to exlude the line if they are all equal, then the value from the "location" seems to be counted once per transaction, which returns a large value that is incorrect.

Any ideas?

 

[synapsevampire]

By Level I assume that you mean group?

I think that including some sample data and expected output would help us help you.

It sounds like you want some groups, except perhaps for the total level.

Please post specifics.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

yes...I agree with SV...an example would definately help Jim Broadbent

 

[Jay10634]

Ok, let's see if I can break this down:

Each Item has unlimited locations. Each location can have many transactions (receipts, xfers, stock issues, etc). These values are stored in three records: Item Master, Item Locations, Item Trans.

I am trying to take the Sum of the QTYONHAND from the Item Trans and compare it to the Sum of the QTYONHAND from the Item Locations, and finally, compare the QTYONHAND on the Item Master. If any of the three don't match the other two, then show the item. If they all match, dont show the item.

So, lets say I have 10 On hand at the Master level, location A with 4, location B with 6. Location A had a Receipt of 5, Issue of 1(5-1=4). Location B had Receipt of 7, Issue of 1, (7-1=6). So, when I created my report, I am getting the location value from Loc A showing as 6, but it shows twice and Location B's value shows twice with a value of 7 twice. The total for the Item trans shows 10 (4+6), but the total for the locations shows as 26 (6+6 & 7+7=26).
Make sense?
Thanks.