1 / 72

Using Action Queries

SESSION 8.2. Action Queries - In this session we will explore the 4 types of action queries. Using Action Queries. Microsoft Access 2000 Class #6. The Major Steps of a MicroSoft Access Database. Tables Queries Forms Macros Reports Modules. On our road map, we are here!.

triage
Download Presentation

Using Action Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SESSION 8.2 Action Queries - In this session we will explore the 4 types of action queries. Using Action Queries Microsoft Access 2000 Class #6

  2. TheMajorSteps of a MicroSoft Access Database • Tables • Queries • Forms • Macros • Reports • Modules On our road map, we are here!

  3. Queries can do more than display answers to the questions you ask; they can also perform actions on the data in your database. Action Queries

  4. Once you understand the basics of Select Queries, Action Queries are very simple, yet extremely Powerful

  5. Action queries, combined with macros, can literally make your day! With Action queries and macros you can automate many of the tedious actions that once took hours or even days to complete. Fast and Accurate!

  6. An Action Query is a query that adds, changes, or deletes multiple table records at one time. Action Queries

  7. Before you can create an Action Query however, you must first create a Select Query, testing the query by “Viewing” the datasheet. Action Queries

  8. Once you are satisfied that the query is going to act on only the records that you have selected, then you can convert the query to an Action Query. Action Queries

  9. This is done from the Select Query’s design window. All that you need to do is select the appropriate query type from the query type button on the tool bar.

  10. By clicking on the selection arrow to the right of the button, you will see a list of query types that can be used

  11. Action Queries come in four flavors. Make Table Query Append Query Delete Query Update (modify) Query

  12. A “Make Table” query creates a new table from one or more existing tables.

  13. The new table can be an exact copy of the records in an existing table.

  14. The new table can be a subset of the fields and records of an existing table.

  15. The new table can be a combination of the fields and records from two or more tables.

  16. To create a Make Table query, first, right click on an existing query in the Database window.

  17. Then select Design View from the resulting Short Cut Menu.

  18. Access will then open the query in the design view, and your are ready to begin converting it to a Make Table Query.

  19. Step two is to click on the Queries type button on the tool bar and then select Make-Table Query.

  20. Alternatively you can click on the Query Option from the Menu Bar and then select Make-Table Query. The choice is yours, either way is the same.

  21. Special Customers You will then be prompted for the name of the new table that you are making. Enter the name an then click on the OK button.

  22. This will bring you back to the design view. The only change that you will see is the type of query on the tittle bar. It will now say Make Table Query instead of Select Query.

  23. At this point, all that we have done is to create the Action Query. We still must Run the query in order to make the Action happen.

  24. Before we Run the query however, what is it that we must do in order to test it?

  25. We really should View the query in its datasheet view first, just to make sure that the criteria is set up correctly.

  26. The final step, once we know that it will act upon only the records that we want it to, is to Run the Query.

  27. Once we click “Yes” the Action Query will do its thing and return us to the Design view of the Query. To see if it really worked, you can press the F11 Function key which will show us the database window.

  28. Click on the Tables Tab to View the Tables The new table will be shown in the list of Table Objects.

  29. To return to the design view of your query, simply left click in the gray area of the query that is in the back ground.

  30. An “Append Query” adds records from an existing table or query to the end of another table.

  31. For an append query, you choose the fields you want to append from one or more tables or queries; the selected data remains in the original tables.

  32. The data that you selected, will be added to the end of another already existing table.

  33. To create an Append query, first, right click on an existing query in the Database window.

  34. Then select Design View from the resulting Short Cut Menu.

  35. Access will then open the query in the design view, and your are ready to begin converting it to an Append Query.

  36. Step two is to click on the Queries type button on the tool bar and then select Append Query.

  37. Alternatively you can click on the Query Option from the Menu Bar and then select Append Query. The choice is yours, either way is the same.

  38. Special Customers You will then be prompted for the name of the table that you are appending the data to. Enter the name an then click on the OK button.

  39. This will bring you back to the design view. The only change that you will see is the type of query on the tittle bar. It will now say Append Query instead of Select Query.

  40. At this point, all that we have done is to create the Action Query. We still must Run the query in order to make the Action happen.

  41. Before we Run the query however, what is it that we must do in order to test it?

  42. We really should View the query in its datasheet view first, just to make sure that the criteria is set up correctly.

  43. The final step, once we know that it will act upon only the records that we want it to, is to Run the Query.

  44. Once we click “Yes” the Action Query will do its thing and return us to the Design view of the Query. To see if it really worked, you can press the F11 Function key which will show us the database window.

  45. Click on the Tables Tab to View the Tables Open the table that you appended the data to. You should see the additional records at the end.

  46. To return to the design view of your query, simply left click in the gray area of the query that is in the back ground.

  47. Delete and Update Queries are Created, Tested and Run in much the same way. Let’s consider this important note of interest however. The Make-Table and Append Queries, will copy data from already existing tables and place the data in either a new table or another already existing table.

  48. Delete and UpDate Queries will modify the data in the table that they are based on, rather than acting on other tables.

More Related