Creating a Trial Balance in MS Access

A simple and quick way to create trial balance in MS-Access

Step 1 Create tables:

First table : tblNominals

NominalID AutoNumber Primary Key
NominalHeads    Text

Next table called tblTransactions

Transaction ID AutoNumber Primary Key
TransactionDate Date
DebitHead Text Lookup to Nominal table : NominalID
Credit Head Text Lookup to Nominal table : NominalID
Amount Currency

 

Create another table tblSign

Sign Number Primary Key

Enter only two entries in this table 1 and -1.

Step 2 Enter some transactions in tblTransactions

 

Step 3 Create a Query. This is the main bit – go slow

Query Design > Select all three tables created above plus add tblNominals once more, so you should have four tables in the query.

In first QBE grid Field

Nominal: IIf([Sign]=1,[1tblNominals].[NominalHead],[1tblNominals_1].[NominalHead])

In second QBE grid Field

Total: [Amount]*[Sign]

Also Total : Sum

Good luck !

PS – I am currently learning MS Access and this trial balance was made with the help of articles from Simon Hurst on accountingweb

 

Sample Ms Access file:
Simple Trial Balance

 

Update: 11/06/2020

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s