Queries for data modification Action queries MS Access

  • Slides: 13
Download presentation
Queries for data modification: Action queries MS Access 2007 IT User Services - University

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

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

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

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)

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

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

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

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 ◦

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

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

Action Queries: Summary Action queries ◦ ◦ Make table Append Update Delete Alternatives (insimple cases) ◦ ◦ 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

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

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

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