DataBlox OfficeQ Queries

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.

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: Z - P11 - - Name: Z035 - Verify MDB has latest fields

If sMemo80 field doesn't exist (bechtold_2012) - it won't be in the output.

If it exists but is always empty (sprm_prod*) - the field will show in the output but will be empty.

If it exists and is sometimes filled, will show values in output (dblx_real*).

Field sMemo80 is about 60% thru the field list

SELECT dDate, sTranType, kDoc, sMemo80

FROM aTranLine

WHERE LEN(sMemo80) > 0

Group: Z - P15-e - X Name: Z025 - Example - date math test

These are a few ways of selecting a date range using the current date as a reference. The date range could start before the current date and end after the current date.

SELECT *
FROM aTranDoc
WHERE dDate BETWEEN #1/1/2013#
  AND DATEADD("d", 60, #1/1/2013#)
ORDER BY dDate

----------------------------- also worked

WHERE dDate BETWEEN DATE() - 30 AND DATE()

WHERE dDate BETWEEN
  DATE() AND DATEADD("d", 30, DATE())

WHERE dDate BETWEEN
  NOW() and DATEADD("y", 20, NOW())

WHERE dDate BETWEEN
  #1/1/2013# AND DATEADD("d", 60, #1/1/2013#)

Group: Z* - P22 - - Name: Z990 zquery to external file

Entered 120105 to test syntax for querying a file other than the current MDB file.

Line is too short to display whole file path

SELECT * FROM aAccount
IN 'L:\dropbox\qbxml\officeq_user\file.mdb'

Group: CZ - P30-d - Ji Name: C300 Customers - TranLines: named subset of customers

Uses LIKE to only show transactions where the customer's name fits a pattern.

SELECT
  cCustJob.nKey,
  sFullName,
  cCustJob.sEntityType,
  sTranType,
  kEntity,
  mSumIncome,
  mSumExpense,
  mAmount
  
FROM
  cCustJob INNER JOIN aTranLine
    ON cCustJob.nKey = aTranLine.kEntity

WHERE cCustJob.sFullName LIKE 'A*'
   AND bDocMain = True
   AND bNoPost = False

ORDER BY cCustJob.sFullName, aTranLine.kDoc