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: Logo

You are commenting using your 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

%d bloggers like this: