An append query adds selected records to an existing table
|
A Select query returns a list of records that belong to one or more specified families and match your query criteria. Show The results of a Select query can provide you with a comprehensive or custom view of the data that exists in the families in your database, allowing you to see only the records and fields that you need to see. Creating a Select query is also often the first step in a process that involves using the query results to perform a certain task. For example, you might create a Select query and then use those results to build a report or create a graph. About Crosstab QueriesA Crosstab query lets you group data into categories, where a category is determined by a value that exists in multiple fields across multiple families in the database. In the results of a Select query, each field appears in a column, providing a simple list of data. The results of a Crosstab query appear in a grid. In other words, a Crosstab query presents the same information as a Select query, but in a different format. The format that you choose will depend on the type of information that is returned by the query, and how you want to view it. Example: Select vs. Crosstab Query ResultsSuppose you have a family called Pumps, which stores data on Pump Location, Pump Manufacturer, and Pump Failures. If you queried the family and included the location, manufacturer, and failures fields:
In this example, you can see that ACME is the manufacturer of multiple pumps. The manufacturer, therefore, represents the category by which you want to display the remaining data (pump location). Each column in the results grid represents a separate value within the same category. So, in this example, ACME and SUPER are different types of manufacturers within the manufacturer category. Example: Crosstab QueryWhen management personnel request that work be performed on a piece of equipment, the work results in some amount of downtime for the piece of equipment. Some work activities result in longer amounts of downtime than others. In your company, work requests are recorded in Work Request records, which contain the following fields:
If you were to create a Select query to view information about work requests that have been completed, the results might look something like those shown in the following image.  In these results, you can see each work request ID, the corresponding work activity, and the total amount of downtime per request. The format of these results, however, does not display the work requests grouped by activity type. While this result set is small, which allows you to visually determine how many work requests fall into each activity type, more typical query results will contain enough rows of data that it will be difficult to divide it into categories by visually comparing the data. This is especially true when the results span multiple pages. To group the results such that you can see at a glance how many work requests fall into each activity type, you could decide to make the query an aggregate query. If you use the COUNT function on the Work Request ID field and the SUM function on the Downtime field, the query results would look similar to those shown in the following image. In these results, you can see that three work requests asked for an adjustment, three requests asked for something to be cleaned, and four work requests asked for a repair. You can also see that the total amount of downtime for all adjustments was four days, the total amount of downtime for all cleaning tasks was six days, and the total amount of downtime for all repairs was 20 hours. While the stored data is interesting when viewed in this format, you might be more interested in determining which work requests resulted in a downtime over a certain number of days. For instance, suppose that you expect repairs to take over seven days, but cleaning tasks that take more than seven days are unacceptable to management personnel. You might want to construct the query such that it groups the raw data into two categories: downtime and work activity. You then want to determine how many work requests in each type of activity resulted in downtime between one and seven days, and how many resulted in downtime over seven days. The results of a Select query cannot present the data in this format. To format the data such that it provides the desired information, you must create a Crosstab query, where you can:
The Crosstab query will contain the same fields as the Select query: Work Request ID, Work Activity, and Downtime. To convert the stored downtime values into categories, however, you will need to add another column that includes a DECODE statement that uses the SIGN function. The DECODE statement would look like this: This statement indicates that:
In the grid in the Conditions section, the Work Activity field will be the row heading, and the column with the DECODE statement will be the column heading. The Work Request ID field will be the intersecting field, or the Value, and a COUNT function will be defined in the Total cell for the Work Request ID field. This means that the intersecting cell in the results will contain a number instead of a Work Request ID. The number will indicate the number of work requests that fall into the category defined by the intersection of the row and the column (e.g., the number of repair work requests that resulted in a downtime of over seven days). In addition, the SUM function will be defined in the Total cell for the Downtime field. This will ensure that the results contain only one row representing each work activity instead of multiple rows containing the same work activity. For example, if there are three repair requests, because the SUM function is defined on the Downtime field, the results will contain only one row representing the repair work type (displaying the total number of work requests of that type) instead of three rows representing the repair work type (displaying only one work request of that type for each row). The results will be grouped as shown in the following table. Work ActivityDowntime Category (1 to 7 Days)Downtime Category (Over 7 Days)Work Activity (Repair)# of Repairs with a Downtime of 1 to 7 Days# of Repairs with a Downtime Over 7 DaysWork Activity (Clean)# of Cleaning Tasks with a Downtime of 1 to 7 Days# of Cleaning Tasks with a Downtime Over 7 DaysWork Activity (Adjust)# of Adjustments with a Downtime of 1 to 7 Days# of Adjustments with a Downtime Over 7 Days About Update QueriesNote: When you create an update query, entered times are assumed to be in UTC. An Update query modifies the records that match the criteria that you have specified in the query. An Update query makes global changes to a group of records belonging to one or more families. An Update query is similar to a Select query in that it retrieves records from the database that match the criteria defined within the query. The difference is that instead of displaying the results, the results are modified according to the criteria defined in the query. For example, if an equipment manufacturer name has changed, you would need to update all the existing records for equipment made by that manufacturer so that the value in the Manufacturer field is the new name of the manufacturer. Instead of manually modifying these values, you could create an Update query to modify all the records for this family. Only Super Users and members of the MI Power User Role Security Group can create and run the Append queries, provided that they have been granted View permissions to the Catalog folder in which they are stored. To create the records returned by the query, a user must have Insert permissions to the family in which the records are being created. |
