Help W/A Query Using an Inverse Function

[jmaitri]

Hi!



Am relatively new to Access (using Access 2007) and have a project at work that even has experienced users stumped.

We are accountants and here’s what we are trying to do:

1) We have a huge dbase that has a lot of accrual & their reversals. Example of what I mean: 1 transaction = $4.35 (the accrual). 1 transaction = -$4.35 (the reversal). The net effect of these two transactions is that they equal zero.

2) Each accrual has the same journal_id: ESPACC
Each reversal also has the same journal_id: ESPACCX

4) We have 8 paired journal_id types for these accruals and their reversals. Eg:

1 Pair =
ESPACC
ESPACCX

1Pair =
ESEACC
ESEACCX

3) What we need to do is delete all these zero balance accruals and reversals from a table we are using.

What we would like to do is run a delete query where we see each matching accrual and its paired reversal. Eg:

ESPACC $4.35
ESPACCX -$4.35
ESEACC $9.43
ESEACCX -$9.43

And so on.

Since we also have negative value standalone transactions for transfers, we cannot eliminate those transactions.

While we have gotten queries to work, we have not been able to pair our accruals with their reversals, like the example above, so that we can verify that we are ONLY deleting zero balance transactions.

It struck me that using an inverse function on the numbers is the way to go since these accruals and reversals are inverses of each other. However, I’m stumped on how to do that in Access!

Any help will be greatly appreciated!
Jay

[ajetrumpet]

hello jm,

I am also an accountant, but more of a computer geek than an accountant I would say. Just finishing up the masters work in for an MSA.

But at any rate, this is an interesting problem. After tinkering with it, thinking that one query would work became a little too tough. So in a double-stacked query scenario, here's one solution:

QUERY1:

[CODE]SELECT table.[code], table.[trans]
FROM table
WHERE (((table.[trans])=-Abs([trans])) AND ((InStr(

,"x"))>0));

QUERY2:

[CODE]select table.* from table where

(dcount("code", "QUERY1", "[code] = '" & [code] & "' & 'x'") = 1

AND dcount("trans", "QUERY1", "[trans] = " & -abs([trans]) & "

AND [code] = '" &

 & "' & 'x'") = 1)

QUERY 3 (Final):

[CODE]DELETE table.* FROM table WHERE

((code in (select code from QUERY2) and trans in (select trans from QUERY2)) OR

(left([code], len(

) - 1) in (select code from QUERY2) AND 

([trans] + (abs([trans]) * 2)) in (select trans from QUERY2)))

There's obviously a better way than this somewhere. One way would be to write a small vb procedure, but most people don't write code. So going step by step through the query process would probably yield something like this anyway.

[jmaitri]

Hi Adam,

Congrats on finishing your MSA--well done!

Thank you for your help and with the queries you wrote--very much appreciated and then some!!--will give 'er a go on Monday and see what happens.

We're working on an audit & ready to pull a sample on our data but cannot have these accruals & their reversals in our data . . . a rather daunting task, as we looking at a huge number of transactions to parse these from.

Again, many thanks for your help (will start diving into SQL to buff up my Access skill)!!
Jay