Creating a 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

• Continuing MS Access studies with help of a book – Building accounting systems using Access
• Companion files of the above mentioned book can be found at archive.org