By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,882 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,882 IT Pros & Developers. It's quick & easy.

Account Balancing

P: n/a
I am currently working on a system that applies payments to an
account. Here is an example of how the data looks

Acct TransactionAmount PaymentAmount
1234 100.00 100.00
1234 100.00 100.00
1234 100.00 50.00
1234 200.00 50.00
1234 300.00 100.00

When a payment is received that partially recoups the
TransactionAmount (as in transaction #3) I need to show that the 50.00
payment from transaction #4 applied to the remaining amount in
transaction #3. This situation also causes the payment in transaction
#5 to be applied to the amount in transaction #4.

It's not so much the balance that I need to show it's the actual
account that the payment is being applued to that I need to identify.
I have looked at the SHAPE documentation frm ADO but am looking for an
easier way. The results are going to be shown on a form and a report.

Thanks for the help,

strvariant

May 9 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
There is a one-to-many relationship between transactions and payments. You
need the following tables:

TblTransaction
TransactionID
Acct
TransactionAmount

TblPayment
PaymentID
TransactionID
PaymentAmount

You can get the balance on any transaction by summing the payments for the
transaction in TblPayment and subtracting from the transaction amount for
the transaction in TblTransaction.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com

<st********@yahoo.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...
>I am currently working on a system that applies payments to an
account. Here is an example of how the data looks

Acct TransactionAmount PaymentAmount
1234 100.00 100.00
1234 100.00 100.00
1234 100.00 50.00
1234 200.00 50.00
1234 300.00 100.00

When a payment is received that partially recoups the
TransactionAmount (as in transaction #3) I need to show that the 50.00
payment from transaction #4 applied to the remaining amount in
transaction #3. This situation also causes the payment in transaction
#5 to be applied to the amount in transaction #4.

It's not so much the balance that I need to show it's the actual
account that the payment is being applued to that I need to identify.
I have looked at the SHAPE documentation frm ADO but am looking for an
easier way. The results are going to be shown on a form and a report.

Thanks for the help,

strvariant

May 9 '07 #2

P: n/a
On May 9, 1:43 pm, "Steve" <s...@private.emailaddresswrote:
There is a one-to-many relationship between transactions and payments. You
need the following tables:

TblTransaction
TransactionID
Acct
TransactionAmount

TblPayment
PaymentID
TransactionID
PaymentAmount

You can get the balance on any transaction by summing the payments for the
transaction in TblPayment and subtracting from the transaction amount for
the transaction in TblTransaction.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resou...@pcdatasheet.com

<strvari...@yahoo.comwrote in message

news:11**********************@q75g2000hsh.googlegr oups.com...
I am currently working on a system that applies payments to an
account. Here is an example of how the data looks
Acct TransactionAmount PaymentAmount
1234 100.00 100.00
1234 100.00 100.00
1234 100.00 50.00
1234 200.00 50.00
1234 300.00 100.00
When a payment is received that partially recoups the
TransactionAmount (as in transaction #3) I need to show that the 50.00
payment from transaction #4 applied to the remaining amount in
transaction #3. This situation also causes the payment in transaction
#5 to be applied to the amount in transaction #4.
It's not so much the balance that I need to show it's the actual
account that the payment is being applued to that I need to identify.
I have looked at the SHAPE documentation frm ADO but am looking for an
easier way. The results are going to be shown on a form and a report.
Thanks for the help,
strvariant- Hide quoted text -

- Show quoted text -
Steve,

Thanks for your help. Unfortunately there is no TransactionID that is
part of the payment data. The payments come in one file and the
transactions come in another. There is no relationship other than the
account number.

Your solution would certainly work if the payments had the
corresponding TransactionID. In this case they don't and I may be
forced to rely on some complex looping/if then/case statements to
evaluate each transaction amount against each payment amount. I had
thought I could avoid using a SQL cursor but I may end up doing that.

Thanks,

Mark

May 10 '07 #3

P: n/a
You just need a form based om TblPayment to enter payments. Use a combobox
to record the TransactionID. The rowsource for the combobox would be a query
based on TblTransaction. The query would include all three fields. Column1
would be TransactionID, column would be Acct and Clumn3 would be
TransactionAmount. Set BoundColumn = 1, Columns = 3 and Column Width to
0;1.5;1.5. The combobox will then display Acct and TransactionAmount and
record TransactionID when you make a selection. So when you record a
payment, you would assign the payment to a specific account (TransactionID)
in the combobox and record the actual payment.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


<st********@yahoo.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...
On May 9, 1:43 pm, "Steve" <s...@private.emailaddresswrote:
>There is a one-to-many relationship between transactions and payments.
You
need the following tables:

TblTransaction
TransactionID
Acct
TransactionAmount

TblPayment
PaymentID
TransactionID
PaymentAmount

You can get the balance on any transaction by summing the payments for
the
transaction in TblPayment and subtracting from the transaction amount for
the transaction in TblTransaction.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resou...@pcdatasheet.com

<strvari...@yahoo.comwrote in message

news:11**********************@q75g2000hsh.googleg roups.com...
>I am currently working on a system that applies payments to an
account. Here is an example of how the data looks
Acct TransactionAmount PaymentAmount
1234 100.00 100.00
1234 100.00 100.00
1234 100.00 50.00
1234 200.00 50.00
1234 300.00 100.00
When a payment is received that partially recoups the
TransactionAmount (as in transaction #3) I need to show that the 50.00
payment from transaction #4 applied to the remaining amount in
transaction #3. This situation also causes the payment in transaction
#5 to be applied to the amount in transaction #4.
It's not so much the balance that I need to show it's the actual
account that the payment is being applued to that I need to identify.
I have looked at the SHAPE documentation frm ADO but am looking for an
easier way. The results are going to be shown on a form and a report.
Thanks for the help,
strvariant- Hide quoted text -

- Show quoted text -

Steve,

Thanks for your help. Unfortunately there is no TransactionID that is
part of the payment data. The payments come in one file and the
transactions come in another. There is no relationship other than the
account number.

Your solution would certainly work if the payments had the
corresponding TransactionID. In this case they don't and I may be
forced to rely on some complex looping/if then/case statements to
evaluate each transaction amount against each payment amount. I had
thought I could avoid using a SQL cursor but I may end up doing that.

Thanks,

Mark

May 10 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.