Introduction to Special Queries
A parameterized query is a query that is missing a value in a criteria. The word "missing" here seems negative to indicate something bad in the query but it is not so. The query is purposely created like that so that a value would be provided to its filter. At the time the query is created, the filter is setup in a certain way.
A parameterized query is a query that is missing a value in a criteria. The word "missing" here seems negative to indicate something bad in the query but it is not so. The query is purposely created like that so that a value would be provided to its filter. At the time the query is created, the filter is setup in a certain way. When the query is run, the missing value must be provided to complete the criterion, then the query is complete, and the result of the query is produced. Most of the time, a parameterized query is created so the user must specify the specific record(s) to produce.
Although we specified that the query is parameterized, it is in fact the SQL statement that is parameterized. This means that you can create a SQL expression used as the Record Source of a form or report, but oblige the user to provide the missing information so the form or report would display only the record(s) that use(s) that value.
Practical Learning: Introducing Parameterized Queries
SELECT Properties.PropertyNumber, Properties.PropertyType AS [Property Type], Properties.City, Properties.Bedrooms AS Beds, Properties.Bathrooms AS Baths, Properties.FinishedBasement AS [Basement?], Properties.IndoorGarage AS [Garage?], Properties.Condition, Properties.MarketValue AS [Market Value] FROM Properties;
Creating a Parameterized Query
When creating a parameterized query, you must provide a parameter to it. To proceed, open the query in Design View and select the necessary columns. In the Criteria box of the field that will hold the criteria, type the square brackets. Inside the brackets, enter anything you want, such as a message or a question. The message or question will be presented to the user who must type a value in a text box. Once the user provides the value and clicks OK or presses Enter, the query would run.
Practical Learning: Creating a Parameterized SQL Expression
You can create a parameterized query that asked two or more questions that must be combined to complete the query. To do this, in the Design View of the query, enter a message for the Criteria box of each column.
Practical Learning: Creating a Parameterized Disjunction
A parameterized disjunction requests two or more values. The query produces all record that include any of the values. To create such a parameterized query, add the first question or message to the Criteria box of the desired field. Then add the second message or question to the Or box of the same column. If you need additional values, enter their questions or messages in the boxes below the Or cell of the same column.
Practical Learning: Creating a Parameterized Disjunction
Microsoft Access provides some ready-made queries that allow you to execute semi-complex actions on existing tables. The actions are performed as queries. When you use an action query, you are not creating a regular query in the strict sense. Most of these queries are usually useful only once. For this reason, you will hardly need to save them.
The Make Table Action Query
Instead of first creating a table then filling it with values, if you already have values stored in a table, Microsoft Access allows you to create a new table filled with values from that table.
Using queries, there are two main techniques you can use to create a new table for your database. Microsoft Access provides a technique called Make Table Query. This type of query is used to retrieve all or some fields of an existing table and, instead of creating a new query that depends on an existing table, you would get a brand new table, independent of any existing table. This type of action can let you create a new table based on a rule applied to an existing table.
To create a table using a Microsoft Access query, start a new query in Design View. Then, in the Query Type section of the Ribbon, click the Make Table button . This would present you with a Make Table dialog box that allows you to specify a new name for the query or to select an existing table that would receive the new data.
Practical Learning: Using Make-Table Queries
The Update Query
If you have a large list and many records need to receive a common change, you can create a special query named Update Query. An Update Query allows you to change the existing values of one or more columns of a table. In other words, you can retrieve the values that are already in the fields of a table, change these values and have the table saved with them.
To create an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Update button . When creating the query, you will have to set a condition that Microsoft Access will apply to a table to find out what record(s) need(s) to be updated. To do this, you use the Criteria box of a column, just like we learned during data analysis.
Practical Learning: Updating Records
Read it and click Yes
An Append Query
If you have a table with a significant number of records and you need to add those records to another table, the work can become overwhelming. Microsoft Access provides a feature to create a special query that can be used to add many records to a table in one step.
An Append Query allows you to add records to an existing table but you don't create the records. They must be retrieved from one table and added to another table.
To create an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Append button . You will be presented with a dialog box that expects you to either select the table to which you will add the records, or to specify the name of a new table that would receive the records. If you want to add the records to an existing table, you can select it from the combo box.
As you may imagine, appending a record consists of adding a record to a table. If you want to do this in SQL, simply create an INSERT statement.
Then click Yes
The Delete Query
If you have a few records that need to be removed from a table, Microsoft Access provides various techniques to delete such records. To delete a group of records in one action, you can create a special query called a Delete Query.
Like all other Action queries, a Delete Query allows you to select the necessary fields of a table. The first difference between this and the Select Query is that the Delete Query, like all other action queries, selects or considers all fields, even those you don't include in your statement. The other columns are those that would be used to specify the rules under which a record must be deleted.
You can use a criterion or many criteria to set the rule to follow in order to get rid of (a) record(s). Like most other action queries, the action of a Delete Query is irreversible.
Practical Learning: Deleting Database Records
Special Operations on Queries
Merging the Records of Many Tables
If you have various tables, you can create a query that combines their records so you can examine those records when they are put together. To assist you with this, the SQL provides an operator named UNION. The primary formula to use it is:
SELECT columns-names FROM table-name1 UNION [ALL] SELECT columns-names FROM table-name2
Thyis is equivalent to creating two SELECT statements. Each contains some columns and a table. Both statements must contain the same number of columns. The data types of the columns on each position must be compatible.
Practical Learning: Merging the Records of Many Tables
SELECT Employees.EmployeeNumber, Employees.FirstName, Employees.LastName FROM Employees UNION SELECT Clerks.ClerkCode, Clerks.FirstName, Clerks.LastName FROM Clerks;
If you have some records in an existing table, you can copy all or some of those records to put them in a new table you create. To do this, the formula to follow is:
SELECT fields INTO new-table-name FROM existing-table [WHERE condition]
To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:
SELECT * INTO CompanyRecipients FROM Employees;
Instead of using all columns, you can specify only the desired field after the SELECT keyword. Here is an example:
SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus INTO Salaried FROM Employees;
Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example:
SELECT * INTO FullTimeEmployees FROM Employees WHERE EmploymentStatus = 'Full Time';
Removing All Records from a Table
In the SQL, to remove all records from a table, use the DELETE command. The formula to follow is:
DELETE columns-names FROM table-name;
Normally, you use * as the columns-names. The table-name parameter must be a valid name of a table of the current database. Here is an example:
DELETE * FROM States;
When you execute, you will receive a wanring:
If you click Yes, all records from the table will be removed.
Deleting Some Recordx
To delete one or more records, add a WHERE condition to a DELETE command. The formula to follow is:
DELETE columns-names FROM table-name WHERE condition;
The WHERE clause specifies the condition by which some records will be deleted. Here is an example:
DELETE * FROM [Staff Members] WHERE [Marital Status] = "Single";
Once again, when you execute, you will receive a wanring that allows you to decide whether you want to continue the operation.
Practical Learning: Ending the Lesson
|Previous||Copyright © 2002-2021, FunctionX||Next|