DataBlox OfficeQ Queries

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.

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: A - P09 - - Name: A090 Account - Simple List: all fields

Lists all accounts with all fields for each - sorted as they would appear on a financial statement

SELECT *

FROM aAccount

ORDER BY  sAcctSort, sFullName

Group: A - P12 - - Name: A120 Accounts - AcctRec Only

Very simple query to see how many accounts receivable accounts are in the file.

Most files only have a single A/R account, but it is possible to have multiple. If there are multiple, make sure that they are assigned properly (multi currency, etc.)

SELECT sFullName, sAcctType

FROM aAccount

WHERE sAcctType = 'AcctRec';

Group: A - P21 - - Name: A210 Account - Sort Fields

Shows order of account output using sAcctSort and nReportSort fields. These fields should be used to control the sequence of accounts for standard accounting reports.

Note that using both fields will properly handle accounts that are missing account numbers or where assigned account numbers are not in the account number range of accounts with similar types. If all accounts have proper account numbers, only the nReportSort field is necessary

SELECT sFullName, sAcctType,
  sAcctNum, sAcctSort, nReportSort

FROM aAccount

ORDER BY sAcctSort, nReportSort;

Group: A - P22 - - Name: B220 Budget - All Fields / Records

Simple list of all budget fields and records in default order.

SELECT kAcct, nYear, nMonth, mAmount

FROM aBudget

ORDER BY kAcct, nYear, nMonth

Group: A - P25-?dm - <35 new Name: B250 Budgets - Learn Accounts

This query joins the aAccount and aBudget tables to extract budget details for each account. It outputs 3 columns - the left 3 (sAcctSort, nKey, sFullName) are from the aAmount) are from the aBudget records and show the budgeted amount for each year/month.

SELECT
  sAcctSort,
  nKey,
  sFullName,
  nYear,
  nMonth,
  mAmount

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

ORDER BY sAcctSort, sFullName, nYear, nMonth

Group: A - P28-Sd - - Name: A280 Account - Cash Flow Types / Tax Lines

Lists all accounts that have either a cash flow statement section or a tax line assigned. They are in the order they would normally occur in a financial statement.

Also lists the federal form name and line for each account. This is where it's balance rolls up to when transferred to tax forms. This is the data that external tax preparation programs use to calculate income taxes.

If an account does not have a tax form and line associated with it, postings to that account will not be transferred directly to any tax forms

SELECT
  sCashFlowType,
  sAcctType,
  sFullName,
  nTaxLine,
  sTaxLineName

FROM aAccount

WHERE  (nTaxLine IS NOT NULL
    AND nTaxLine <> 0)
  OR
       (sCashFlowType <> 'None'
    AND sCashFlowType <> 'NotApplicable')

ORDER BY sAcctSort, sAcctNum

Group: A - P29-S - Gr Name: A290 Account/Tax lines: list of tax lines used

List of all tax lines with descriptions. A "tax line" is the income tax form and line number that accounts are summed to when preparing income tax. Tax lines are used by programs such as TurboTax to prepare tax forms.

Accounts are grouped together in this list. All accounts that have the same tax line, account type, and cash flow type are grouped and the count of accounts in each group is shown.

Note that the tax line assigned can be set for each account within QuickBooks.

SELECT nTaxLine,
  sTaxLineName,
  sAcctType,
  sCashFlowType,
  COUNT(*) AS Count_Of_Accounts

FROM aAccount

WHERE nTaxLine IS NOT NULL
  AND nTaxLine <> 0

GROUP BY nTaxLine, sTaxLineName,
  sAcctType, sCashFlowType

Group: A* - P34-pm - Dl Name: T340 TranLine - Bank Depletion

This lists any transaction line which removes money from any bank account. The list is sorted by bank, date, and document (usually check) number.

Notice that field mAmount is negated so the displayed amount is positive - even though it is stored as negative because it removes dollars from an asset account.

This query could be very slow because of the dual DLookup functions. It could probably be written better as a join query.

SELECT
  DLOOKUP(
    "sFullName",
    "aAccount",
    "nKey = " & kAcct)
   AS BankAccount,
  sDocNum, dDate, sTranType, 
  sEntityType, -mAmount AS Amount

FROM aTranLine

WHERE sAcctType = 'Bank'
  AND mAmount < 0

