To enhance the result produced by a query, you can use some of the built-in functions of Microsoft Access, including those we saw in Lessons 11-14. You can use a function to control the values that would display in the query or you can include the functions in the condition set to filter the values.
To control how the values would display in the query, start a query in Design View or open a query in Design View. In the bottom section of the window, in the box of the field name, type the expression. For example, if you have a column named Gender and that display the genders as Male or as Female but you want to display only M or F respectively, you can use the Left() function in an expression as Left(Gender, 1):
In the same way, you can use any of the functions we have seen so far.
To use a function in a criterion, open the query in Design View and select the column(s) you want. In the lower section of the window, click the Criteria box that corresponds to the column that will hold the criterion and type the expression that includes the function. For example, on a list of students that includes their dates of birth in a column named DOB, to get the list of students born in 1994, you would set the condition as Year([DOB])=1994. Here is an example:
The Domain-Based Functions
Besides the functions we reviewed already, there are many other functions available in Microsoft Access. For example, a domain-based function is used to get a value from another object and deliver it to the object in which it is being used or called. The general syntax of these functions is:
FunctionName(WhatValue, FromWhatObject, WhatCriteria)
To perform its operation, a domain-based function needs three pieces of information, two of which are required (the first two arguments) and one is optional (the third argument).
when calling one of these functions, you must specify the value of the column you want to retrieve. This is provided as the WhatValue in our syntax. This argument is passed as a string.
The FromWhatObject is the name of the object that holds the value. It is usually the name of a form. This argument also is passed as a string.
The third argument, WhatCriteria in our syntax, specifies the criterion that will be used to filter the WhatValue value. It follows the normal rules of setting a criterion.
Domain First: If you want to find out what was the first value entered in the cells of a certain column of an external form or report, you can call the DFirst() function.
Domain Last: The DLast() function does the opposite of the DFirst() function: It retrieves the last value entered in a column of a form or report.
Domain Sum: To get the addition of values that are stored in a column of another form or report, you can use the DSum() function.
Domain Count: The DCount() function is used to count the number of values entered in the cells of a column of a table.
Domain Average: The DAvg() function calculates the sum of values of a series and divides it by the count of cells on the same external form or report to get an average.
Domain Minimum: The DMin() function is used to retrieve the minimum value of the cells in a column of an external form or report
Domain Maximum: As opposed to the DMin() function, the DMax() function gets the highest value of a series of cells in the column of an external form or report.
Consider the following list of students:
There are various types of statistics you may want to get from this list: you may want to know the number of students registered in the school, you may want to know the number of girls or the number boys, you may want to know how many students were born in each year, you may want to know the average age of the students, you may want to know the oldest or the youngest students, or you may want to know the number of students from each ZIP code. To assist you with getting these statistics, Microsoft Access provides a type of query called a summary query.
A query is referred to as summary if it provides one or various analytic statistics about the records.
Practical Learning: Introducing Summary Queries
Creating a Summary Query
As always, when creating a query, you can use the Query Wizard or display one in Design View. If you are working in Design View, to make a query summarize its data:
After applying the Totals feature, each column of the query would become equipped with a row named Total.
Although you can create a summary query with all the fields or any field(s) of a query, the purpose of the query is to summarize data, not to review the records, which you would do with a normal select query. For a good summary query, you should select a column where the records hold categories of data. This means that the records in the resulting list have to be grouped by categories.
To support summary queries, the SQL provides the GROUP BY clause. It means where the records display, they would be grouped by their categories. For example, if you want to get the number of students by gender from a list of students, you would select the column that holds that information, but you can select other columns also:
When the results come up, they would be grouped by their categories:
As stated already, the purpose of a summary query is to provide some statistics. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant:
If you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once:
Practical Learning: Creating a Summary Query
To get the types of statistics you want, in the Design View of the query, add the same column one more time, and click the Total box that corresponds to the column:
In reality, a summary query uses some of the functions that ship with Microsoft Access:
Practical Learning: Summarizing
SELECT [Company Assets].[Asset Type], Count([Company Assets].[Asset Type]) AS Qty, Sum([Company Assets].[Purchase Price]) AS [Total Spent], Avg([Company Assets].[Purchase Price]) AS Average FROM [Company Assets] GROUP BY [Company Assets].[Asset Type];
SELECT Properties1.[Property Type], Min(Properties1.[Market Value]) AS Cheapest, Max(Properties1.[Market Value]) AS [Most Expensive] FROM Properties1 WHERE (((Properties1.[Property Type]) Is Not Null)) GROUP BY Properties1.[Property Type];
|Previous||Copyright © 2010-2019, FunctionX||Next|