Create a Form with Sub Form Using Form Wizard

Creating a form with a sub form in MS Access is best applied to the table that have the one-to-many relationship with another table. The objective is to eliminate redundancy in one or more fields in a table.

In general, tables that will be the form is a parent table, while the table to be used as a sub form is a child table that is relevant to the parent table. For example, forms invoices, bills, purchase orders, and various other forms that have a number of details of more than one item. Of the tables we have created, table tblOpenTransactionJournalParent will become a form with table tblOpenTransactionJournalChild that will become its sub form; table tblPermTransactionJournalParent and tblPermTransactionJournalChild are as well.

Create a Form Using Blank Form

If you want to create a form based on your own customized style, then you can create it by using Blank Form. MS Access  give much more flexibility on using Blank Form in designing a form. To create a form using Blank Form, do the following steps:

1. Click Create tab, on the Forms group click Blank Form icon. Access will display a blank form viewed in Layout View and Field List on the right.
2. Choose tblDerivativeAccount1 in the Field List. Point the mouse to the field DerivCode1. To put this field into the Layout View of the blank form, double click this field or drag it into the blank form. Do the same thing for field DerivName1.

Create a Form Using Form Wizard

Form is one of the user interface between users and database. In the case of MS Access database, the form is user interface between the user and the table. To add, delete, edit, and save a record, a user is not allowed (forbidden) to do so directly through the table. Database management systems such as MS Access, mySQL, SQL Server are an integrated database. So if we do edit a record directly in a table, other table(s) may be disturbed, so that the database may corrupt, become out of sync, or possibly be crashed. For that reasons, we should use form to add, delete, edit, and save a record. Other than that, form can be used as a navigation, guideline, even alert when an error occurred.

Create Tables Relationship

After creating and identifying the necessary tables in an accounting system, the next step is to create a relationship. Relationship describes the relations among tables in MS Access database. Once this relationship is finished, then you can design forms, reports, and queries.

A relationship works by matching data in primary key fields, usually fields with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.

Create Tables in MS Access - Transaction Table

In the accounting system, transaction table is a table containing the financial events that are recorded each time a transaction occurred based on valid evidences or documents.

The process of recording in a journal is stored in the transaction table. Generally, journals of transactions that will be created consist of two tables:

1. Parent table: contains information that is global and is a summary, for example, the ID number of the journal, the journal type, journal name maker, name of the verifier/approver
2. Child table: contains information that is details such as account codes, references, debits, credits, and descriptions of the transactions.

Create Tables in MS Access - Master Table

After identifying a glimpse of things related to the accounting system, now is the time for us to learn how MS Access works. As a starting point, we will create a very simple accounting system, the objective is that we know better what it is MS Access. Slowly but surely, we will add a variety of features that will benefit us.

Chart of Account

Chart of account is account codes and account names to be used to record transaction journal. This blog does not discuss various types of chart of accounts theory. This blog will only explain an ideal chart of account based on experience or practice and is very useful for user at the clerical level. As you know, day to day activities related with chart of account mostly used by user, in this case, employee at the clerical or staff level. At the supervisory or managerial level, chart of account is very important also, but they never involved directly in the daily recording transaction journal as the clerical staffs do. In addition, at the supervisory level and above, user usually are able to understand accounting system easily so that they can remember the account code without using any supporting tool.