Queries for data modification: Action queries

Queries for data modification:Action queries MS Access 2007 IT User Services - University of Delaware

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

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

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

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

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

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

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

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

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

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

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

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

Queries for data modification: Action queries