DataBlox OfficeQ Queries

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.

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.

Group: T - P05 - - Name: T050 TranDoc - Simple List

Very simple query that lists all tran docs - sorted by date

SELECT *

FROM aTranDoc

ORDER BY dDate

Group: T - P10 - - Name: T100 TranLine - Simple List

Simple query that lists all tran lines - sorted by data and transaction type.

It could easily be modified by adding other fields or changing the sort order

SELECT *

FROM aTranLine

ORDER BY dDate, sTranType

--------------------------------------
ORDER BY sTranType, dDate

Group: T - P12-s - - Name: T120 TranLine - Fields for Journal

Shows all fields that are used with journal entries. These are the most important transaction line fields.

To learn how transactions work - use a QuickBooks sample file and add, edit, and print some journal entries. Compare them to this query. You should be able to understand every field listed here. Then continue with more complex transaction types - which will still use these, but will add additional fields.

SELECT dDate, sTranType, kDoc,
  nLine, bDocMain, sDocNum,
  kAcct, sAcctType, mAmount,
  kEntity, sEntityType, kEntity2, sEntity2Type,
  kClass, sMemo80

FROM aTranLine

WHERE sTranType IN ('Journal')

ORDER BY dDate, sTranType, kDoc, nLine

Group: T - P13 - - Name: T130 TranLine - All of Specified sTranType

Displays all transaction lines of particular types.

sTranType can be any of the following:

Journal, Transfer, Check, Deposit

CashSale, Estimate, CustInvc,
CustChrg, CustPymt, CustCred

InvAdjst, PurchOrd, ItemRcpt
VendBill, VendPymt, VendCard, VendCred

CardChrg, CardRfnd

TaxPymt

Paycheck, LiabPymt, YtdAdjst, LiaAdjs

SELECT nKey, dDate, sTranType, sDocNum, kDoc, nLine,
  sAcctType, kClass

FROM aTranLine

WHERE sTranType IN
  ('Journal')

ORDER BY kDoc, nLine

------------------------------------------------
WHERE sTranType IN
  ('CashSale', 'CustInvc')

Group: T - P13 - - Name: T130 TranLine - That Use Field: analysis

Show all tranlines where a particular field is used. Useful for learning how a field is used.

Change the field or select list as needed

SELECT nKey, dDate, sTranType, kAcct, sAcctType, 
  mAmount, bNoPost, sEntityType, sItemType,
  dService

FROM aTranLine

WHERE dService IS NOT NULL

ORDER BY dService

Group: T - P13 - - Name: T138 TranLink - Simple List

Show all tranlinks. Tranlinks are used when one type of transaction 'feeds' another type of transaction - such as payment receipts against invoices.

Note that tranlinks are of minor importance compared to tranlines and trandocs. They are only used for a few different types of transactions and mainly for internal housekeeping

SELECT *

FROM aTranLink

ORDER BY dDate

Group: T - P14 - - Name: T140 TranLink - Find odd

Normally only transaction types CustInvc, CustChrg, and VendBill are expected to receive future payments and eventually be paid in full.

This query shows counts of other transaction types using links - which are rare. Typically they involve a CustCred - such as a check issued to refund a customer credit.

SELECT sTranTypeFrom, sTranTypeTo,
  COUNT(*) As Count

FROM aTranLink

WHERE sLinkType = "Amt"
 AND NOT (
  (sTranTypeFrom IN
    ("CustInvc", "CustChrg", "VendBill"))
  OR (sTranTypeTo IN
    ("CustInvc", "CustChrg", "VendBill"))
  )

GROUP BY sTranTypeFrom, sTranTypeTo

------------------------------
WHERE sLinkType = "Quan"

Group: T - P14-m - - Name: T145 TranLine - Shell, Frequent Fields

Simple query that can be used as a starting point for other aTranLine queries. Copy this query and use it to write more specific queries.

