|
You have selected free tutorial of the Microsoft Corporation for the Microsoft Office Specialist (MOS) :
77-424: Access 2013 Topics : Create queries (15–20%):
Create a query •Run queries, create crosstab queries, create parameter queries, create action queries, create multi-table queries, save queries, delete queries
Microsoft Help:-
Run queries
Types of queries that Access provides give you many ways to select and view specific data in your database.
A query provides a powerful mechanism for filtering and sorting data. These types of queries are available in Access:
- Advanced Filter/Sort: The simplest kind of query, Advanced Filter/Sort allows you to find and sort information from a single table in the database. You can access this option in any datasheet by clicking Advanced in the Sort & Filter group on the Home tab of the Ribbon and then choosing Advanced Filter/Sort.
- Select: A select query selects the data you want from one or more tables and displays the data in the order in which you want it displayed. A select query can include criteria that tell Access to filter records and display only some of them. Select queries that display individual records are detail queries; those that summarize records are totals or summary queries
- Totals or Summary: These queries are subsets of select queries, but they allow you to calculate a sum or some other aggregate (such as an average) rather than display each individual record.
- Parameter: A parameter query asks you for one or more pieces of information that Access then uses to filter the data before displaying the datasheet.
- AutoLookup: An AutoLookup query fills in information for you.
- Action: Action queries change your data based on some set of criteria. Action queries can delete records, update data, append data from one or more tables to another table, and make a new table.
- Crosstab: Most tables in Access, including ones generated by queries, have field names across the top, with each row containing a record of data. Crosstab queries produce tables with the values from one field down the side and values from another field across the top of the table. A crosstab query performs a calculation; it sums, averages, or counts data that is categorized in two ways, as defined by the row and column labels. You could use a crosstab query to show states across the top and products down the side, for example, so that you can see how sales differ by state.
It can also be used to bring together data from several tables or other queries in a single presentation. Like a table, a query can be opened to display data in the design interface without showing the results in the browser. To display the results in the browser, you need to create a view that uses the query. In Section 7, "Modifying data using queries," we describe how you can create action queries that modify data in a desktop database
- Click Advanced on the Home tab of the ribbon.
- Select Query.
- From the Show Table Dialog box, select one or more queries or tables from the Show Table dialog box by double-clicking each table. 4 Click Close when you have finished making selections.
- Add the fields from the source data into the query grid by doubleclicking each field listed in the table.
- Click Save.
- Type a name for the query, and click OK to save it.
- Click View.
- Select Datasheet View to test the query.
create crosstab queries
A crosstab query is a special kind of aggregate query that summarizes values from a specified field and groups them in a matrix layout by two sets of dimensions, one set down the left side of the matrix and the other set listed across the top of the matrix. Crosstab queries are perfect for analyzing trends over time, or providing a method for quickly identifying anomalies in your dataset. The anatomy of a crosstab query is simple. You need a minimum of three fields in order to create the matrix structure that will become your crosstab. The first field makes up the row headings, the second field makes up the column headings, and the third field makes up the aggregated data in the center of the matrix. The data in the center can represent a Sum, Count, Average, or any other aggregate function. Crosstab queries can be constructed manually. However, it is simpler to use the Query Wizard to create the query, which you can later change in design view.
- Click the Create tab.
- Click Query Wizard.
- In the New Query popup window,
- click Crosstab Query Wizard.
- Click OK.
- In the Crosstab Query Wizard, click Queries.
- Select a query and click Next.
- Select one or more columns to act as row headings, and click Next.
- Select one column to act as the new column heading, and click Next.
- Select the column to summarize.
- Select the calculation type to use, and click Next.
- Type a name for your query, and click Finish.
- Select design view.
- In the query Column Headings property field, type a fixed set of headings.
Cross Query Ve Total Query:
A crosstab query is a powerful summary tool that examines huge amounts of data and uses it to calculate information like subtotals and averages. If this sounds familiar, it’s because you’ve already seen totals queries pull off a similar feat.
CRossTab QueRies As with totals queries, crosstab queries use two key ingredients: grouping and summary functions. The grouping is used to organize the rows into small sets. The summary function is used to calculate a single piece of information for each group.
Behind the scenes, crosstab queries and totals queries work in almost exactly the same way.
Both take large numbers of records and boil them down to totals, averages, minimums, maximums, and so on.
However, there are two important differences.
- crosstab queries always use two levels of grouping. For example, a typical totals query may group order records by product, so you can see the top sellers and how much cash they bring in. But a crosstab query can analyze sales figures by country and product category. Using this type of analysis, you can quickly see what product categories do well in particular countries.
- The other difference between totals queries and crosstab queries is the way Access organizes the results. A totals query creates a separate row for each different group. For example, if you’re analyzing sales by country and product category, a totals query gives you a row for each country and category combination, top.
- A crosstab query works a little differently; it takes the same information and packs it into separate columns, creating a denser view
Parameter Query
A parameter query is a query that intentionally omits a piece of needed information so that you can enter the information on the fly when you open the query in Datasheet view. Parameter query is an interactive query that prompts you for criteria before the query is run. A parameter query is useful when you need to ask a query different questions using different criteria each time it’s run.
When you run a parameter query, Access attempts to convert any text to a literal string by wrapping the text in quotes. However, if you place square brackets ([]) around the text, Access thinks that it’s a variable and tries to bind some value to the variable using the following series of tests:
- Access checks to see if the variable is a field name. If Access identifies the variable as a field name, that field is used in the expression.
- If the variable is not a field name, Access checks to see if the variable is a calculated field. If Access determines the expression is indeed a calculated field, it simply carries out the mathematical operation.
- If the variable is not a calculated field, Access checks to see if the variable is referencing an object such as a control on an open form or open report.
- If all else fails, the only remaining option is to ask the user what the variable is, so Access displays the Enter Parameter Value dialog box, showing the text you entered in the Criteria row.
parameter queries come with their own set of ground rules that you should follow in order to use them properly.
- You must place square brackets ([]) around your parameter. If you don’t, Access will automatically convert your text into a literal string.
- You can’t use the name of a field as a parameter. If you do, Access will simply replace your parameter with the current value of the field.
- You can’t use a period (.), an exclamation point (!), square brackets ([]), or an ampersand (&) in your parameter’s prompt text.
- You must limit the number of characters in your parameter’s prompt text. Entering parameter prompt text that is too long may result in your prompt being cut off in the Enter Parameter Value dialog box. Moreover, you should make your prompts as clear and concise as possible.
You can add tables and field names just as you would in any other query. Then follow these steps to make your query a parameter query:
- In the Design View window, click Parameters in the Show/Hide group of the (Query Tools) Design tab on the Ribbon. The Query Parameters dialog box appears
- Enter a parameter name and its data type in the appropriate columns. The parameter name can be any name you like, as long as it doesn’t match the name of a regular or calculated field that’s already included in the table. The data type matches the type of data that the parameter will ask for, such as Text for text, Currency for a dollar value, or Date/Time for a date or time. You can repeat this step to create as many parameters as you want.
- Click OK to close the Query Parameters dialog box.
create action queries
Action queries differ significantly from select queries. A select query shows you data that meet your criteria, whereas an action query looks for the data that meets your criteria and then does something with it, such as making changes to the data or moving records to a new tableAction queries perform a specific task on a group of records in a table. The four most common action queries are
- Update: This query updates the value in a field for the records you select via the Criteria row.
- Append: Use this query type to add records from one table to another.
- Delete: If you’re in a destructive mood, use this query to delete groups of records from a table.
- make-table query: Create a new table from data stored in other tables .
action queries can update, insert, and delete data. When you need processing that executes a sequence of action queries, macros enable you to run those action queries to make bulk changes to the data in your system. The OpenQuery macro will either open and display the results of a selected query or execute an action query with prompting for changing the data, When you work with an action query, the View and Run buttons do completely different jobs:
- View: The View button displays Datasheet view with all the records that match your selection criteria, which is a good way to preview what records will change when you run the action query. The View button is a safe way to look at the datasheet of an action query to see whether the query will work the way you want.
- Run: The Run button executes the action — deletes or changes data in your database. You can’t undo the action after you click the Run button in an action query, so be very sure that you set up the query correctly before you run it — and be sure to have backups of the affected tables just in case disaster strikes.
- Empty and populate a table of data You need to perfect an action query before you run it so that you don’t wreck your data. (If you make a mistake, you have a backup, right?) Make the action query, look at it, maybe test it on a few records in a test table, and then finally run it.
- Create an action query to delete all the data in a table that you regularly need to populate from another table of imported data.
- Create an action query that will append a set of records from another table to the same table from which you deleted all the records. (We are going to assume here that you regularly import data from another system into a table and that you then need to further process that data into other tables in your database.)
- Click the Create tab.
- Click Macro.
- Add an OpenQuery command.
- Select the query that empties the data from your table.
- Add an OpenQuery command.
- Select the query that adds data to the empty table.
- Save the macro.
- Type a name for the macro, and click OK. At this point, you should save and test the macro.
- Add the Set Warnings command, with Warnings On set to No. This prevents the prompting boxes from displaying when you are executing the query. (When you have verified that it executes correctly, you might want to switch off the built-in warnings when executing an action query.)
- Add the Set Warnings command, with Warnings On set to Yes. It is very important that you always switch the standard warnings back on.
- Add a MessageBox command to indicate that the processing is completed.
- Use the up and down arrows to move your commands to the correct position in the macro sequence.
- Executing a saved import/export Macro commands are provided for running the saved import/ export processes , suppose, on a form, these commands could allow you to create a button that executes a sequence of import/export processes to manage your data. In this example we will show how to create a macro that is not embedded inside a form. You can then execute this macro from the navigation pane, or alternatively, you could use the RunMacro command inside an embedded macro on a button on a form. (Macros can run other macros.)
- Click the Create tab.
- Click Macro.
- Click Show All Actions. 4 Select the RunSavedImportExport action. (You will need to have already saved an import/export to use this.) Select the file to import or export.
- Click Save.
- Type a name for the macro, and click OK. In the navigation pane, a new object type will be shown (unless you have previously created macros). Double-click the macro to execute the operation.
- To edit your macro, display the macro in the navigation pane, rightclick, and select Design View.
- Update Query: update query when you work on lots and lots of data or when you want to update multiple fields makes sense. But before you delve in to the complexities of an action query, consider whether you can use the much simpler Find and Replace dialog box to find and replace data instead. The Update query can replace the value in a field in a group of records with another value. To create an Update query, first you need to determine the table (and the field within it) in need of updating — and decide how you want to update that field. When you’ve figured that out, do this:
- Select the Create tab on the Ribbon. The Create buttons appear onscreen. Notice the Queries group toward the left side of the Ribbon.
- Click the Query Design button from the Queries group on the Ribbon. A new query opens in Design view, and the Show Table dialog box pops up.
- Select the table that contains the field you’d like to update; then click the Add button. The selected table is added to the top half of the Query Design window
- Click the Close button in the Show Table window. The Show Table window closes.
- From the Table field list, double-click the field name that the query will update along with any fields you’ll need to write criteria. The fields are added to the bottom half of the Query Design window.
- Click the Design tab on the Ribbon. The Ribbon displays the design commands, including the Query Type group.
- Select the Update button from the Query Type group. The Update To row appears on the query grid.
- In the Update To row of the field you want updated, enter a value or expression to update the field.
- In the Criteria row, enter the criteria that will select the records you’d like updated.
- Click the Run button from the Ribbon’s Results group to run the query and update the records.
- Click Yes in the message box window to run the query and update your data.
- Update queries will update the data you specify — but the update can’t be undone. Back up your data before you run Update queries
- Append queries Append queries are useful for archiving information, moving data between databases, and performing other useful housecleaning chores. An append query copies data from one or more tables or queries in your database and adds the data selected by the query to an existing table as new records. As with other queries, you can use criteria to tell Access exactly which data to append. Append queries add records from one table (called the source table) to another table (called the destination). A common use for an Append query is to add data from an external file (such as an imported or linked spreadsheet) to an existing Access table. To create an Append query, follow these steps:
- 1. Select the Create tab on the Ribbon. The Create buttons appear on-screen. Notice the Queries group toward the left side of the Ribbon.
- Click the Query Design button from the Queries group. A new query opens in Design view, and the Show Table dialog box pops up.
- Select the table that contains the source data to be appended; then click the Add button. The selected table is added to the top half of the Query Design window.
- Click the Close button in the Show Table window. The Show Table window closes.
- From the Table field list, double-click the field names that contain the data to be added to the destination table. The fields are added to the bottom half of the Query Design window .
- Click the Design tab on the Ribbon. The Ribbon displays the Design buttons, including the buttons in the Query Type group.
- Select the Append button from the Query Type group. The Append dialog box appears.
- Select the destination table from the Table Name drop-down list; then click OK to
close the Append dialog box. The Append To row appears in the query just above the Criteria row. If a source field name matches exactly a destination field name, the Append To row will pre-populate with each matching field name from the destination table. If the source and destination field names do not match for a specific field, the Append To row will not pre-populate with a field name from the destination table. To fix this, click in the Append To row with a missing destination field name. A drop-down arrow appears to the right. Select the matching destination field from the drop-down list.
- Write criteria if necessary to select just those records you want from the source table. the completed Append query. In most cases, the source and destination fields must be of the same data type. For example, you cannot append data from a text field to a number field.
- Click the Run button from the Ribbon’s Results group to run the query and append the records from
the source table to the destination table. A message box appears, telling you how many records will be added. When Append queries add records to the destination table, you cannot undo the results. Be sure to back up your database file before you run an Append query.
- Click Yes in the message-box window to run the query and add the records.
- The Delete query Always make sure that you have a backup before you run a delete query. You may want to back up the whole database or just the tables affected by the delete query because delete query can clean up unwanted records in a hurry as A delete query deletes whole records from tables, usually based on criteria you provide (although you can also use delete queries to delete all records in a table while keeping the field and table properties intact). Delete queries are dangerous because they permanently delete data from the tables in your database. Obviously, delete queries are powerful and should be treated with respect! Of the four action queries that have been discussed in this chapter, the Delete query is the most dangerous: It can wipe out all the data in your table in an instant. Pay special attention to the selection criteria you write for a Delete query so you’re sure you’ll delete only the correct records. To build a Delete query, follow these steps:
- Select the Create tab on the Ribbon. The Create buttons appear onscreen. Notice the Queries group toward the left side of the Ribbon.
- Click the Query Design button from the Queries group . A new query opens in Design view and the Show Table dialog box pops up.
- Select the table that contains the source data to be deleted; then click the Add button. The selected table is added to the top half of the Query Design window.
- Click the Close button in the Show Table window. The Show Table window closes.
- From the Table field list, double-click the asterisk (*) at the top of the list and any individual field names you intend to use for criteria. The fields will be added to the bottom half of the Query Design window.
- Click the Design tab on the Ribbon. The Ribbon displays the Design buttons, including the Query Type group.
- Select the Delete button from the Query Type group. The Delete row appears in the query grid,
- Write criteria if necessary to select just those records you want to delete from the table.
- the completed Delete query.
- 9. Click the Run button from the Ribbon’s Results group to run the query and delete the records from the selected table. A message box will appear, telling you how many records will be deleted. A Delete query deletes records permanently from the selected table. You cannot undo the results of this query type. Back up your database file before running a Delete query!
- Click Yes in the message box window to run the query and delete the records.
- Create new Table : A make-table query is useful if you need to make a new table to export or to serve as a backup. You can use a make-table query to create a new table that contains a copy of the data in a table or query. To create a table with a make-table query, follow these steps:
- Create a select query that produces the records you want in a new table.
- Click the View button on the toolbar to view the results.
- Click the View button on the toolbar to display Design view.
- Change the query type to a make-table query by clicking the Make Table button in the Query Type group on the Design tab of the Ribbon. Access immediately displays the Make Table dialog box.
- In the Table Name field, type the name of the table you’re creating. Although you’re offered a drop-down menu, you’ll probably want to create a new table with a new name, so type a name for the table that isn’t the name of any table currently in your database.
- Choose whether to create the new table in the current database or in another database. If you choose the Another Database option, you can browse for an existing database. You can’t use a make-table query to create a new database — only a new table in an existing database.
- Click OK to close the dialog box. If you need to change the settings in the Make Table dialog box, click the Make Table button again to display the dialog box again.
- Click the View button to see the records that will be in the new table. You may need to return to Design view to edit the query until all the records you want in the new table appear in the datasheet when you click the View button.
- Click the Run button to create the new table. Access asks whether you’re sure, because you won’t be able to undo your changes.
- Click Yes to create the new table. Access quietly creates the new table.
- Check the new and old tables to make sure that you got what you need in the new table. You may want to edit the table design because the new table doesn’t inherit the field properties or the primary key setting from the original table.
- search Unmatched Records with a Wizard: The Find Unmatched Query Wizard finds records in one table that have no matching records in another, related table. Find Unmatched Query Wizard to find unmatched records in the following way:
- Click the Query Wizard button in the Queries group on the Create tab of the Ribbon. The New Query dialog box opens.
- Select the Find Unmatched Query Wizard option and then click OK. The first window of the wizard appears.
- Select the table (or query) that may have unmatched records in a second table and then click Next.
- Select the table (or query) that should contain the matching records for the data in the table you selected in Step 3 and then click Next.
- Check to make sure that Access correctly guessed the related fields in the two tables you selected in the third window of the wizard if it did, click Next. The window shows field names in the two tables you selected. The names of the related fields probably are highlighted. Click the related field in each table if Access hasn’t selected the correct related fields. The two fields that you select should contain the same information and be of the same data type.
- Select the fields you want to see in the query results in the next window of the wizard and then click Next. To select all fields, click the double arrow pointing to the right.
- Accept the name that Access gives the query, or name the query yourself in the final window of the Find Unmatched Query Wizard. Access is good at naming the results of this query descriptively. Notice whether the name reflects the query you thought you were creating. If not, click the Back button to redefine the query.
- Choose whether you want to view the results or modify the design and then click Finish. Access displays the query in Design or Datasheet view, as you requested.
- Find Duplicates Query Wizard option : Follow these steps to run the wizard:
- Click the Query Wizard button in the Queries group on the Create tab of the Ribbon. The New Query dialog box opens.
- Select the Find Duplicates Query Wizard option and then click OK. The Find Duplicates Query Wizard starts.
- Click the name of the table that you want to search in the first window of the wizard and then click Next. Optionally, you can click the Queries option and choose a query to use as the basis for the search.
- Click the > button to copy fields from the Available Fields list to the Duplicate-Value Fields list in the second window of the wizard and then click Next. Be sure to include all fields that contain the data needed to define duplicate records.
- Choose the fields to be shown for additional information in the third window of the wizard and then click Next. The fields you specify aren’t used for comparing records, but they appear in the query results to help you better identify any duplicate records. If your table has a primary key and/or Date Entered field, both are good candidates for this third field.
- Give the query a name. Change the suggested name for the query, if you want, or select the suggested name in the last window of the wizard.
- Choose the View the Results option. 8. Click the Finish button.
|
Your Salary Above $ 66000... Click ...
Ohh! You want More.... be game developer of your choice $ 102000 ....
|