Journal Entries Table

Finding which table stores the journal entries in the Xtreme.mdb database is pretty simple. It is called “Journal Entry”. The table lists the account number, a balance, whether it is a debit or credit, and a description. The MS Financials database doesn’t have any tables with the word “Journal” in them, but it does have a table name that abbreviates the word “transaction”. Upon opening “frl_tran_detail”, I find that this is the correct table because it does have the journal entries in it. In fact, this table is a bit different from what we’ve talked about because it doesn’t list whether the amount is a debit or a credit. Instead, it lists each number as a positive or negative number. The entries we’ve looked at so far were always positive and we had to look at whether it was classified as a debit or credit. But with MS Financials, the sign tells us that. Positive numbers are debits and negative numbers are credits. We get the same information from either type of table, but as we’ll see when we design the reports, we have to create a different type of formula to handle this type of data. Upon further inspection of the MS Financials database you will also see a table called “frl_tran_detail_history”. This table archives the transactions prior to the current year. This is an important table to use when creating multi-year reports. You’ll have to use the SQL UNION statement to combine the two tables into a single result set prior to reporting from it.

The QuickBooks database is a little misleading at first. If you scan through the tables, you’ll see one called “Journal Entry”. My initial hunch is that this would be the table we want. Unfortunately, it doesn’t list the journal entry transactions (and I actually can’t figure out what it does). If you continue to scan the tables you’ll see one called “Transaction”. This is the one we want. Upon opening it, I see that it lists the account number, amount, transaction date and a memo field. This table, like MS Financials, doesn’t specify whether the transaction is a debit or credit. Instead, it makes the amount positive or negative for debits and credits respectively.