Domain Aggregate Functions are like
SQL aggregate queries.
They give the result for a dataset (domain).
They differ from aggregate queries because you cannot group (GROUP BY) data.
Domain Aggregate syntax is:
DFunction(fieldname, table (domain), [criteria] )
The domain is the dataset of records being calculated. It can be a table or a query resultset.
The fieldname and dataset names must be enclosed in quotes.
Bracket are not required, but they make the statement easier to read.
In the above example, the SUM aggregate expression will be grouped by branch,
The DSUM aggregate will be the sum for the entire domain, which is the 'branchsales' table.
Here are the results:
The domain amount,
4369772.4 (the query doesn't format domain amounts), is the total of all 7 branches in the branchsales table.
The SQL statement looks like this:
SELECT branchsales.branch, Sum(branchsales.sales) AS SumOfsales, DSum("[sales]","[branchsales]") AS [Total Sales]
FROM branchsales
GROUP BY branchsales.branch;
Here is an example of using DSUM for two different fields in the same domain.
You can use the criteria statement to further narrow the domain.
In this example, the result is the same as the SUM amount for Chicago.
The most useful
Domain Aggregate Functions are listed below:
DSUM |
Total of a set of records |
DAVG |
Average of the values in a set |
DCOUNT |
Number of records in a set of records |
DFIRST |
First record in a set |
DLAST |
Last record in a set |
DMIN |
Smallest value in a set |
DMAX |
Largest value in a set |
Domain Aggregate syntax is:
DFunction(fieldname, table (domain), [criteria] )
The domain is the set of records being calculated. It can be a table or a query resultset.
DSUM
SUM will return the
SUM of all amounts in the
Balance_Due field from the table
Invoices:
SELECT SUM(Balance_due) FROM Invoices
The equivalent
DSUM statement is:
DSUM("Balance_due", "Invoices")
Here is an example where the total Balances are placed in a textbox on a form.
TotalDue.text = DSUM("Balance_due", "Invoices")
This is an example of using the
WHERE clause of a SQL statement:
SELECT Sum(Balance_due) FROM Invoices WHERE [CustomerID] = '32413'
The equivalent
DSUM statement is:
DSUM("Balance_due", "Invoices", "[CustomerID] = '32413'")
DAVG
This will return the average amount of sales in the branchsales table.
DAVG("sales", "branchsales",)
Here is the statement in code to place in a textbox.
AvgerageSales.text = DAVG("sales", "branchsales")
The SQL statement looks like this:
SELECT branchsales.branch, Avg(branchsales.sales) AS AvgSales, DAvg("[sales]","[branchsales]") AS [Average Sales]
FROM branchsales
GROUP BY branchsales.branch;
DCOUNT
DCOUNT returns the number of records in the domain.
DCOUNT("Reps", "branchsales",)
DMIN and DMAX
These return the minimum or maximum values in the domain.
DMAX("sales", "branchsales",)
The above statement will return the maximum sales figure from the branchsales table.
Copyright © 2006-2019, LQSystems,Inc. All rights reserved.