Building an Accounting system in access.

[bubai]

Hello everybody,
I am trying to make an Accounting system in Access. Few parts have been designed but got stuck in one particular part. I am attaching the Access file for your kind analysis. In the file there are two tables- 1^st one is ChartOfAccounts which acts as a lookup table. It holds the Account code, Name of the unique accounts and their Normal Balance (ie whether it is Debit or Credit).
The 2^nd table is the Transaction table which by definition holds all the transaction. It has the columns- Transaction ID, which is auto number; Date of transaction; Account id which looks up its value from ChartOfAccounts table; Account name which also looks up its value from ChartOfAccounts table; Type, which states whether the account is debited or credited for that transaction and lastly the Amount. Now there is a catch in the table. By the rule of accounting if an account with normal balance of debit is debited (normal balances are all stated in the ChartOfAccounts table for each account) the value get added and if it is credited, the value gets subtracted and for an account with normal balance of credit, if credited, the value gets added and if debited, the value gets subtracted. Hence if you look at the transaction, both building and cash are once debited and once credited. Both of their normal balances as stated in the ChartOfAccounts, are debit. So, depending on the Type column’s value, the Amount column’s value should be positive or negative. How do I do this?
The Ledger is a query which adds up all the accounts’ value and show their balances.
The Journal is a form which feeds into the Transaction table. But it also has a catch. By the rule of Double Entry accounting every transaction should have at least one debit entry and one credit entry and total debit value should equate to total credit value. It can be more than that. Like two debit entry and three credit entry in one transaction and so on. Hence I want to keep an option for that in the form, so, if the total value of debit and credit entries are not equal, the form won’t let anything to be posted to the Transaction table. There could be a button for posting and couple of combo boxes to provide for if a transaction has multiple debit or credit entry. How to make this happen?
ERP.zip
Thanks in advance for any clue.

[rpeare]

First...
1. Don't use spaces in your field/object names, they will cause you problems as you progress with design/programming
2. Don't use special characters other than underscore (_) in your field/object names for the same reason
3. Use a primary key for each table that DOES NOT rely on the data entry to figure it out (i.e. have an AC_ID field in your CHARTOFACCOUNTS table has A/C Code as your unique identifier, charts of accounts can change over time and you do NOT want to rely on the account number to remain the same forever.

Your TRANSACTION table does not need to store the account name or account type, that can be looked up from your chart of accounts table in 1 of two ways.

See Journal and Journal_2 for examples of doing it either way

Journal relies on using the .column function to look up a value in a particular column of a list box or combo box
Journal_2 relies on primary keys being set up on your tables correctly and something called a 'lookup query' to drive the data entry.

ERP.zip

[rpeare]

hit reply too soon, once you decide on a method you can start working formulas, for instance in the first case the account type field is named ACCTYPE so you could have a formula on this form that said:

=iif(acctype = "DEBIT", [Amount] * -1)

to change the amount to a negative on debits

I changed your field names to eliminate the problem characters/names but all your LEDGER query is doing is summing the amount field. If you are looking to create a formula that will show the sum of transactions over a particular time period (or over the life of the account) you just modify the formula above to fit into your query.

something like

SELECT ChartOfAccount.AC_Name, ChartOfAccount.NormalBalance, Sum(Transaction.Amount) AS SumOfAmount, Sum(IIf([normalbalance]="DEBIT",[amount]*-1,[amount])) AS AmtMod
FROM [Transaction] LEFT JOIN ChartOfAccount ON Transaction.AC_ID = ChartOfAccount.AC_ID
GROUP BY ChartOfAccount.AC_Name, ChartOfAccount.NormalBalance;

[bubai]

Dear rpeare,
Thanks for your reply, but there might have been some misinterpretations.
First of all, you cannot take away the Acc_Type (Debit or Credit) field from the Transaction table as it is the identifier of the transaction. It determines the nature of a transaction whether an amount is being added or subtracted from that account depending on its Normal Balance. If you leave that field out you cannot compare with the Normal Balance. The linking you have made in the Journal forms just plots Normal Balance field from the ChartofAccount table which will always be the same. It does not take regard to the transaction’s variety which is not always the same.

I need a formula that will compare the form’s Acc_Name value with ChartofAccount’s Acc_Name value and if they are same it will compare the Normal Balance field value for that account with form’s/Transaction table’s Acc_Type value(ie Debit/Credit) and if they are different will multiply the Amount field value with -1 (ie negative).

For the journal form, I want a button which will check the total debt amount with total credit amount if they are equal and then will let me post this to the Transaction table or else not. Every time these values are posted to the transaction table will be considered a transaction and they should put the same Transaction_ID value in the field. I need this to see the report for transactions of a particular period. A similar structure of the journal form is attached as .
Another file of the database is attached with corrected field names as advised by you.ERP2.zip
One thing baffles me- how Journal_2 A/c_ID field plots values for next two columns! Please clarify.

[rpeare]

The concept is the same, I've modified your database, added a table that defines the 'type' (credit vs debit) and modified both your form and given you a query that figures the debit and credit columns based on the NORMAL transaction type based on the chart of accounts.

The formula is broken up into several different fields so you can see how I put it together, normally you would likely want this to be a single formula to simplify things.


ERP.zip

[bubai]

Dear rpeare,

I have not yet been able to figure out the total mechanics of your select statement. But two tables named “tblAccTypeNormal” & “tblAccTypeTransaction” seems to be missing in it. Although the Journal and qryDataEntryBase showing the amounts accordingly; when it saves the inputs in the Transaction table all the amounts become positive numbers. Thus summation of the Transaction table data is not showing the CORRECT totals of the accounts (Legger query). Do I have to derive the accounts sum from the qryDataEntryBase?

[rpeare]

The tables are not missing, I have just given them aliases, they both reference the same table tblAccType, I just gave them names that would help me distinguish between the NORMAL (GL chart of account table) and the ACTUAL (transaction table) account type. Your Debit/Credit fields are based on calculations and therefore should not be stored in a table at all, you can calculate them any time you use a form, run a report or run a query based on the data. In other words, your data entry should give you all the information you need to correctly identify debits/credits so you should not need to store the amount and 2 other columns (debit/credit).

If you are determined to store the debit/credit amounts with the correct sign (+/-) a query is not the place to do it, you would have to have code on your form that would correctly update the appropriate field during data entry using something like the ON EXIT property of your ACTUAL account type (debit/credit) to update the field accordingly.