Transaction Processing

[JimB]

Rich,

Have you tried an update query to update the "flag" field
in both tables where the cost center, account and $$ are
joined? I tested this and it updated both tables only
where the joined records matched. Question, are the $$ in
each table the sum total for each record or do you need to
add the each detail for grouped cost center, account
combination to get a total as it searches? If you are
working with one table entry totals this process will
work. Else here is a sample vba of this update which you
may need to expand.

Dim rs As New ADODB.Recordset
Set cncinv = CurrentProject.Connection

stCriteria = "UPDATE [tbl1] INNER JOIN [tbl2] ON ([tbl1].
[costcent] = [tbl2].[costcent]) AND ([tbl1].[acct] =
[tbl2].[acct]) AND ([tbl1].[amt] = [tbl2].[amt]) SET
[tbl1].[match] = 'Complete', [tbl2].[match] = 'Complete';"

rs.Open stCriteria, cncinv, adOpenKeyset, adLockOptimistic

Set rs = Nothing

**Note: you do not need an rs.Close with an update
command. It will generate an error saying can not close
object that is not open.


JimB

-----Original Message-----
Can anyone suggest a good reference (book or otherwise)

for writting VB module within Access for matching and then
marking transactions?

I have 2 tables, one with debits and other with credits.

Some common fields are cost center, account and
transaction amount. Within the tables there can be
multiple debits or credits of the same dollar amount. What
I need to do is start with record 1 in debit table and go
through records in credit table until same $$ is found
where the cost center and account in credit also match the
cost center and account of the debit amount transaction.
Then mark (flag) both records as "complete". Then move to
record 2 in debit table and move through credit table
looking for same dollar amount - cost center - account
combination, but ignoring any credit transactions
previously "flagged". If no matching $$ is found in credit
table for a debit, then move to next debit record without
flagging and repeat above search for matching credit
transaction.

Note: Other than cost center, account and amount there

would not be any other possible fields to match.

 

[JimB]

Rich,

Then the update process I suggested should work. See the
previous threads for the sample VBA code.

JimB

-----Original Message-----
Jim -
Each transaction stands alone, there is no need to add

detail to get a total. However, the debit transactions are
not one to one with the off setting credits. i.e., there
may be 395 individual debit transactions with the same
account, cost center and dollar amount ----- cost ctr
0001, account 12345, debit amount of $25 and 237
individual credit transactions with the same account, cost
center and dollar amount ---cost ctr 0001, account 12345,
credit amount of $25. So for the above example I would
want to flag 237 of the debit transactions and all of the
credit transactions as a match. The remaining 158 debit
transactions would be "unmatched" if you want to think of
it as such.

--
Rich S

Rich,

Have you tried an update query to update the "flag" field
in both tables where the cost center, account and $$ are
joined? I tested this and it updated both tables only
where the joined records matched. Question, are the $$ in
each table the sum total for each record or do you need to
add the each detail for grouped cost center, account
combination to get a total as it searches? If you are
working with one table entry totals this process will
work. Else here is a sample vba of this update which you
may need to expand.

Dim rs As New ADODB.Recordset
Set cncinv = CurrentProject.Connection

stCriteria = "UPDATE [tbl1] INNER JOIN [tbl2] ON ([tbl1].
[costcent] = [tbl2].[costcent]) AND ([tbl1].[acct] =
[tbl2].[acct]) AND ([tbl1].[amt] = [tbl2].[amt]) SET
[tbl1].[match] = 'Complete', [tbl2].[match] = 'Complete';"

rs.Open stCriteria, cncinv, adOpenKeyset, adLockOptimistic

Set rs = Nothing

**Note: you do not need an rs.Close with an update
command. It will generate an error saying can not close
object that is not open.


JimB

-----Original Message-----
Can anyone suggest a good reference (book or

otherwise)
for writting VB module within Access for matching and then
marking transactions?

I have 2 tables, one with debits and other with

credits.
Some common fields are cost center, account and
transaction amount. Within the tables there can be
multiple debits or credits of the same dollar amount. What
I need to do is start with record 1 in debit table and go
through records in credit table until same $$ is found
where the cost center and account in credit also match the
cost center and account of the debit amount transaction.
Then mark (flag) both records as "complete". Then move to
record 2 in debit table and move through credit table
looking for same dollar amount - cost center - account
combination, but ignoring any credit transactions
previously "flagged". If no matching $$ is found in credit
table for a debit, then move to next debit record without
flagging and repeat above search for matching credit
transaction.

Note: Other than cost center, account and amount there

would not be any other possible fields to match.

Thanks-----

.