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.
tblNominals will have two connections with tblTransactions. Remove one connection and add one from tblNominals_1 in a way that Debit and Credit in tblTransactions should have one connection each from the tbltblNominals and tblNominals_1. Remember PrimaryID is connected.
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 – 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
Update:
02/05/2023 – Added queries to generate Profit & Loss Account and Balance Sheet. Link
Roy Accountants 