SQL Wizard - Field in Query
To select a table, click on the drop down box next to Tables and a list of all the tables available will appear. Different icons are used to identify fields that contain text, numbers, numbers that include decimals, selections, and dates and times. Use these icons as a guide when determining the type data contained in the field. Choose each Table you want to use in the query and using the Add Column and/or Add All Columns buttons, select the columns to appear in the result. The Remove Column button will remove the field from the result.
Once columns have been selected, pressing the icon next to the Tables selection box will display all tables that relate to the current tables and fields selected. This makes it easier to determine what other tables may need to be included in your query.
Always use Table - The "Always Use Table" is used when you want to use a particular table in your WHERE statement, but do not need to show the columns of the table in your final result. If the column is used in the final result, the table is automatically selected and used.
Reset - By pressing the "Reset" button, all the information in all screens of the query wizard are cleared.
Formula – Select this option to create formulas from different fields. Once a formula is created, the other tabs available can be used to format the the columns, change the column labels or highlight specific results.
-
Formula - From this tab you can add, subtract, multiply or divide the data contained in different fields to create a new column. You can create a column that would give you the total number of guests arriving on a certain day by adding together the fields for adults and children, or the total amount owed by a company by adding the amounts from the 30, 60, 90, >120 day fields of the city ledger aging report.
-
Format - On this tab the column title can be overridden. Also, the font for the field created can be overridden using the Font Dialog button. Items in the Override Positions section can be used to justify the data in the column, adjust the height and width of the column, and select the picture of the column by pressing the look up button Filter icon2. From here you can select a numeric picture or a Month, Abbreviated Month, or Day of the Week, depending on the column being formatted. To display only one of each result, like a sub header, check the Suppress if Duplicated box . Select the Hide Column option to hide the results from being displayed on the final report, but the results can still be used to sort your data.
Note: Changes made on the format tab appear on the final report, NOT on the Data Grid Preview. Click here for more information on formatting columns.
-
Change Field Values – The Changed Field Values tab allows you to label what is displayed for a result. Rather than displaying 1 or 0 for a yes or no field, such as whether a City Ledger account is set a Direct Bill, the 1 can be changed to Direct Bill and 0 defaulted to Company Only. Click here for more information on changing field values.
-
Highlighting – This tab is used to highlight certain results within the column. Click the Insert This Column Name to insert the column title in the Conditional Highlighting Formula section then define a condition for the data you want highlighted. Click here for more information on highlighting.
Format Column/Change Formula - The label on this option will change depending on the column selected. For columns selected, Format Column will appear and when selected the Format, Change Field Values and Highlighting tabs will appear. if the column is a formula, Change Formula appears and the Formula tab also appears, along with Format, Change Field Values and Highlighting.
If the column selected contains email addresses, the Domain Checking tab is displayed.
- Domain Checking - This tab only appears for fields that contain email addresses. Domain Checking will validate the domain name for an email address and add a column to display error messages. Select Domain Checking - List all results to display all results or Domain Checking - Include Only Valid or Domain Checking - Include Only Invalid to display only email addresses with valid domain names or invalid domain names, respectively. Click here for more information on domain checking.
Fix All Titles - Click this button to have the Column Description used as the title of the column rather that the field name. Instead of having CONFNUM as the column title on the report, clicking Fix All Titles will rename the column title to "Confirmation Number". All fields that have been selected will have the column title changed to their description. See Format Column above regarding editing the Column Title for each individual field.
Grouping Data
Difference between Sum and Total Column
The Group By option allows you to sum, count or average a Group of results where-as the Total Columns option at the bottom of the screen will simply create totals and sub-totals of the data you select.
Example of Group By (SUM):
| Date | Type | Amount |
|---|---|---|
| 2007-01-01 | Cash | 50.00 |
| 2007-01-01 | Visa | 240.00 |
| 2007-01-01 | Amex | 127.00 |
| 2007-01-01 | Discover | 300.00 |
Example of Column Totals:
| Date | Folio | Type | Amount |
|---|---|---|---|
| 2007-01-01 | 100000 | Cash | 50.00 |
| 2007-01-01 | 100090 | Visa | 50.00 |
| 2007-01-01 | 100091 | Visa | 190.00 |
| 2007-01-01 | 100990 | Amex | 127.00 |
| 2007-01-01 | 100991 | Discover | 290.00 |
| 2007-01-01 | 100911 | Discover | 10.00 |
| 717.00 |
Example of Sum and Column Totals:
| Date | Type | Amount |
|---|---|---|
| 2007-01-01 | Cash | 50.00 |
| 2007-01-01 | Visa | 240.00 |
| 2007-01-01 | Amex | 127.00 |
| 2007-01-01 | Discover | 300.00 |
| 717.00 |
Group By -Think of "Summing" when you want the system to only report on the total/summary of the group rather than each detail record. The above results would be in relation to the JOURNAL file (each individual file). The first example, shows just the total of each Account Type. In the middle example, each individual transaction is displayed, and then the column is totalled. The final example shows both. The drop down box after selecting Group By will display SUM, COUNT, AVERAGE, MINIMUM, and MAXIMUM. Each of these can be used the same fashion as the SUM button to get a count on the data or the average of the data, or the minimum or maximum value of the data requested.
- CUSTOM FORMULA is also contained in the drop down box. Selecting this allows you to create a formula including multiple fields and then either summarize, get a count, an average, or the minimum or maximum value of the data created by the formula.
For more information on Grouping Data, click here.
No Total / Sum / Average / Count / Absolute Count
You can select to get a total (sum), average or count to the field you are selecting and have the result show at the bottom of the column. Only numeric fields are available to total. For more information on each type, click here.
Once you have select the columns and table(s) you wish to appear in your query, press Next to continue.
If you have chosen to use more than one table, you will then need to define how the tables are related. Otherwise, you will then define which records are included in the query.