Some of the fields in table aTranLine are seldom used. They exist because of features that few users use (such as foreign currency or units of measure), or internal housekeeping. This query excludes the 20 most infrequently used fields: (kNextTran, sLineRoles, mAmountFC, rExchangeRate, kCurrency, kUnitOfMeas, sMeasSubUnit, rUnitPriceFC, sMemo, sCustomField1...7, mBalanceDueFC, nOrigKey, sTxnDocID, sTxnLineID)

To study particular trantypes, use the WHERE clause to restrict the query to particular trantypes. You can then eliminate additional fields that are not used with that trantype - and study what remains.

SELECT dDate, sTranType, kDoc,
  nLine, bDocMain, sDocNum,
  kAcct, sAcctType, mAmount, bNoPost,
  kEntity, sEntityType, kEntity2, sEntity2Type,
  kItem, sItemType, rItemCount, rUnitPrice,
  bUnitPricePcnt,
  mBalanceDue, mStillOwing, dPaidInFull,
  kClass, kPayMeth, bAutoTax, kSalesTax, rCountMisc,
  kPayItem, sPayItemType, dPayThrough,
  mWageBase, mPayTaxable, sMemo80,
  sBillableStatus, dService

FROM aTranLine

ORDER BY dDate, sTranType, kDoc, nLine

--------------------------------------------
WHERE sTranType IN ('VendBill', 'VendCred')

Group: T - P14 - - Name: T146 TranLine - Shell, Frequent Fields2

Similar to T145 - but excludes payroll fields.

SELECT dDate, sTranType, kDoc,
  nLine, bDocMain, sDocNum,
  kAcct, sAcctType, mAmount, bNoPost,
  kEntity, sEntityType, kEntity2, sEntity2Type,
  kItem, sItemType, rItemCount, rUnitPrice,
  bUnitPricePcnt, mBalanceDue, mStillOwing,
  dPaidInFull, kClass, kPayMeth, bAutoTax,
  kSalesTax, rCountMisc, sMemo80,
  sBillableStatus, dService

FROM aTranLine

ORDER BY dDate, sTranType, kDoc, nLine

Group: T - P15-dmt - - Name: L150 Classes - Trans Missing Classes

Useful when reporting requires all TranDocs of certain types to have the class specified. (Unfortunately QuickBooks will never force a class to be specified). TranDocs don't have the kClass field - it is in the TranLine record type (bDocMain only?)

SELECT dDate, sTranType, kDoc, kClass

FROM aTranLine

WHERE bDocMain = True

ORDER BY sTranType, dDate, nKey

-----------------------------------
  AND NOT(ISNULL(kClass))

Group: T - P15 - X Name: T155 Tran/Stats - In Date Range

Outputs all fields from all TranLines that are between 2 dates.

Change the dates to whatever is desired.

This be used as a learning tool. You can create bogus transactions at some date in the future, analyze them, then delete them. If you make them all within a specific date range, they are easy to delete

SELECT *

FROM aTranLine

WHERE dDate BETWEEN #10/1/2011# AND #12/31/2011#

ORDER BY nKey

Group: T* - P16 - AX Name: T165 TranDoc - Count Per Period

List count of transactions per month - from the first month to the last month containing transactions in the file.

SELECT
  YEAR(dDate) & '-'
    & FORMAT(dDate, "mmm") as Month,
  COUNT(*) as Count_TranDoc

FROM aTranDoc

GROUP BY
  YEAR(dDate) & '-' & FORMAT(dDate, "mm"),
  YEAR(dDate) & '-' & FORMAT(dDate, "mmm")

ORDER BY YEAR(dDate) & '-' & FORMAT(dDate, "mm")

Group: T - P17-m - Gr Name: T170 TranDoc - Count of TranTypes

Lists all transaction types that are used in the file - with a count of trandocs for each.

There are 27 transaction types possible.

The 2nd query shows how many tranlines of each type are in the file.

SELECT
  sTranType,
  COUNT(sTranType) as CountOfTranType

FROM aTranDoc

GROUP BY sTranType

---------------------------------------------------
SELECT
  sTranType,
  COUNT(sTranType) as CountOfTranType

FROM aTranLine

GROUP BY sTranType

Group: T - P17 - - Name: T175 TranDoc - Dates, First and Last

