Any Access SQL QUERY ?

[Augustine]

Hi I need to know whether this can be done in Access.

Basically what I am trying to do is in one table I have a
debit amount by a certain person and in the other table I
have a credit amount by the same client or vice versa.

If for example Customer aaa has a
cr ( +1,000-00) in Table A & a
dr ( -1,000-00) in Table B

this would result in as 0 after deducting the 2
transactions. I would then want both Tables respectively
to be "Matched" using the update feature.It should match
1 record in Table A against another record in Table B.

For all items that don't meet the criteria that is 0, I
require to indicate as "unmatched"

The SN is just the serial Number of the number of
transactions in each Table and only for info purposes.

Hope you can give me asolution whether Access can do
matching record by record in this manner.

The two tables eg A & B both are similar tables. i need
to do a 1 to 1 matching. That is
where table Name A= table Name b
table DR/CR A<> table DR/CR b
table Amount A MINUS table Name b =0

if it matches the "Matched" and "unmatched" status must be
updated in the expected result column.Kindly also note
that I have many amounts in my table.


table A

SN Name dr/cr amount Expected Result
1. aaa dr -1000 unmatched
2. aaa cr 1000 matched
3. aaa dr -1000 unmatched
4. bbb cr 1000 matched
5. aaa cr 1000 unmatched
6. aaa dr -1000 unmatched
7. bbb cr 1000 unmatched

table b

SN Name dr/cr amount Expected Result
1. aaa dr -1000 matched
2. aaa dr -1000 unmatched
3. aaa dr -1000 unmatched
4. bbb cr 1000 unmatched
5. bbb dr -1000 matched



Hope you can advise. Thanks

 

[Ben]

Hi-
I'm sorry if I don't fully understand your question, but
it sounds like you are looking for transactions. If you
wish to make sure that 2 or more updates are fully
completed, or not at all (as with a bank transaction, you
want the debit and credit to both be committed, or
neither of the, well, I only want the credit, not the
debit ;-). You can do this in ADO by opening a connection
and using beginTran then execute both statements, and if
there are no errors then commitTran, if there are errors
use RollbackTran. I hope this helps, if you only want to
search for unmatched records there is a wizard in Access
that will find unmatched records- it should give you the
syntax you are looking for.
HTH
Ben