WORKING WITH REVERSALS

[Angela]

Hi,

I'm working with a table in which I have following fileds.

DATE
NUM
AMOUNT
RECEIPT#
AMOUNT2

Basically have to return latest line against NUM after below
calculations.

In case of a reversal, a similar line is posted with different date
usually next day, a new receipt# and amount in either amount columns
is -ve.. for instance if it was 50, then reversal line becomes -50 in
either column.

In such case, numwise, the query should not return any line since all
transactions are now reversed.

In case of a third and correct entry, the query should return this
third line with its row details.

In case of another reversal of same, the query should be blank and so
on...

The problem is that I'm using max on date & last for all the fields
but still getting lines for which there are single reversal posted.
The query is unable to check the pairing.

What I want in the query is all single entries(those without
reversals), all those entries which go through reversals, calculate
the valid entry from those reversals by grouping them and getting the
final clean entry if any.

Examples:
DATE| NUM| AMOUNT| RECEIPT| AMOUNT2
------------------------------------------
1/10/2009 777 299 00019 0
1/10/2009 123 199 00024 0
1/10/2009 444 0 00026 199
3/10/2009 123 -199 00028 0

should get
1/10/2009 777 299 00019 0
1/10/2009 444 0 00026 199
--------------------------------------------
1/10/2009 777 299 00019 0
1/10/2009 123 199 00024 0
1/10/2009 444 0 00026 199
3/10/2009 123 -199 00028 0
3/10/2009 123 199 00031 0

should get
1/10/2009 777 299 00019 0
1/10/2009 444 0 00026 199
3/10/2009 123 199 00031 0
--------------------------------------------
1/10/2009 777 299 00019 0
1/10/2009 123 199 00024 0
1/10/2009 444 0 00026 199
3/10/2009 123 -199 00028 0
3/10/2009 123 199 00031 0
16/10/2009 123 -199 00045 0
17/10/2009 444 0 00046 -199

should get
1/10/2009 777 299 00019 0
---------------------------------------------
1/10/2009 777 299 00019 0
1/10/2009 123 199 00024 0
1/10/2009 444 0 00026 199
3/10/2009 123 -199 00028 0
3/10/2009 123 199 00031 0
16/10/2009 123 -199 00045 0
17/10/2009 444 0 00046 -199
21/10/2009 123 0 00068 199
21/10/2009 444 199 00096 0


should get
1/10/2009 777 299 00019 0
21/10/2009 123 0 00068 199
21/10/2009 444 199 00096 0

Hope the examples are clear enough.

Anyhelp is appreciated greatly.

Angela.

 

[Allen Browne]

Considering the possibility of a partial reversal (it could occur, even if
you don't think it should), I suggest you use a Totals query:

Group by Num
Max of [Receipt#]
Sum of Amount

If you then want to return other fields as well, you can save this query and
create another one based on this query as well as the original table. Join
[MaxOfReceipt#] to [Receipt#]

 

[Angela]

Well guys I did the following.. and email from Sinner. Thx.

I counted the NUM with repetition = 1.
I counted the NUM with repetition = 2 & 4.
I counted the NUM with repetition = 3 & 5.

Since 2 & 4 would mean the reversals cleared all previous and so to be
ignored.
Maxed the 3rd query & ignored the reversals by <>"reversed" and got
the latest of the the 3 or 5 transactions. Tested and confirmed.

Union of first query & third query yielded the required.

Will definitely try the above posts an logic..

Thanks once again for your kind replies. Much appreciated.

 

[Angela]

Ken the two amounts are:

If transaction is on cash, amount is in cash column and discount is
blank.

If transaction is on discount, amount is in discount column with
discount reason in another column and cash column is zero.

Likewise, if reversals are posted, its same treatment. Reversals are
mainly due to key-punch errors, wrong amount posting, human errors
etc.