DataBlox OfficeQ Queries

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

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: X - P10 - - Name: 0090 Settings - all (to verify file) - has excel args

Used to verify the company this MDB file belongs to if you have lots of files. Note that this is a single record table - so filtering, sorting, ect. does not make sense

SELECT *

FROM xSettings

Group: X - P11 - - Name: L110 Class - Simple List

Simple list of all fields from record type aClass. Output is sorted by the sFullName field.

Note the difference between sorting by nKey vs sorting by sFullName. Because classes can be nested, sorting by sFullName groups nested classes together

SELECT *

FROM aClass

ORDER BY sFullName

Group: X - P11 - - Name: X110 Company - Identification

Used to identify company if you have lots of files. Note that this is a single record table - so filtering, sorting, ect. do not make any sense.

There are several other fields that could be added to the SELECT list

SELECT sCompanyName, sCountry, bSample,
  sFiscalMonth, sTaxMonth, sCompType, sTaxForm

FROM xCompany

Group: X* - P47-e - Un Name: X470 Entity - All Names Unioned

List all people in the file alphabetically by last name. This combines all customers, jobs, vendors, employees, and other names.

Notice that the same person often appears more than once from CustJob because jobs are nested under customers

SELECT sLastName, sFirstName, sMidName,
  'Customer' AS Type,
  sFullName AS CompanyName
FROM cCustJob
WHERE LEN(sLastName) > 0

UNION
SELECT sLastName, sFirstName, sMidName,
  'Vendor', sName
FROM vVendor
WHERE LEN(sLastName) > 0

UNION
SELECT sLastName, sFirstName, sMidName,
  'Employee', '' FROM pEmployee
WHERE LEN(sLastName) > 0

UNION
SELECT sLastName, sFirstName, sMidName,
  'OtherName', '' FROM xOtherName
WHERE LEN(sLastName) > 0

ORDER BY sLastName, sFirstName, sMidName