Show the dates of the first and last transactions in the file.

SELECT MIN(dDate) as DateFirst,
  MAX(dDate) as DateLast,
  COUNT(dDate) AS Count_TranDoc

FROM aTranDoc

Group: T - P18 - - Name: T180 TranDoc - Count Per Period

Show how old the data in the file is - relative to today's date. Groups transactions into quarters and shows how many quarters ago each transaction occurred.

A year has approximately 365.25 days and a quarter is 1/4 of a year - so we rounded this out to 91.3 days.

SELECT INT((DATE() - dDate)/91.3) as Quarters_Ago,
   COUNT(*) AS Count_During_Quarter

FROM aTranDoc

GROUP BY INT((DATE() - dDate)/91.3)

Group: T - P20-dm - Gr Name: T200 TranLine - Accounts / TranTypes: analysis

For each type of account, shows the type of transactions (sTranType) that occur for that account - and the count of each account / tran type combination in the current file. This helps reveal how QuickBooks uses account types.

Note the 'back tick' character used to delimit the caption for the count field. This allows spaces to be used in the caption. Underscore characters can also be used for a similar effect.

SELECT sAcctType, sTranType,
  COUNT(*) AS `Count Of AcctType_TranType`

FROM aTranLine

WHERE bDocMain=True

GROUP BY sAcctType, sTranType

Group: T - P21 - Gr Name: T210 TranLink - By TranTypes

Helps to understand TranLinks. Collects count of TranLinks from each type of tranline to other type of tranline. From this, you can see which types of tranlines require tranlinks.

When sLinkType is "Amt", dollar amounts are logged - otherwise sLinkType is "Quan" and part counts are being logged

SELECT sTranTypeFrom, sTranTypeTo,
  COUNT(*) As Count

FROM aTranLink

WHERE sLinkType = "Amt"

GROUP BY sTranTypeFrom, sTranTypeTo

------------------------------
WHERE sLinkType = "Quan"

Group: CT - P26-t - - Name: C260 Receivables - Max Aging

Notice that this is the same set of transaction lines listed with the other A/R query, even though the queries are very different

SELECT sAcctType, sTranType, nLine,
  sEntityType, sEntity2Type, dDate,
  dDue, mBalanceDue, mAmount

FROM aTranLine

WHERE sAcctType = 'AcctRec'
  AND sEntityType = 'CustJob'
  AND sTranType IN
    ('CustInvc', 'Check', 'CustPymt', 'CustCred')
  AND bNoPost = False

ORDER BY nKey
# ORDER BY sTranType, nKey

Group: T - P27-s - - Name: E270 Employee - TranLine fields used

Shows all tranlines containing an employee while excluding all fields typically not used when an employee exists. Most of these transaction lines are from paychecks.

Many of the fields in table aTranLine are seldom needed. They exist because of features that few files use (such as foreign currency or units of measure), or internal housekeeping. This query excludes those fields.

SELECT dDate, sTranType, kDoc,
  nLine, bDocMain, sDocNum,
  kAcct, aAccount.sFullName,
  aTranLine.sAcctType, mAmount,
  kEntity, sEntityType, kEntity2, sEntity2Type,
  rUnitPrice, kClass,
  kPayItem, sPayItemType, dPayThrough,
  mWageBase, mPayTaxable

FROM aTranLine INNER JOIN aAccount
  ON aTranLine.kAcct = aAccount.nKey

WHERE
  sEntityType = 'Employee'
  OR
  sEntity2Type = 'Employee'

# ORDER BY dDate, sTranType, kDoc, sLine
ORDER BY sTranType, kDoc, nLine

Group: T - P28 - - Name: T280 TranDoc - Sales Per Weekday

Display the total count of transactions that occurred on each day of the week.

In this case - only count CashSale and CustInvc type transactions are included.

This query shows how additional functionality can be extracted using functions to extract characteristics of fields - then grouping by that characteristic

SELECT
  CHOOSE(FORMAT(dDate, "w"),
      "Sun","Mon","Tue","Wed","Thu",
      "Fri","Sat","Sun")
    AS DayOfWeek,
  COUNT(*) AS Count_On_This_Weekday