ORDER BY
  DLOOKUP("sFullName", "aAccount",
      "nKey = " & kAcct),
  dDate, sDocNum

Group: A - P35-t - Ji Name: A350 Accounts - Tranlines Subset: using account name(s)

Show all transactions against a group of accounts - with accounts in group listed by the first few characters of their full name. (Accounts would need to have been named appropriately). Name can be adjusted if necessary to include fewer accounts.

For each account, lists all tran lines posted to the account during 2012

SELECT
  aAccount.sFullName,
  aAccount.sAcctType,
  sAcctNum,
  sFullName,
  sTranType,
  dDate,
  mAmount

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

WHERE aTranLine.bNoPost = False
  AND sFullName LIKE 'Auto*'
  AND YEAR(dDate) IN (2012)

ORDER BY sAcctSort, sFullName, aTranLine.nKey

Group: A - P35 - Ji Name: B350 Budget - Simple List

Simple list of all monthly (the only time period option) budgets in the file. Table aBudget is joined with table aAccount so the name and sorting of each account can be displayed.

This is about the simplest join query possible.

SELECT
  sAcctSort,
  nKey,
  sFullName,
  nYear,
  nMonth,
  mAmount

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

ORDER BY sAcctSort, sFullName, nYear, nMonth

Group: AC - P38-st - JiSu Name: C385 Receivables - By Account Type: analysis

Output all tranLines that post to accounts receivable type accounts for customers or jobs that currently owe money. Positive dollar amounts are invoices - negative are payments. Both invoices and payments for each customer or job are sorted by date.

Useful for understanding A/R transactions. Note that most of the tranlines should be from type CustInvc or CustPymt transactions. Occasionally Check or CustCred will be seen. Any other transaction types should be suspect. Also, payments do not necessarily correspond directly with invoices. Sometimes payments are for a portion of an invoice - and sometimes a single payment is for multiple invoices. The aTranLink table needs to be used to connect payment chunks with invoices.

Note that this query uses both an inner join and a subquery. It could have also been done with a 3 table join.

SELECT cCustJob.sFullName, sAcctType,
  dDate, sTranType, nLine, aTranLine.sEntityType,
  dDate AS dSold, dDue, mAmount

FROM aTranLine INNER JOIN cCustJob
ON aTranLine.kEntity = cCustJob.nKey

WHERE (aTranLine.kAcct IN (
  SELECT nKey
  FROM aAccount
  WHERE sAcctType = 'AcctRec'))

  AND bNoPost = False
  AND mBalance > 0

ORDER BY sFullName, dDate

Group: A - P40-S - JlGrHa Name: A400 Account - Unused

Lists all accounts that have never had a transaction posted to them.

This works because all tran lines have a valid value in dDate.

Could be used to delete extraneous accounts. There are some accounts, however, that QB has created that cannot be deleted. Most of them are special accounts - which are marked here with the special account type

SELECT
  aAccount.sAcctType,
  sAcctNum,
  sFullName,
  aAccount.sAcctType,
  sSpecAcct,
  sDescr,
  COUNT(dDate) AS CountOfTrans

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

GROUP BY sAcctSort,
  nReportSort, aAccount.sAcctType,
  sAcctNum, sFullName, aAccount.sAcctType,
  sSpecAcct, sDescr

HAVING COUNT(dDate) = 0

Group: A* - P40-S - Su Name: A440 Accounts - Balance Sheet Diffs: between 2 dates

Prints a balance sheet as 2 separate dates - showing the difference between the 2 dates (a time span).

To change the dates, manually enter the desired dates. Unlike QuickBooks reports, any arbitrary pair of dates can be used. The format for each date is #10/15/2010#.

Note that if the entries for an account are all blank, there has been no activity for that account during the time span. The sAcctSort field will be < 50 for any balance sheet account.

