1 / 13

Queries for data modification: Action queries

Queries for data modification: Action queries. MS Access 2007. Action Queries. Types Make table queries Append queries Update queries Delete queries Demos: Building action queries. Action queries.

khalil
Download Presentation

Queries for data modification: 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. Queries for data modification:Action queries MS Access 2007 IT User Services - University of Delaware

  2. Action Queries • Types • Make table queries • Append queries • Update queries • Delete queries • Demos: Building action queries IT User Services - University of Delaware

  3. Action queries • Action query: A query that permanently adds, changes or removes data or changes the structure of the database (data definition language, DDL). • Queries that include calculations without changing the stored data are SELECT queries, not action queries. • Denoted with special symbols in the Navigation Pane: Make-table Append Update Delete IT User Services - University of Delaware

  4. Action queries • Function and alternatives • Make table query: Creates a new table • Alternative (simple cases) • Copy/Paste (Navigation Pane) • Append query: Adds one or more rows of data to a table • Alternative (simple cases) • Copy/Paste Append (Navigation Pane) • Append to existing table (Import) IT User Services - University of Delaware

  5. Action queries • Function and alternatives • Update query: Changes data values • Alternative (simple cases) • Find/Replace • Delete query: Removes rows of data from a table • Alternative (simple cases) • Select rows to delete, press Delete button or key. IT User Services - University of Delaware

  6. Building action queries • Build a SELECT query. • Tables, columns (fields), criteria that will be used in the action query • Test the results if building a make-table or delete query. • Choose the query type. • Make table, append, update, delete. • Add fields, criteria, as needed. • Check the results in datasheet view. • Use the datasheet view button, not the Run button. • Save and run the action query. • Watch for warnings or error messages. IT User Services - University of Delaware

  7. Make table query • What it does: Creates a table in the current database or in another database using the data from a select query. • Use to create: • a backup table • an archive table • a local copy of a linked (external table) • Keep in mind: • Backup tables are not a substitute for a database backup. • Tables created are static – current only when created. IT User Services - University of Delaware

  8. Append query • What it does: Inserts one or more rows of data into an existing table. • Use an append query • to add data to an existing table from another table or query in the database • to eliminate duplicates based on primary key • If append fails with error message, check for • primary key violation (duplicate records) • data type mismatch IT User Services - University of Delaware

  9. Update query • What it does: Changes data values permanently • Use an update query • to update data to current values without re-entering • to correct data entry errors • to correct inconsistencies in the data • addresses • dates • names IT User Services - University of Delaware

  10. Delete query • What it does: Deletes entire rows from a table. • Use a delete query • to remove large amounts of data from a table • Caution: • back up the data before deleting • set criteria with care IT User Services - University of Delaware

  11. Action Queries: Summary • Action queries • Make table • Append • Update • Delete • Alternatives (insimple cases) • Copy / Paste • Copy / Paste Append • Find / Replace • Delete manually Caution when using action queries or alternatives: very limited undo option; keep good backups. IT User Services - University of Delaware

  12. Resources • Web tutorials (VTC, lynda.com) • http://www.udel.edu/learn/ • MS Access help • F1 key or ? in upper right corner • Microsoft help • http://support.microsoft.com/search/ • IT Help Center • x6000 or http://www.udel.edu/help/ IT User Services - University of Delaware

  13. Thank you for coming! IT User Services - University of Delaware

More Related