Accounting Problem

[Stuart Taylor]

I have several to show the state of each clients account on there record.

Usually this would be easy:

(Contacts Tbl, Invoice Tbl, Payments Tbl)

Contact Balance = Total Contact Invoices - Total Contact Payments

But here is the wild card.

(Bank Accounts Tbl)

The Bank Accounts Table holds the information about each bank account each in different currencies. The Invoices and payments are in different currencies relating to the accounts.

So, I need to show the balance of each Currency Bank Account on each contact record and i can not find a way for doing this without creating a seperate table with bank account records for each contact?

Contact > Contact Bank Account > Invoice

Contact > Contact Bank Account > Payment

Is this the only way to achieve this or can i do it with:

Contacts

Bank Account

Invoice

Payment

Note: contacts do get invoiced in multiple currencies although it is only ever one currency per invoice.

Any thoughts would be great.

[mr_vodka]

Hi Stuart,

Can you elaborate on the Bank Account table, its purpose, and how you envision it fitting in with the rest of your tables?

[Stuart Taylor]

Hello,

The bank account is that of the company who's database it is.

They have a USD account, GBP account and EUR account.

Invoices relate to the Currency of the Items being sold. Each invoice is assigned a currency account.

This means with multiple sales a client may pay into different bank accounts.

This is never resolved into one currency, so the only way to track the clients balance is to track each currency account.

Edited March 14, 2007 by Guest

[Stuart Taylor]

Bit More:

------------------------

CONTACT:

ID

------------------------

INVOICE:

ID

Contact_ID

Account_ID

Currency

Amount

------------------------

PAYMENT:

ID

Contact_ID

Currency

Amount

------------------------

ACCOUNT:

ID

Currency

------------------------

Do I need ?

CONTACT ACCOUNT:

Contact_ID

Account_ID

[comment]

This is confusing, especially this part:

INVOICE:

ID

Contact_ID

Account_ID

Currency

How do you decide on the Account_ID value? Judging from what you said above, there are only 3 accounts, and the account should be given by the currency.

Can an invoice be in one currency, and receive payments in another? More background information could be useful.

[Stuart Taylor]

Hi comment,

• The company can have several Bank Accounts.

• Each Bank Account can only have one currency, but it can be any currency.

• The company can have more than one Bank Account in the same currency.

• When creating an invoice you choose a bank account for the contact to pay the money into. This determines the currency of the invoice and the currency of the items on it.

• Although a contact can pay in a different currency to that on the invoice, it is payed into the specific Currency Account and therefore the payment will be in that currency when entered into the payments table.

• The required result is to simply have the contact record say

0. Credit, 1. Balanced, 2. Outstanding

This must be accompanied by the totals outstanding for each Bank Account in relation to that contact

I know i can do this by creating an intermediate database containing the Account_ID and the Contact_ID but i was hoping there was another way, with the tables already in place.

[mr_vodka]

Hmmm, if you use a calc with List (INVOICE:Account_ID) or using the old value list technique, then is should give a you a key that you can use from the contact table to allow you to use in a relationship to the Accounts table. You would have to compensate for refresh issues but would this solve your issue?

Edited March 14, 2007 by Guest

[comment]

I am still having trouble with the basic structure. Is this a correct summary:

A Contact has many Invoices. Each Invoice has one Bank Account. Each Account has one Currency.

A Contact has many Payments. Each Payment has one Bank Account.

[Stuart Taylor]

Thats all correct

[comment]

OK, then. Now what do you want to see? A list of Accounts of the selected Contact, with the balance (for this Contact)? Or is this supposed to be a report for multiple Contacts?

Also am I correct in assuming that the account balance here is the sum of Invoices whose ContactID match the selected Contact and whose AccountID match the Account, minus the sum of Payments with a similar match?

(I think that if my assumptions are correct, I gave you the answer already...)

[Stuart Taylor]

Hi Comment/Mr V

Here is an image of my ORIGINAL structure and the two structures that do work, its just that the Contact_Account database is a little difficult to manage.

Maybe this is the only way?

[comment]

I don't see why you need a ContactAccount join. It's just duplicating known data. We already know which Accounts are related to a Contact: it's the Accounts in which a Contact has received an Invoice and/or made a Payment. IOW, you already have 2 join tables between Contacts and Accounts, why add a third one, with no new information?

Now, back to my last question...

[Stuart Taylor]

I was trying to show this information on the contact record:

Each account with the balance relevant to the contact.

[comment]

That picture is a bit confusing, because it SAYS 'Accounts', but SHOWS currencies. I presume that if the contact had dealings with two USD accounts, there would be two separate rows to reflect that.

I see two questions here:

a) how to get contact's accounts into a portal?

how to compute contact's balance in each account?

The answer to (a) is easy. First, if it can be assumed that no one makes a payment without being invoiced first, the portal can be simply to the same TO of Accounts that is connected to Contacts via Invoices.

If the assumption is incorrect, we can define a calculation field in Contacts that lists the AccountIDs from both Invoices and Payments, and connect this to a new TO of Accounts.

The second problem is a bit more complicated. Clearly, from the point of view of Account, you need to see the transactions that relate to the Account AND to the viewed Contact. This means you need a global gContactID field in the Accounts table - and therefore the navigation between Contacts has to be scripted (at least when on this layout).

A relationship from Accounts to a new TO of Invoices:

Accounts::AccountID = Invoices 2::AccountID

AND

Accounts::gContactID = Invoices 2::ContactID

can fetch the sum of contact's debits in this account. A similar relationship to a new TO of Payments can bring in the sum of credits. The balance is the difference between the two sums.