Omnis Mus Database Tables
A table is a collection of data. A database is a collection of tables. The journals and ledgers that comprise the Omnis Mus accounting system are physically stored in tables. Most components are stored in a single table. General journal entries and orders require two tables each to adequately represent their data structure.
System Components (click picture to enlarge)
Ledgers
Ledgers contain summarized financial data. The Omnis Mus accounting system has three ledgers. Each ledger is stored in a single table.These tables, and their internal Microsoft Access table names (which you NEVER have to use when using Omnis Mus), are as follows.
The general ledger is stored in the table titled 'General Ledger'.
The accounts receivable ledger is stored in the table titled 'acctsreceivable'.
The accounts payable ledger is stored in the table titled 'acctspayable'.
Journals
General Journal
A general journal entry is recorded in two separate tables. The table title 'General Journal' contains a single record of header information for each entry: number, date, description, and posted fields. The accounts and related amounts for each entry (a 'many to one relationship') are recorded in the table title 'Details'. Each entry in the details table contains the general journal number, a specific account, and the related specific amount. General journal entries are posted to the general ledger individually and immediately upon their entry.
Orders
An order is recorded in two separate tables. The table titled 'Orders' contains a single record of header information for each order: order number, customer number, date, order type, subtotal, shipping, amount due, and posted fields. The items ordered and related amounts for each entry (a 'many to one relationship') are recorded in the table titled 'Order Details'. Each entry in the order details table contains the order id number, the vendor product number of the item, quantity of the item, price, and itemtotal (quantity times price).
The orders form functions as a sales journal. Orders become sales when the order is shipped and shipping charges are entered into the order. Periodically orders are posted. Posting orders updates the accounts receivable subsidiary ledger for each individual shipped order (sale) and also updates the general ledger with summary data which serves as the general ledger journal entry.
Omnis Mus displays a posting report at the completion of processing. The posting report confirms that the balances of the accounts receivable subsidiary ledger and the general ledger control account are in agreement after posting. The posting report also displays the summary journal entry data that was posted to the general ledger.
Receipts
Receipts - payments received from customers on account - are stored in the table titled 'Receipts'. Periodically receipts are posted. Posting receipts updates the accounts receivable subsidiary ledger for each individual payment received on account and also updates the general ledger with a summary data. The summary data is a general ledger journal entry posted directly to the general ledger. Omnis Mus displays a posting report at the completion of processing. The posting report confirms that the balances of the accounts receivable subsidiary ledger and the general ledger control account are in agreement after posting. The posting report also displays the summary journal entry data that was posted to the general ledger.
Purchases
Invoices received from vendors for purchases made on account are entered into the purchases form, which is stored in the table titled 'Purchases'. Periodically purchases are posted. Posting purchases updates the accounts payable subsidiary ledger for each purchase on account and also updates the general ledger with a summary data. The summary data, which is the general ledger journal entry, is posted directly to the general ledger. Omnis Mus displays a posting report at the completion of processing. The posting report confirms that the balances of the accounts payable subsidiary ledger and the general ledger control account are in agreement after posting. The posting report also displays the summary journal entry data that was posted to the general ledger.
Payments to Vendors
Payments made to vendors on account are entered into the Payments to Vendors form, which is stored in the table titled 'Payments'. Periodically payments to vendors are posted. Posting payments updates the accounts payable subsidiary ledger for each payment on account and also updates the general ledger with a summary data. The summary data, which the general ledger journal entry, is posted directly to the general ledger. Omnis Mus displays a posting report at the completion of processing. The posting report confirms that the balances of the accounts payable subsidiary ledger and the general ledger control account are in agreement after posting. The posting report also displays the summary journal entry data that was posted to the general ledger.
System Tables
There are several other tables in the actual database that are updated by the Omnis Mus accounting system. You NEVER have to use these tablenames when using Omnis Mus. The Income table stores the income statement. The Balsheet table stores the balance sheet. The incomesummary table stores the closing entry. These three tables are processed when you select the option 'close accounts' in the general ledger.
The software table stores the description and sales price of items in inventory. The software table is displayed in the orders form to allow selection of the items ordered by the customer and their sales price.
The preferences table is used by Omnis Mus to store preferences.