Loading....
Coupon Accepted Successfully!

 

Designing Accounting Reports using Access


Generating Financial Accounting Reports like Cash book, Bank Book, Ledger Accounts and Trial Balance based on Access database tables is the main feature of MS Access. It is very easy to create Accounting Reports mentioned above through Access by using relevant SQL statements. Let us now consider the exact process in the context of generating Trial Balance.

To produce a Trial Balance, it is necessary to obtain the following information:
  • Total amount by which every account has been debited
  • Total amount by which every account has been credited
  • Total amount of accounts with their debit and credit totals; and
  • The record which consists of Account code, name of Account, Debit and Credit Amount.
To get the above mentioned information, we have to perform the following SQL statements simultaneously:

To find total amount by which the accounts have been debited, we can use the SQL statement given below and save it as Query 1:

 

SELECT Debit AS code, Sum (amount) AS Total
FROM Vouchers
GROUP BY debit;

To find the total amount by which the accounts have been credited, we can use the SQL statement given below and save it as Query 2:

SELECT Credit AS Code, Sum (Amount) * (-1) AS Total
FROM Vouchers
GROUP BY Credit;

To find total amount of accounts with their debit and credit totals, we can use the SQL statement given below and save it as Query 3:

SELECT*
FROM Query 1
UNION SELECT*
FROM Query 2

To find the net amount with which an account has been debited or credited, we can use the SQL statement given below and save it as Query 4:

SELECT Code, Sum (Total) AS Net
FROM Query 3
GROUP BY Code:

To find the record which consists of Account code, name of Account, Debit and Credit Amount, we can use the SQL statement given below and save it as Query 5:

SELECT a. Code, b. name AS (Name of Account), IF (a. Net>0, a. Net.null) AS Debit,
IF (a. Net <0.abs(a.Net), null) AS Credit
FROM Query 4 AS a, Accounts AS b
WHERE a.code = b.code;

 

After executing all the above mentioned queries in SQL statement form, we will be able to get the Trial Balance of the given accounts of the organization.




Test Your Skills Now!
Take a Quiz now
Reviewer Name