To add the 4th Quarter results, we will add a final UNION to the query.
1.Select the "Add UNION" button from Query Builder Toolbar.
2.Add the "InvoiceHeader" to the query by selecting the table, and then selecting the "Add Table/View" button.
3.Right click on the "InvoiceHeader" table under "Tables/Views In Use", and choose "Select Columns" from the speed menu.
4.In this example, select the "New Expression" button to display the Expression Builder.
5.Enter the value "4" into the "Expression Text" panel. The value "4" is the numeric value that will represent the 4th quarter of the figures in the query. Press the "OK" button to save the expression.
6.Select the "New Expression" button to once again display the Expression Builder.
7.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))"
8.Select the "Use" button to add the function to the "Expression Text" panel.
9.Place the cursor within the set of parenthesis and delete the "arg" value.
10.Then, with the cursor still in the parenthesis, use the "Select Columns" list box to locate and select the "InvoiceTotal" column.
11.Once selected, press the "Add Column" button to insert the column into the "Expression Text" panel.
12.Click the OK button to close the Expression Builder.
13.Click the OK button to close the column selection dialog and return to the Query Builder main window.
14.Right click on the "InvoiceHeader" table under "Tables/Views In Use", and choose "WHERE Clause" from the speed menu.
15.Use the "Select Columns" list box to locate and select the "TransDate" column.
16.Once selected, press the "Add Column" button to insert the column into the "Expression Text" panel.
17.From the "Operators and Keywords" panel, select the "BETWEEN" button to add the keyword to the WHERE Clause panel.
18.Type in the fourth quarter starting date of "10/01/2018".
19.From the "Operators and Keywords" panel, select the "AND" button to add the keyword to the WHERE Clause panel.
20.Type in the fourth quarter ending date of 12/31/2018".
21.Click the OK button to close the WHERE Builder.
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
UNION SELECT 2,(SUM( T1.InvoiceTotal ))
FROM InvoiceHeader T1
WHERE T1.TransDate BETWEEN 04/01/2018 AND 06/30/2018
UNION SELECT 3,(SUM( T1.InvoiceTotal ))
FROM InvoiceHeader T1
WHERE T1.TransDate BETWEEN 07/01/2018 AND 09/30/2018
UNION SELECT 4,(SUM( T1.InvoiceTotal ))
FROM InvoiceHeader T1
WHERE T1.TransDate BETWEEN 10/01/2018 AND 12/31/2018
