This section contains queries involving accounts. Some are simple, such as queries that show simple account fields. Other are more complex, such as queries that show the level of activity for each account, first and last dates of transactions posted, and accumulated account totals. There are queries to make sure that account balances exactly match the sums of transactions.

This category also includes several easily modified versions of profit / loss reports, balance sheets, etc. There are also many queries that separate the different types of accounts and queries for listing accounts that are not being used - as well as budget and class reports.

A unique feature is OfficeQ's ability to extract the gross profit for each transaction. Internally, for each transaction - sums of all transaction lines for P&L accounts are summed - which basically shows the amount of gross profit per transaction (fields mSumIncome and mSumExpense). These can be quickly merged to show profitability by any imaginable criteria.

Some of these queries require that certain QuickBooks features be used. For example, there are queries that categorize transactions by the income tax line (on federal forms) assigned to the account. This requires that each expense account have the proper federal form and line associated with it.

Queries that involve customers, jobs, accounts receivables, and customer related transactions are in this section. The cCustJob table contains a record for each customer or job with around 50 fields.

Transaction types that involve customers or jobs are (alphabetically): CardChrg, CashSale, Check, CustCred, CustInvc, CustPymt, InvtAdjst, and Journal.

Using the aTranLine table, you can join together any combination of criteria for creating customers lists. For example, using cCustJob and/or aTranLine, here are a few random query examples:

• extract a list of customers who bought any of a particular list of products during a specified time frame
• customers in 'top ten' in gross profit dollars in a designated combination of states
• extract email addresses for all customers with receivables that are currently more than 60 days overdue
• compare AR balances for each customer with the accumulated transaction amounts to make sure balances are correct
• get a list of all customers in the QuickBooks file who have been inactive for the prior 24 months

This section contains queries that involve employees, when they worked for the company, and associated payroll transactions and other information.

Within QuickBooks, each employee has a stored set of payroll earnings items and payroll deduction items. You can query the complete history of any of these items - both employee paid and company paid.

OfficeQ also extracts full payroll transaction data (which is not available in competing products). When the pEmployee table is combined with the aTranLine table, dozens of important payroll reports become possible. There are several examples of payroll earnings reports.

This section contains queries that involve inventory and fixed assets. Inventory 'items' include many types of items that are not intuitively obvious - such as services, tax items, and groups. Fixed assets are stored along with inventory items - although they have substantially more fields.

There are a lot of example queries in this section. Examples include reports of all items on hand, all accounts assigned to inventory items, service items used in time lines, all items currently on order sorted by due date, comparison of on hand count vs. sum of adding or deleting transactions for each stocked item.

An inventory item will be one of 12 types:

"Service", "InvtPart", "InvtAssy", "NonInvtPart", "FixedAsset", "OthCharge", "SubTotal", "Group", "Discount", "Payment", "TaxItem", "TaxGroup"

These queries involve jobs or employee time tracking. Even though jobs have fields identical to customers and each job has a customer as a parent - the focus here is on employee time tracking and time billing - and not financial (such as receivables).

Each record in the pTimeLine table is for a block of time - with info as to when it occurred, how long it was for, the customer the service was provided for, the employee or vendor who performed the service and what the actual service (inventory item) was - plus other fields.

There are queries for unbilled blocks of time, 'useage' queries for employee time, queries by customer, cross queries that intersect employee time, customer time, and service item, and any of the above separated by date boundaries.

Payroll Items are items that either contribute to an employees paycheck (such as salary and hourly wages, bonuses), or items that deduct (such as different types of taxes, health insurance, etc.). There are also taxes that need to be paid by the company separate from the employee. The payroll subsystem manages all these amounts and consolidates them for paychecks, tax authority payments, sick and vacation time reports, etc.

The most useful queries in this section combine the pPayrollItem table with the aTranLine table. You can create several types of earning reports, tax reports, liability payment reports, etc. These queries can also be used to verify that the payroll subsystem is working properly and can be used to trace complete transaction history. Also, setup data (such as expense and liability accounts currently used - and used in the past) can be monitored.

These queries involve transactions that are not particularly associated with the other groups of queries. Transactions are the most difficult part of QuickBooks to master - but also the most powerful. Because of that, we include many queries whose purpose is educational rather than producing directly useful output.

There are 29 different transaction types. Some fields are identical across all types. Some are used by a subset of the types, and a few are unique to a particular type. The best way to learn transactions is to extract the data from a small QuickBooks file, print out the table / field listings on this site, and experiment by running the queries here and observing results.

There are 2 complex SQL features that need to be mastered to produce the most powerful transaction queries. One in the SQL join - and the other is the pivot. There are many examples here to get you started.

There are also directly useful queries here - such as transaction statistics, queries for detecting incomplete data (such as class not entered or wrong quantity counts), queries showing activities per day of week, queries calculating profit dollars several ways, and listings of transactions posted to retained earnings.

These queries involve vendors, types of vendors, accounts payables, items purchased from vendors and vendor returns. When vendors are used to perform services for customers that are facilitated by the company, this area also included those details. It also includes payments made to tax authorities as a result of payroll transactions.

Transaction types that involve vendors are (alphabetically): CardChrg, Check, ItemRcpt, Journal, LiabPymt, TaxPymt, VendBill, VendCred, and VendPymt.

You can create very detailed historical accounts payable reports. When combined with inventory items, you can create any type of purchase history needed.  These queries can also be used to collect information about services performed by vendors.

This category contains queries that involve QuickBooks program settings or information about the company as a whole.

This category if a catch all for queries that may not be useful for extracting QuickBooks data - but show features, examples, or structure for special purposes.