FROM aTranDoc

WHERE sTranType IN
    ('CashSale', 'CustInvc')

GROUP BY
  FORMAT(dDate, "w")

Group: T - P42-eS - PiGrX Name: 0080 - Transaction Stats - Dates and Types

In QuickBooks, there are 27 different types of transactions - such as Check, Deposit, CashSale, Paycheck, etc. (see OfficeQ Transaction Types for details).

This query shows exactly which of those transaction types are used in this QuickBooks file - and how many per quarter.

Studying the results of this query can provide a lot of insight into the company's activities.

TRANSFORM COUNT(dDate)

SELECT sTranType

FROM aTranDoc

GROUP BY sTranType

PIVOT YEAR(dDate) & '-Q' & FORMAT(dDate, "q")

--------------------------------- variations:
PIVOT YEAR(dDate)

WHERE YEAR(dDate) IN (2001, 2002, 2003, 2004,
  2005, 2006, 2007, 2008, 2009, 2010, 2011,
  2012, 2013)

Group: T - P45-dv - JlSu Name: C450 Customers - TranType Counts: analysis

This lists all transaction types, with count of each - where a customer or job nKey is in the kEntity or kEntity2 field.

This shows a count of each transaction type that in any way involves a customer or job.

SELECT aTranDoc.sTranType,
  COUNT(*) AS TranTypeCount_CustJob

FROM aTranLine INNER JOIN aTranDoc
ON aTranLine.kDoc = aTranDoc.nKey

WHERE
  aTranLine.bDocMain = True
    AND
  aTranLine.bNoPost = False
    AND
  (aTranLine.kEntity
      IN (SELECT nKey FROM cCustJob)
    OR
  aTranLine.kEntity2
      IN (SELECT nKey FROM cCustJob))

GROUP BY aTranDoc.sTranType

Group: T* - P48-dms - PiJiGr Name: L480 Classes - Profit: by time period

Show the amount of gross profit for all profit (or loss) producing documents - by quarter and by class.

Ths leftmost column is for the time period - one for each quarter. Each class then has a column.

Each row contains the gross profits for each class.

TRANSFORM SUM(-mSumIncome - mSumExpense)

SELECT YEAR(dDate) & '-Q' & FORMAT(dDate, 'q')
  AS TimePeriod

FROM aClass INNER JOIN aTranLine
ON aTranLine.kClass = aClass.nKey

WHERE aTranLine.bDocMain = True

GROUP BY YEAR(dDate) & '-Q' & FORMAT(dDate, 'q')

PIVOT aClass.sFullName

Group: T* - P52-mS - Jl Name: T520 TranDoc - P&L by TranDoc

Gross profit per transaction. Shows income and expenses that can be directly attributed to each transaction.

With each document, OfficeQ sums all the income postings from each TranLine into field mSumIncome. This is also done with expenses to create mSumExpense. Both fields are listed here, as well as their difference.

This only includes costs that are directly attributable to this transaction, including cost of goods. Note that for cost of goods to be accurate in QuickBooks, all transactions before this transaction must be properly entered.

Also note that you could calculate the total Profit and Loss for a time period by just adding these entries.

SELECT aTranDoc.dDate,
  aTranDoc.nKey AS Doc_nKey,
  aTranDoc.sTranType,
  aTranLine.kEntity,
  aTranLine.sEntityType,
  -mSumIncome AS Income,
  mSumExpense AS Expense,
  (-mSumIncome - mSumExpense) AS Profit

FROM aTranDoc LEFT JOIN aTranLine
ON aTranDoc.nKey = aTranLine.kDoc

WHERE YEAR(aTranDoc.dDate) IN (2013)
  AND
    aTranLine.bDocMain = True
  AND
    aTranLine.bNoPost = False
  AND
      (mSumIncome <> 0
    OR
       mSumExpense <> 0)

Group: T - P57 - PiGr Name: T570 TranDoc - Count Per Month: by type

