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.

For file storing purposes, journal of the transaction also consist of two part:

1. Open journal: the journal that is editable, un-approved, even can be deleted.
2. Permanent journals: journals that have been approved, can not be edited, and can not be deleted. To correct the wrong journal, a correction journal must be made.

How to create transaction table

Since you have already known how to create table in MS Access as discussed earlier, here are the information pertain with transaction table. Using the first two steps on how to create table, use this below information to create transaction table.

1. Table name: tblOpenTransactionJournalParent

1. Field Name = JournaId
   1. Data Type = AutoNumber
   2. Description = Journal ID number
   3. Field Properties - General Tab:
      1. Field Size = Long Integer:
      2. Caption = Journal ID
2. Field Name = JournaType
   1. Data Type = Text
   2. Description = Journal type
   3. Field Properties - General Tab:
      1. Field Size = 2:
      2. Caption = Journal Type
   4. Feld Properties - Lookup Tab:
      1. Display Control = Combo Box
      2. Row Source Type = Table/Query
      3. Row Source = JournalType
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Head = Yes
      7. Column Widths = 1";2.5"
      8. List Width = 3.5"
      9. Limit to List = Yes
3. Field Name = JournalNumber
   1. Data Type = Number
   2. Description = Journal number in related with journal type
   3. Field Properties - General Tab:
      1. Field Size = Long Integer:
      2. Caption = Journal Number
4. Field Name = TransactionDate
   1. Data Type = Date/Timer
   2. Description = Indicate transaction date
   3. Field Properties - General Tab:
      1. Format = Short Date
      2. Input Mask = 99/99/00;;_
      3. Default Value = =Now()
      4. Caption = Transaction Date
5. Field Name = Ref
   1. Data Type = Text
   2. Description = Reference
   3. Field Properties - General Tab:
      1. Field Size = 50:
      2. Caption = Journal Reference
6. Field Name = RefNo
   1. Data Type = Number
   2. Description = Journal Reference number
   3. Field Properties - General Tab:
      1. Field Size = Integer:
      2. Caption = Reference Number
7. Field Name = CreatedBy
   1. Data Type = Text
   2. Description = Indicate person who create the transaction journal
   3. Field Properties - General Tab:
      1. Field Size = 3:
      2. Caption = Created By
8. Field Name = ApprovedBy
   1. Data Type = Text
   2. Description = Indicate person who approve the transaction journal
   3. Field Properties - General Tab:
      1. Field Size = 3:
      2. Caption = Approved By
9. Primary key : JournaId

2. Table name: tblOpenTransactionJournalChild

1. Field Name = Number
   1. Data Type = AutoNumber
   2. Description = Order number
   3. Field Properties - General Tab:
      1. Field Size = Long Integer:
      2. Caption = Number
2. Field Name = JournaId
   1. Data Type = Number
   2. Description = Journal ID number
   3. Field Properties - General Tab:
      1. Field Size = Long Integer
      2. Caption = Journal ID
3. Field Name = RefDetail
   1. Data Type = Text
   2. Description = Reference
   3. Field Properties - General Tab
      1. Field Size = 50
      2. Caption = Reference Detail
4. Field Name = AccountCode
   1. Data Type = Text
   2. Description = Account code
   3. Field Properties - General Tab:
      1. Field Size = 3
      2. Caption = Account Code
   4. Field Properties - Lokup Tab:
      1. Display Control = Combo Box
      2. Row Source Type = Table/Query
      3. Row Source = MainAccount
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Head = Yes
      7. Column Widths = 1";2.5"
      8. List Width = 3.5"
      9. Limit to List = Yes
5. Field Name = Description
   1. Data Type = Text
   2. Description = Description of the transaction
   3. Field Properties - General Tab:
      1. Field Size = 100:
      2. Caption = Description
6. Field Name = Deriv1
   1. Data Type = Text
   2. Description = Indicate first derivative account for the related account code
   3. Field Properties - General Tab:
      1. Field Size = 3:
      2. Caption = Deriv 1
   4. Field Properties - Lokup Tab:
      1. Display Control = Combo Box
      2. Row Source Type = Table/Query
      3. Row Source = DerivativeAccount1
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Head = Yes
      7. Column Widths = 1.5";2.5"
      8. List Width = 4"
      9. Limit to List = Yes
7. Field Name = Deriv2
   1. Data Type = Text
   2. Description = Indicate second derivative account for the related account code
   3. Field Properties - General Tab:
      1. Field Size = 1:
      2. Caption = Deriv 2
   4. Field Properties - Lokup Tab:
      1. Display Control = Combo Box
      2. Row Source Type = Table/Query
      3. Row Source = DerivativeAccount2
      4. Bound Column = 1
      5. Column Count = 2
      6. Column Head = Yes
      7. Column Widths = 1.5";2.5"
      8. List Width = 4"
      9. Limit to List = Yes
8. Field Name = Debit
   1. Data Type = Number
   2. Description = Amount to be debited
   3. Field Properties - General Tab:
      1. Field Size = Double
      2. Format = Standard
      3. Caption = Debit
      4. Default Value = 0
9. Field Name = Credit
   1. Data Type = Number
   2. Description = Amount to be credited
   3. Field Properties - General Tab:
      1. Field Size = Double
      2. Format = Standard
      3. Caption = Credit
      4. Default Value = 0
10. Field Name = UOM
    1. Data Type = Text
    2. Description = Indicate unit of measurement, if any
    3. Field Properties - General Tab:
       1. Field Size = 12:
       2. Caption = Measurement Unit
11. Field Name = Quantity
    1. Data Type = Number
    2. Description = Quantity
    3. Field Properties - General Tab:
       1. Field Size = Double
       2. Format = Standard
       3. Caption = Quantity
       4. Default Value = 0
12. Field Name = UnitPrice
    1. Data Type = Number
    2. Description = Unit price
    3. Field Properties - General Tab:
       1. Field Size = Double
       2. Format = Standard
       3. Caption = Unit Price
       4. Default Value = 0
13. Field Name = TotalAmount
    1. Data Type = Number
    2. Description = Total amount, as a result of Quantity multiplied by Unit Price
    3. Field Properties - General Tab:
       1. Field Size = Double
       2. Format = Standard
       3. Caption = Unit Price
       4. Default Value = 0
14. Field Name = DueDate
    1. Data Type = Date/Timer
    2. Description = Indicate due date for specific transaction
    3. Field Properties - General Tab:
       1. Format = Short Date
       2. Input Mask = 99/99/00;;_
       3. Default Value = =Now()
       4. Caption = Transaction Date
15. Primary key : Number

As discussed earlier, there are two tables: open transaction journal and permanent transaction journal. Do create permanent transaction table as exactly same as the above open transaction journal table. The difference is that permanent table has Process and OJRef fields as follow:

1. Field Name = Process
   1. Data Type = Number
   2. Description = Indicate whether the transaction has been processed
   3. Field Properties - General Tab:
      1. Field Size = Integer:
      2. Caption = Process
      3. default Value = 0
2. Field Name = OJRef 
   1. Data Type = Number
   2. Description = Open journal ID reference
   3. Field Properties - General Tab:
      1. Field Size = Long Integer
      2. Caption = OJ Ref

So, instead of 8 fields needed in open transaction journal table, permanent journal has 10 fields. The fields of permanent transaction journal table are as follow:

1. The fields of tblPermanentTransactionJournalParent = All the fields of tblOpenTransactionJournalParent + field name "Process" and "OJRef " above.
2. The fields of tblPermanenTransactionJournalChild = All the fields of tblOpenTransactionJournalChild

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