Account Types Table
This table lists the different account types that the accounts are grouped into. This is a pretty simple table. It has a primary key for identifying each record and it lists the account type. The actual names used are typically very similar to the account types listed in Table A-1: Assets, Liabilities, Revenues, Expenses and Shareholder Equity. To find the proper table, look for a table name with the word “Account” in it as well as some type of word relating to classifying data.
If you look at Xtreme tables listed in Figure A-1, you’ll see three tables with the word “Account” in them and another descriptive word. It appears that accounts are broken down into three categories: Type, Heading and Class. Once I opened up the tables and looked at the data, I saw that the table “Account Type” had what I was looking for. There were five records with names matching Table A-1. However, I was curious about what was in the other tables. Upon inspection I saw that the table “Account Class” broke each account into more descriptive sub-types such as “Cash”, “Operating Revenue”, “NonOperating Revenue”, etc. The table, “Account Heading” lists appropriate headings that should appear on financial reports. Some examples are: “Current Assets”, “Inventory Assets”, “Current Liabilities”, “Long Term Liabilities”, etc. All these tables will prove to be very useful when we build our own financial reports.
The MS Financials database, shown in Figure A-2, has two tables with the word “Account” in them. The first table, “frl_acct_code”, is the list of the individual accounts. The second table, “frl_acct_seg”, lists the account segments. When I opened up this table to see how they define segments, all I saw were references to another segment ID field. I closed the table and upon further review of the other table names in the database, I saw the tables “frl_seg_ctrl” and “frl_seg_desc”. These two tables appear to be their method for breaking down an account number into individual segments (group of numbers). Each group defines the department/division that the account applies to as well as the account type. It’s clear that prior to designing the financial reports, support documentation will have to be consulted to get a thorough understanding of how the account segments are used to structure the account number.
The QuickBooks database, shown in Figure A-3, has the most tables listed of all three databases that we discuss. Unfortunately, there is only one table relating to accounts and that is the “Accounts” table which lists the individual account codes. There doesn’t appear to be a table we could use that specifies an account type. Nor is there anything related to helping us classify the accounts into groups. We’ll have to make a mental note about that and continue looking for the other necessary tables.
So far in our search for a table that lists the account types, we see that the Xtreme.mdb table will be the easiest to report from. It has ample tables for classifying accounts into groups. The MS Financials database uses a segment notation for classifying accounts and the QuickBooks database doesn’t appear to have anything related to account types (let’s hope we find out otherwise before we are done).
From what we’ve seen of the database structures, we can already make a logical assumption about which accounting software is tailored to which type of company. The MS Financials database uses a segment notation to break down accounts into different departments and divisions. Clearly, this is designed for a large corporation which needs separate financial statements for each department/division and they are probably in different locations around the world. At the opposite end of the spectrum is the QuickBooks database which, as far as we can tell, doesn’t even break down accounts into their type. This is geared for a small sized business which doesn’t have sophisticated reporting requirements.
