Queries for data modification Action queries MS Access
- Slides: 13
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 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 ◦ 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
- Action queries in access
- Terminal access controller access-control system
- Terminal access controller access-control system
- Using subqueries to solve queries
- Standing queries are
- Dimensional modeling basics
- Multirelation queries
- Wild card queries in information retrieval
- Any queries
- Complex sql join queries
- Basic retrieval queries in sql
- Hotel.hotelno=room.hotelno(hotel room)
- Sql insert update delete query
- Answering my queries