In the Query Builder you will see a list of tables and views within a panel to the left that can be added to your query. The list contains all tables and views for the connected database. The table used in this example is InvoiceHeader.
1.Add the "InvoiceHeader" to the query by selecting the table, and then selecting the "Add Table/View" button.
The table should now be listed under "Tables/Views In Use". With the table added, the specific columns to be used in the query must be selected.
2.Right click on the "InvoiceHeader" table under "Tables/Views In Use", and choose "Select Columns" from the speed menu.
The "InvoiceHeader" columns will be displayed to be added to the query. This dialog window is basically a graphic representation of the SELECT command. When working with columns in your queries, expressions can also be added to the columns to perform calculations.
3.In this example, select the "New Expression" button to display the Expression Builder.
4.Enter the value "1" into the "Expression Text" panel. The value "1" is the numeric value that will represent the 1st quarter of the figures in the query. Press the "OK" button to save the expression.
5.Select the "New Expression" button to once again display the Expression Builder.
6.Within the the "Function Templates" panel, select use the drop-down combo box to select the aggregate function "SUM", which will be displayed as "(SUM(arg))"
7.Select the "Use" button to add the function to the "Expression Text" panel.
8.Place the cursor within the set of parenthesis and delete the "arg" value.
9.Then, with the cursor still in the parenthesis, use the "Select Columns" list box to locate and select the "InvoiceTotal" column.
10.Once selected, press the "Add Column" button to insert the column into the "Expression Text" panel.
The Expression panel should have the following displayed:
(SUM( T1.InvoiceTotal ))
11.Click the OK button to close the Expression Builder.
12.Click the OK button to close the column selection dialog and return to the Query Builder main window.
13.Right click on the "InvoiceHeader" table under "Tables/Views In Use", and choose "WHERE Clause" from the speed menu.
The WHERE Builder will be displayed to place a condition on the view in order to limit the records displayed.
14.Use the "Select Columns" list box to locate and select the "TransDate" column.
15.Once selected, press the "Add Column" button to insert the column into the "Expression Text" panel.
16.From the "Operators and Keywords" panel, select the "BETWEEN" button to add the keyword to the WHERE Clause panel.
17.Type in the first quarter starting date of "01/01/2018".
18.From the "Operators and Keywords" panel, select the "AND" button to add the keyword to the WHERE Clause panel.
19.Type in the first quarter ending date of 03/31/2018".
The Expression panel should have the following displayed:
TransDate BETWEEN 01/01/2018 AND 03/31/2018
20.Click the OK button to close the WHERE Builder.
Back in the Query Builder main window, you should see the SQL syntax displayed in the bottom of the page as:
SELECT 1,(SUM( T1.InvoiceTotal ))
FROM InvoiceHeader T1
WHERE TransDate BETWEEN 01/01/2018 AND 03/31/2018
