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