|
|
|
Technical Support
|
 |
This page discusses why using SQL is the best way to extract data from QuickBooks and how to go about ...why SQL... ...long list of what example queries can produce... ...single MDB file = 1 QB file... ...specific tables often used together...
SQL is a language used by virtually all databases to retrieve data. It's original goal was to have a single, easy to learn, set of phrases that could be used to manipulate complex data interchangeably between database products from several vendors.
We strongly recommend learning as much as you can about SQL queries. They are the key to extracting data from QuickBooks that is not available from standard reports. SQL knowledge is also very useful for general business computing.
OfficeQ extracts data into a set of tables that are fully linked using ...
To get meaningful data from this system, you typically need to select certain rows and columns, combine data from different tables, sort the data in different ways, etc.
Queries are how this is done. We get many questions from potential customers asking if a particular report, graph, or set of data can be generated. Because the data is all there - the answer is yes in almost every case - if you know how to write the query.
The end result of all SQL SELECT queries is a rectangular block with rows and columns - exactly what a spreadsheet or database report is optimized to handle.
Using SQL queries with the data extracted from OfficeQ enables ...what queries can do...
...link to page containing queries (and link back)...
...sql features to understand...
...
remainder of this page copied from old help file and applies mainly to Access. Make sure to link to queries list (although it's in the top)
Once you understand the basics of queries, there is an additional form in QACC_CODEC.MDB you might find useful. Form mainQuery is a rudimentary form that can be used for query experimentation. It stores the SQL text of queries in table zQuery (which is internal - not attached).
This is useful in 2 ways. First, many people like the simplicity of simple SQL text. In our opinion, it's easier to start with simple SQL and progressively advance. Also, Access's query design window can't be used to create subqueries, union queries, or use other advanced features. These are a very important part of SQL and often simplify, not complicate, the task.
Form mainQuery is simple to understand, but not very bullet proof. The combo box at the top allows you to pick a query. The panel at the left lets you keep notes about each query - and the panel at the right contains the SQL text for the query. The Query List button brings up the table containing the list of queries so you can add, delete, change the name of, etc. each query. Also, you can make mistakes and not trash anything. When you click Execute - The code validates the SQL by attempting to create a temporary query. If you've made a mistake - it will tell you and will not attempt to execute it.
We have included a few dozen quickly thrown together queries to get you started. Some of them are very useful for data extraction - while others are useful as SQL examples. We must emphasize, however, that these are unsupported examples to get you started. They have not been extensively tested and will likely need to be modified to get the exact results you want. They can be very useful for understanding the data relationships, learning SQL, and creating queries to produce reports.
You can print the results of any of these queries by using the mouse to adjust the column widths - then printing. Unfortunately, column widths are not saved with each query because the query is recreated from the SQL text each time it is run.
If you create a query that you want to reuse regularly, you can copy the SQL text from the right panel, create a new normal query using the Queries tab, paste the SQL, and save it for reuse. This also allows column widths to be adjusted and other properties to be changed and saved with the query. The query can then also be used as the basis for printed reports, graphs, or forms.