SELECT
  nKey, sAcctType, sAcctSort,
  sAcctNum, sFullName,
  
  (SELECT SUM(mAmount) FROM aTranLine
   WHERE bNoPost = False
   AND kAcct = aAccount.nKey
   AND dDate <= #12/31/2010#) AS Bal_123110,

  (SELECT SUM(mAmount) FROM aTranLine
   WHERE bNoPost = False
   AND kAcct = aAccount.nKey
   AND dDate <= #12/31/2011#) AS Bal_123111,

   Bal_123111 - Bal_123110 AS Difference

FROM aAccount

WHERE sAcctSort <= '50'

ORDER BY sAcctSort, sAcctNum

Group: A - P41 - JiGr Name: A430 Account/Tax Lines: summed account totals

Adds all TranLines within a date span (typically one year) - summed by account tax lines. This compresses all dollar amounts for the appropriate line on the tax form for one year.

Note that the date span could be modified to include any years, quarters, etc

SELECT sTaxLineName,
  sCashFlowType,
  SUM(aTranLine.mAmount) AS SumTrans

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

WHERE aTranLine.bNoPost = False
  AND sTaxLineName IS NOT NULL
  AND YEAR(dDate) IN (2012)

GROUP BY sTaxLineName, sCashFlowType

Group: A - P45-t - Jl Name: A450 Accounts - Using Any P&L Account: in data range

List all transactions that posted to any P&L account during a specified date range. This is useful for examining activity during a small date range.

The format of each date needs to be like: #11/15/2013#.

Note that a P&L account is any account with sAcctSort >='50'

SELECT
  aAccount.sAcctType, sFullName,
  sSpecAcct, dDate, mAmount,
  kDoc, sTranType

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

WHERE sAcctSort >= '50'

  AND dDate
    BETWEEN #12/15/2013# AND #12/17/2013#

  AND aTranLine.bNoPost = False

ORDER BY aTranLine.nKey

Group: A - P47 - J3 Name: L470 Class - All TranLines Containing

Shows all translines where a class has been entered - including the class name, tran type, date, and account name and type for each.

Simple example of a 3 table join. Note that sorting by nKey implies sorting by dDate.

SELECT aClass.sFullName,
  sTranType, dDate, kDoc, nLine, sAcctSort,
  sAcctNum, aAccount.sFullName,
  aAccount.sAcctType, mAmount

FROM
  (aClass INNER JOIN aTranLine
  ON aClass.nKey = aTranLine.kClass)
  INNER JOIN aAccount
  ON aAccount.nKey = aTranLine.kAcct

WHERE bNoPost = False

ORDER BY sAcctNumaTranLine.nKey

Group: A - P50-eS - JiGrX Name: A500 Account Totals vs. TranLines: Integrity

Query that verifies account balances against totals of all debits and credits in transaction lines.
This is the basic 'integrity check' of transactions in any QuickBooks file. If there are any accounts where the 'Difference' column is not 0 - there is a data problem and reports should not be trusted.

Does not list accounts that have never had activity.

SELECT aAccount.sAcctType, sFullName, mBalance,
  SUM(mAmount) AS SumOfAmount,
  mBalance - SUM(mAmount) AS Difference,
  COUNT(mAmount) AS CountOfAmount

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

WHERE aTranLine.bNoPost = False

GROUP BY sAcctSort, aAccount.sAcctType,
  sFullName, mBalance

Group: A - P50 - J3 Name: L500 Classes - P&L Trans Without

List all TranLines that post to the retained earnings account - but don't have a class assigned - sorted descending (most recent first).

This could be used when you need classes assigned for proper categorization. It shows all entries that would not be categorized.

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 aTranLine.kClass IS NULL

ORDER BY aTranDoc.sTranType,
  aTranDoc.dDate,
  Doc_nKey DESC

Group: A - P51-dsx - JlGrHa Name: 0095 Account - Stats - Dates, Counts by account

Show all accounts that have ever been posted to in the order they would appear on financial reports. For each account, shows statistics such as the account type, when the first posting to the account occurred, when the last posting occurred, count of postings, etc.

SELECT
  aAccount.nKey,
  aAccount.sAcctType,
  sFullName,
  MIN(dDate) AS FirstTransDate,
  MAX(dDate) AS LastTransDate,
  COUNT(dDate) AS CountTrans

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

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

HAVING COUNT(dDate) > 0;

Group: AC* - P51-S - JiSuGr Name: C500 Receivables vs. Tranlines: integrity

Verifies that the mBalance (amount they owe) field of each customer or job matches the sum of transactions.

The Difference column should be $0.00 for each. Any other value signifies a problem in the A/R system for that customer.

This is a complex, but very important query. It could have also been done with a 3 table join and would probably execute more quickly.

SELECT cCustJob.nKey,
  sFullName,
  mBalance,
  SUM(aTranLine.mAmount) AS SumOfmAmount,
  mBalance - SUM(aTranLine.mAmount) AS Difference,
  COUNT(aTranLine.mAmount) AS CountOfmAmount

FROM cCustJob INNER JOIN aTranLine
  ON cCustJob.nKey = aTranLine.kEntity

WHERE (aTranLine.kAcct IN (
    SELECT nKey
    FROM aAccount
    WHERE sAcctType = 'AcctRec' ))
  AND
    aTranLine.bNoPost = False

GROUP BY cCustJob.nKey, sFullName, mBalance

ORDER BY sFullName

Group: A* - P55-x - JiGrHa Name: A550 Accounts - Balance Sheet: as of specified date

Prints a balance sheet as of a certain date - showing the summed total of each account for all postings to that account through that date.

All dollar sums have correct amount and polarity, except Retained Earnings. Also Net Income is not calculated here.

SELECT
  aAccount.nKey,
  aAccount.sAcctType,
  sAcctSort,
  nReportSort,
  sAcctNum,
  sFullName,
  SUM(IIF(MID(sAcctSort, 3, 1)="+",
    mAmount, -mAmount)) AS Balance

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

WHERE aTranLine.bNoPost = False
  AND
    aAccount.sAcctSort < '50'
  AND
    aTranLine.dDate <= #12/15/2013#

GROUP BY aAccount.nKey, aAccount.sAcctType,  
         sAcctSort, sAcctNum, nReportSort,
         sFullName 

HAVING COUNT(dDate) > 0

ORDER BY sAcctSort, nReportSort, sAcctNum;

Group: A - P61 - <70 datesX Name: B770 Budgets - vs. Actuals

Shows budgeted vs. actual amounts for each month.

SELECT
  aAccount.kAcct,
  aAccount.sFullName,
  aBudget.nYear,
  aBudget.nMonth,
  aBudget.mAmount AS Budgeted,
  SUM(aTranLine.mAmount) AS Actual

FROM
  (aBudget INNER JOIN aTranLine
    ON aBudget.kAcct = aTranLine.kAcct)
  INNER JOIN aAccount
    ON aTranLine.kAcct = aAccount.nKey

GROUP BY
  aAccount.kAcct,
  aAccount.sFullName,
  aBudget.nYear,
  aBudget.nMonth,
  aBudget.mAmount

ORDER BY
  aAccount.kAcct,
  aAccount.sFullName,
  aBudget.nYear,
  aBudget.nMonth

---------------------------------------------
WHERE YEAR(dDate) IN (2011)

Group: A* - P81-etx - J3 Name: A810 Accounts - P&L Detail (EXCEL MULT LEVEL EXAMPLE1)

Similar to Profit & Loss Detail QB report.

P&L accounts (only)

Note that a P&L account is any account with sAcctSort >='50'. Also, this query will only run on a current file because of the date range.

SELECT
  sTranType,
  dDate,
  sDocNum,
  xEntity.sFullName,
  mAmount,
  sAcctNum,
  sAcctSort,
  aAccount.sAcctType,
  nReportSort,
  aAccount.sFullName

FROM (aAccount INNER JOIN aTranLine
  ON aTranLine.kAcct = aAccount.nKey)
INNER JOIN xEntity
  ON aTranLine.kEntity = xEntity.nKey

WHERE sAcctSort >= '50' AND sAcctSort < '90'
  AND YEAR(dDate) IN (2013)
  AND bNoPost = False

ORDER BY sAcctSort, nReportSort, dDate

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: A - P86-ms - PiJiGrX Name: A860 Account/Tax lines - summed by time period

Show the annual totals for transaction totals by income tax line for multiple years.

This report simple sums all account totals for accounts that sum to the same line on income tax preparation reports.

The time periods here could be changed to different years as well as by months, quarters, etc.

TRANSFORM SUM(aTranLine.mAmount)

SELECT sTaxLineName, sCashFlowType

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

WHERE aTranLine.bNoPost = False
  AND sTaxLineName IS NOT NULL
  AND YEAR(dDate) IN (2011, 2012, 2013)

GROUP BY sTaxLineName, sCashFlowType

PIVOT YEAR(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")