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
