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.
SELECT dDate, sTranType, kDoc, sMemo80 FROM aTranLine WHERE LEN(sMemo80) > 0
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#)
SELECT * FROM aAccount IN 'L:\dropbox\qbxml\officeq_user\file.mdb'
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