m m
Technical Support
OfficeQ - SQL Queries

This is a list of example queries that have been tested and can be used with Microsoft Access, Microsoft Excel, or any other environment that supports Microsoft's Jet database engine. Most of these queries should also work in other database environments, such as SQL Server or MySQL while others may require small tweaks. Most of these queries are examples that are useful as is - but some should be viewed as examples where the main purpose is to show a feature.

The queries have been grouped into categories. You can click on a category below to view the actual queries of that category. Where there is a line of dashes (like -----------) in the query, only the part of the query above the dashes is valid SQL syntax. Text below the dashes is alternate text for the query or notes.

The are a couple of fields (most notably aTranLine fields mSumIncome and mSumExpense) that have been added to OfficeQ since the last version shipped. Any query using either of these fields will may not work with other than the latest version of OfficeQ.

This set of queries uses more features of Microsoft's Jet database query engine than any other site we could find through search engines. It's a great reference for Microsoft Office users. This is only a small sampling of the queries that are possible. The goal here is to stimulate ideas - not be a complete list - however we will add to this list over time.

The queries listed below are generally arranged from simple to complex. Normally, queries that get their data from only 1 table and do not aggregate any fields are listed first.  To get full benefit of these queries and create your own queries - you should understand joins (where multiple tables are combined into a single table) and aggregation (where numeric columns from multiple rows are combined into 1 column).  These concepts are not as difficult as they first appear and a web search for 'SQL' and 'join' or 'aggregation' will bring up hundreds of tutorials.

Where a line of dashes (like: --------------------) appears in a query, a useful variation of the query is shown.  It could be very simple, such as changing the sort order of the output - or more complex, such as a complete alternate query.  Where a single line in the query has the character: # as it's leftmost character - that line is a comment and is not processed by our query runner.

Query Categories

Account / Journal / Budget (A)

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.

Customers / Jobs / Receivables (C)

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

Employee (E)

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.

Inventory / Fixed Asset (I)

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"

Job (not receivables) or Time Tracking (J)

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 or associated (P)

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.

Transaction (other) (T)

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.

Vendor or OtherName (V)

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.

Other (Company, Settings, ...) (X)

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

SQL feature or other not QB related (Z)

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.

Back to OfficeQ Overview