For any particular year(s), for each TranType (rows), show the count of transactions for each month (columns).

Multiple years can be included - however the output becomes very wide because there are 12 columns for each year.

By changing the PIVOT expression, the columns can be changed to years or quarters

TRANSFORM COUNT(dDate)

SELECT sTranType

FROM aTranDoc

WHERE YEAR(dDate) IN (2013)

GROUP BY sTranType

PIVOT YEAR(dDate) & '-' & FORMAT(dDate, "mm")

--------------------------------- variations:
WHERE YEAR(dDate) IN (2011, 2012, 2013)

PIVOT YEAR(dDate)
PIVOT YEAR(dDate) & '-Q' & FORMAT(dDate, "q")

Group: T - P62-dm - Ji3 Name: I620 Inventory - TranLines: analyze items with no rItemCount

Output all tranlines that contain invtitems with rUnitPrice but no rItemCount

SELECT
    aTranLine.sTranType,
    aTranLine.bNoPost,
    aTranLine.kDoc,
    aTranLine.nLine,
    aTranLine.rItemCount,
    aTranLine.rUnitPrice,
    aTranLine.bUnitPricePcnt,
    aTranLine.kItem,
    vInvtItem.sFullName,
    vInvtItem.sItemType,
    aTranLine.mAmount,
    aAccount.nKey,
    aAccount.sFullName
FROM
  (aAccount RIGHT JOIN aTranLine
    ON aAccount.nKey = aTranLine.kAcct)
  LEFT JOIN vInvtItem
    ON aTranLine.kItem = vInvtItem.nKey

WHERE (kItem IS NOT NULL)
  AND (aTranLine.rUnitPrice IS NOT NULL)
  AND (rItemCount IS NULL)
  AND (bNoPost = False)

ORDER BY vInvtItem.sItemType

---------------------------------------------
  AND (bNoPost = False)

Group: AT - P82 - J3 Name: T820 TranLine - To Retained Earnings

Shows all transactions that post to the retained earnings account - except the standard types.

This query can be modified to refine any data needed on P & L transactions. Any transaction that posts to the retained earnings account will affect earnings.

SELECT aTranDoc.dDate,
  aTranDoc.sTranType,
  aTranDoc.sDocNum,
  aTranDoc.nKey AS Doc_nKey,
  aAccount.sFullName AS Account,
  aTranLine.mAmount
  
FROM
  (aTranDoc INNER JOIN aTranLine
  ON aTranDoc.nKey = aTranLine.kDoc)
    INNER JOIN aAccount
  ON aTranLine.kAcct - aAccount.nKey

WHERE aAccount.sSpecAcct = 'RetEarnings'
  AND aTranLine.bNoPost = False
  AND aTranLine.mAmount <> 0
  AND aTranDoc.sTranType NOT IN
    ('CashSale', 'CustInvc',
     'Paycheck', 'VendBill')

ORDER BY aTranDoc.sTranType, aTranDoc.dDate

Group: AT - P90-s - PiJiGrX Name: A900 Accounts - Totals by Month or Quarter

Adds up account totals by month or quarter. The results contain one row for each account - sorted as if they were in a balance sheet. The number of columns is variable - depending on the expression in the PIVOT clause.

If YEAR(dDate) & '-' & FORMAT(dDate, "mm") is used, the result will contain a column for each month with the sum of all account transactions for each month.

If YEAR(dDate) & '-Q' & FORMAT(dDate, "q") is used, the result will contain columns with group the account transactions by quarter.

TRANSFORM SUM(mAmount)

SELECT aAccount.sAcctType,
    aAccount.sFullName

FROM aAccount INNER JOIN aTranLine
ON aAccount.nKey = aTranLine.kAcct

WHERE YEAR(dDate) IN (2011, 2012)
  AND bNoPost = False

GROUP BY aAccount.sAcctSort,
    aAccount.sAcctType,
    aAccount.sFullName

------------------------------------
# PIVOT YEAR(dDate) & '-' & FORMAT(dDate, "mm")
PIVOT YEAR(dDate) & '-Q' & FORMAT(dDate, "q")