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.

Create master table

Master table contains data that are descriptive and summarized information and will be used as guidelines to enter data into the transaction table. Master table in a simple accounting system consists of:

1. Table chart of accounts
2. Table 1 derivative account
3. Table 2 derivatives account
4. Table journal type: to identify from what sources is, a transaction occurred. For example: a cash receipt transaction should use Cash Receipt journal type to record this transaction, a purchasing transaction should use Account Payable journal type to record this transaction - no matter it is a cash or credit purchasing.

How to create table in MS Access

First, create an Access database with the name Accounting. Next do the following steps:

1. You can create table by clicking Create tab in the Ribbon and then choose Table Design in the Tables Group.
2. In the Field Name column, type "AccountCode". In the Data Type column, select Text, and in the Description column, type "Unique account code".
3. Furthermore, for the field properties (at the bottom, you can press F6 to move to the Field Properties), fill in the General Tab: Field Size = 3 and Caption = Account Code.
4. Make sure that AccountCode is set as the primary key. If not, highlight the line AccountCode, click the right mouse and select the primary key, or you can also choose a primary key via the Tools Group - Primary Key button.
5. Under the AccountCode line or row, insert a second field by typing "AccountName" and Data Type = Text. Type "Account name or description" in the Description Column. For the field properties, fill in General Tab: Field Size = 100 and Caption = Account Name.
6. Third field, fill in the Field Name = GroupName, Data Type = Text and Description = Choose the group name whether this account is Assets, Liabilities, Equities, Revenues, Expenses, Or Income Summary.
7. For the field properties, fill in General Tab: Field Size = 1, Caption = Group Name, and Rquired = Yes. Fill in the Lookup Tab:
   1. Display Control = Combo Box
   2. Row Source Type = Value List
   3. Row Source = Group Code;Group Name;a;Assets;l;Liabilities;e;Equities;r;Revenues;x;Expenses;i;Income Summary
   4. Bound Column = 1
   5. Column Count = 2
   6. Column Heads = Yes
   7. Column Widths = 0";1.5"
   8. List Width = 1.5" (must be equal to the total Column Widths)
   9. Limit To List = Yes

If you view the  MainAccount table using Datasheet View, the display should be like this:

At this stage you have already known how to create a simple table with the name tblMainAccount that in the accounting system it is a table chart of accounts. The next step is to create a master table for Table 1 derivative account, Table 2 derivative account, and Table journal type. As a step of making the table above, the following is the information needed to create another table.

Table name: tblDerivativeAccount1

1. Field Name = DerivCode1
   1. Data Type = Text
   2. Description = Derivative Code
   3. Field Properties - General Tab:
      1. Field Size = 3:
      2. Caption = Derivative Code
2. Field Name = DerivName1
   1. Data Type = Text
   2. Description = Derivative name or description
   3. Field Properties - General Tab:
      1. Field Size = 100:
      2. Caption = Derivative Name
3. Field Name = Remark
   1. Data Type = Text
   2. Description = Additional description or remark
   3. Field Properties - General Tab:
      1. Field Size = 30:
      2. Caption = Remark
4. Primary key : DerivCode1

Table name: tblDerivativeAccount2

1. Field Name = DerivCode2
   1. Data Type = Text
   2. Description = Derivative Code
   3. Field Properties - General Tab:
      1. Field Size = 3:
      2. Caption = Derivative Code
2. Field Name = DerivName2
   1. Data Type = Text
   2. Description = Derivative name or description
   3. Field Properties - General Tab:
      1. Field Size = 100:
      2. Caption = Derivative Name
3. Primary key : DerivCode2

Table name: tblJournalType

1. Field Name = JournaType
   1. Data Type = Text
   2. Description = Journal type
   3. Field Properties - General Tab:
      1. Field Size = 2:
      2. Caption = Journal Type
2. Field Name = JournalName
   1. Data Type = Text
   2. Description = Journal name or description
   3. Field Properties - General Tab:
      1. Field Size = 100:
      2. Caption = Journal Name
3. Field Name = Remark
   1. Data Type = Text
   2. Description = Additional description or remark
   3. Field Properties - General Tab:
      1. Field Size = 30:
      2. Caption = Remark
4. Primary key : JournaType

To see the full field summary, please go to page Create Tables Relationship.