Find Offset Record

  • Thread starter Thread starter Eric Ip
  • Start date Start date
E

Eric Ip

I have a table with five fields :

Account_Nr. Description Amount Offset
123456 Income 123.00 Yes
123456 Reverse Income -123.00 Yes
123456 Income 123.00
222222 Other Income 222.00
250000 Interesgt 10.00

The table keeps accounting records. Now if a record is
wrong, and an offset record with the same Account_Nr and
same amount (negative) is found, then "Yes" will input
manually by users to identify the wrong record and the
offset record.
Can someone help me to automate this process ? Thanks.
 
What are you wanting to automate? Is it necessary to have an Offset field at
all? What do you do with it?
 
Now if a record is
wrong, and an offset record with the same Account_Nr and
same amount (negative) is found, then "Yes" will input
manually by users to identify the wrong record and the
offset record.
Click to expand...

SELECT AccountNr, Amount, Offset
FROM LedgerRecords AS Outside
WHERE EXISTS
( SELECT *
FROM LedgerRecords AS Inside
WHERE Outside.Account_Nr = Inside.Account_Nr
AND Outside.Amount = - Inside.Amount
AND Inside.Amount > 0
)


Bear in mind that this will fail badly if there is more than one record
with the same amount, and one of them is reversed. And I cannot see what
the PK is, so you cannot create an UPDATE command if you can't identify the
record.

Hope that helps

Tim F
 
-----Original Message-----
What are you wanting to automate? Is it necessary to have an Offset field at
all? What do you do with it?

--
Ken Snell
<MS ACCESS MVP>




.Hi Ken,
Click to expand...

Now I need to check record with negative amount and find
if there is another record with the same positive amount
and with the same account number. I put "Yes" to identify
they offset each other. The process is tedious and I want
to automate it. It is not necessary to keep the offset
field as finally I will delete the records from the table
if there is "Yes" in the field : offset. FYI there may be
more than one pair of offest records. Thanks for you help.
 
Doing it via an automated process will not be straightforward either. Before
we get into this any deeper, let me understand when and under what
conditions you'd want to do this automated process.

Would it be run at a user-initiated action (clicking a button) at some point
in time, thus meaning that the table must be searched in its entirety?

Or would it be run when a record is being entered by the user?

What use do you make of the "Yes" value in subsequent actions?

I still am not convinced that you need to do this action. Are you sure that
there are not other ways to document such information in your table?

Once I get the above information, let's see what we might identify.
 
Back
Top