RE: Transaction Processing
From: JimB (jim.bennett_at_kuehne-nagel.com)
Date: 06/24/04
- Next message: Rob: "Re: Start Word Macro from access"
- Previous message: Rick Brandt: "Re: Access 2000 - Realtime Development - Should i stick with Access97"
- In reply to: Rich S: "RE: Transaction Processing"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 24 Jun 2004 06:26:22 -0700
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
>
>
>"JimB" wrote:
>
>> 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-----
>> >--
>> >Rich S
>> >.
>> >
>>
>.
>
- Next message: Rob: "Re: Start Word Macro from access"
- Previous message: Rick Brandt: "Re: Access 2000 - Realtime Development - Should i stick with Access97"
- In reply to: Rich S: "RE: Transaction Processing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
