If the criteria fields don't change, but the values you are interested in do change frequently, you can create a parameter query. A parameter query prompts the user for field values, and then uses those values to create the query criteria.
For more information about parameter queries, see the article Use parameters in queries and reports. The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.
A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. The syntax for this expression is as follows: HyperlinkPart [Table1].
Query result. For a list of such characters, see the article Access wildcard character reference. Returns records where the field is set to a blank but not null value. For example, records of sales made to another department might contain a blank value in the CountryRegion field. Returns records where there is either no value in the field, or the field is set to a blank value. Note: The characters? The following examples are for the UnitPrice field in a query that is based on a table that stores products information.
The criterion is specified in the Criteria row of the field in the query design grid. Use this crit erion. The following examples are for the OrderDate field in a query based on a table that stores Orders information. Returns records of transactions that took place on Feb 2, Remember to surround date values with the character so that Access can distinguish between date values and text strings.
You can also use the Between operator to filter for a range of values, including the end points. Returns records where the transactions took place on Feb 1, , March 1, , or April 1, Contain a date that falls in a specific quarter irrespective of year , such as the first quarter.
Returns records of transactions that took place on the current day. Returns records of transactions that took place the day before the current day. Returns records of transactions that took place the day after the current day. Returns records of transactions that took place during the current week. A week starts on Sunday and ends on Saturday. Returns records of transactions that took place during the last week.
Returns records of transactions that will take place next week. Returns records of transactions that took place during the last 7 days. Returns records for the current month. Returns records for the previous month.
A month's worth of sales records. Returns records for the current quarter. Returns records for the previous quarter. Returns records for the next quarter. Returns records for the current year. Returns records of transactions that took place during the previous year. Returns records of transactions with next year's date. Returns records of transactions with dates that fall between Jan 1 of the current year and today. Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it.
Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it. Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. This practice is a warm-up for later discussion of SQL queries.
In particular, you are to do the following:. This assignment was originally written by Dr. A select query is one of several kinds of queries that might be made on a database others including insert, update, and delete.
It is a request to retrieve information from a database. In Microsoft Access, the query is typically created by giving a symbolic example of the information to be retrieved. It uses a special form called the QBE grid. In this section, an example is presented to show the general details of the approach. At the end of the section is a list of queries that you are to create and save as part of the database.
Recall from the prior activity that the record type for the grade database is the following:. We must refer to this record type in creating a query. A table is added to the upper portion of the QBE grid by either double clicking the name of the table or by highlighting the table name and clicking the Add button. We will need to add Sections , Grades , and Student to the grid.
Why those three? See if you can answer that question for yourself. In the QBE grid, field names to be used in the query are dragged from the tables in the top part to the field row in the grid. Uses the IIf function to display the values of the City and PostalCode controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode controls, separated by spaces.
Null propagation means that if any component of an expression is null, the entire expression is also null. You use the Page and the Pages properties to display or print page numbers in forms or reports. The Page and Pages properties are available only during printing or print preview, so they do not appear on the property sheet for the form or report. Typically, you use these properties by placing a text box in the header or footer section of the form or report, and then using an expression, such as the ones shown in the following table.
For more information about using headers and footers in forms and reports, see the article Insert page numbers into a form or report. You can use expressions to add, subtract, multiply, and divide the values in two or more fields or controls. You can also use expressions to perform arithmetic operations on dates.
The product of the value of the Price field or control and 1. Sometimes, you need a value that exists somewhere else, such as in a field or control on another form or report. You can use an expression to return the value from another field or control. The following table lists examples of expressions that you can use in calculated controls on forms. Column 2. The value of the third column in ProductID, a multiple-column list box on the subform named Orders Subform on the Orders form.
Note that 0 refers to the first column, 1 refers to the second column, and so on. The product of the value of the Price control on the subform named Orders Subform on the Orders form and 1. The expressions in the following table show some ways to use calculated controls on reports. The expressions reference the Report Property.
You can use a type of function called an aggregate function to calculate values for one or more fields or controls. For example, you can calculate a group total for the group footer in a report, or an order subtotal for line items on a form. You can also count the number of items in one or more fields or calculate an average value.
The expressions in the following table show some of the ways to use functions such as Avg, Count, and Sum. Uses the Avg function to display the average of the values of a table field or control named "Freight. Uses the Sum function to display the sum of the product of the values of the Quantity and the Price controls. Displays the percentage of sales, determined by dividing the value of the Sales control by the sum of all the values of the Sales control.
For more information about using aggregate functions and totaling the values in field and columns, see the articles Sum data by using a query , Count data by using a query , Display column totals in a datasheet using a Totals row , and Display column totals in a datasheet. You use a type of function called an SQL or domain aggregate function when you need to sum or count values selectively.
A "domain" consists of one or more fields in one or more tables, or one or more controls on one or more forms or reports. For example, you can match the values in a table field with the values in a control on a form.
Tracking dates and times is a fundamental database activity. For example, you can calculate how many days have elapsed since the invoice date to age your accounts receivable. You can format dates and times in numerous ways, as shown in the following table. Uses the Date function to display the current date in the form of mm-dd-yy , where mm is the month 1 through 12 , dd is the day 1 through 31 , and yy is the last two digits of the year through Uses the Format function to display the week number of the year for the current date, where ww represents weeks 1 through Uses the DateAdd function to display a date that is 10 days before the value of the PromisedDate control.
Uses the DateDiff function to display the number of days' difference between the values of the OrderDate and ShippedDate controls. Uses arithmetic operations on dates to calculate the date 30 days after the date in the InvoiceDate field or control. The example expressions in the following table use the IIf function to return one of two possible values.
You pass the IIf function three arguments: The first argument is an expression that must return a True or False value. The second argument is the value to return if the expression is true, and the third argument is the value to return if the expression is false. Uses the IIf and IsNull functions to display the values of the City and PostalCode controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode fields or controls.
Uses the IIf and IsNull functions to display the message "Check for a missing date" if the result of subtracting ShippedDate from RequiredDate is null; otherwise, it displays the interval between the date values of the RequiredDate and ShippedDate controls. This section contains examples of expressions that you can use to create a calculated field in a query or to supply criteria to a query.
A calculated field is a column in a query that results from an expression. For example, you can calculate a value, combine text values such as first and last names, or format a portion of a date.
You use criteria in a query to limit the records that you work with. For example, you can use the Between operator to supply a starting and ending date and limit the results of your query to orders that were shipped between those dates. Creates a field called Address2 that displays the values in the City, Region, and PostalCode fields, separated by spaces.
ProductInitial: Left [ProductName], 1. Creates a field called ProductInitial, and then uses the Left function to display, in the ProductInitial field, the first character of the value in the ProductName field. Creates a field called TypeCode, and then uses the Right function to display the last two characters of the values in the AssetCode field.
Creates a field called AreaCode, and then uses the Mid function to display the three characters starting with the second character of the value in the Phone field. Names the calculated field ExtendedPrice, and uses the CCur function to calculate the line item totals with an applied discount.
You can also perform arithmetic operations on dates. Creates a field called PrimeFreight, and then displays freight charges plus 10 percent in the field. Creates a field called OrderAmount, and then displays the product of the values in the Quantity and UnitPrice fields. Creates a field called LeadTime, and then displays the difference between the values in the RequiredDate and ShippedDate fields.
Creates a field called FreightPercentage, and then displays the percentage of freight charges in each subtotal. This expression uses the Sum function to total the values in the Freight field, and then divides those totals by the sum of the values in the Subtotal field.
To use this expression, you must convert your select query into a Totals query because you need to use the Total row in the design grid, and you must set the Total cell for this field to Expression.
For more information about creating a Totals query, see the article Sum data by using a query. Nearly all databases store and track dates and times. Access can perform arithmetic calculations on dates; for example, you can calculate how many days have elapsed since the invoice date to age your accounts receivable. Creates a field called LagTime, and then uses the DateDiff function to display the number of days between the order date and ship date.
Creates a field called YearHired, and then uses the DatePart function to display the year each employee was hired. Creates a field called MinusThirty, and then uses the Date function to display the date 30 days prior to the current date. You often see these functions for example, Sum, Count, and Avg referred to as aggregate functions.
In addition to aggregate functions, Access also provides "domain" aggregate functions that you use to sum or count values selectively. For example, you can count only the values within a certain range or look up a value from another table. To calculate totals, you will often need to create a totals query.
For example, to summarize by group, you need to use a Totals query. To enable a Totals query from the query design grid, click Totals on the View menu. Creates a field called RowCount, and then uses the Count function to count the number of records in the query, including records with null blank fields.
Creates a field called FreightPercentage, and then calculates the percentage of freight charges in each subtotal by dividing the sum of the values in the Freight field by the sum of the values in the Subtotal field. This example uses the Sum function. You must use this expression with a Totals query. Creates a field called AverageFreight, and then uses the DAvg function to calculate the average freight on all orders combined in a Totals query.
The expressions shown here work with fields with potentially missing information, such as those containing null unknown or undefined values. You frequently encounter null values, such as an unknown price for a new product or a value that a coworker forgot to add to an order. The ability to find and process null values can be a critical part of database operations, and the expressions in the following table demonstrate some common ways to deal with null values.
Creates a field called CurrentCountryRegion, and then uses the IIf and IsNull functions to display an empty string in that field when the CountryRegion field contains a null value; otherwise, it displays the contents of the CountryRegion field.
Creates a field called LeadTime, and then uses the IIf and IsNull functions to display the message "Check for a missing date" if the value in either the RequiredDate field or the ShippedDate field is null; otherwise, it displays the date difference. Creates a field called SixMonthSales, and then displays the total of the values in the Qtr1Sales and Qtr2Sales fields by first using the Nz function to convert any null values to zero.
You can use a nested query, also called a subquery, to create a calculated field. The expression in the following table is one example of a calculated field that results from a subquery. The sample expressions in this table demonstrate criteria that match whole or partial text values.
0コメント