Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There are a variety of queries that you can use to modify your data. These are called Action Queries. When you want to move records from one table to another, combine an Append query and a Delete query. An Append query will add records that match your criteria to the end of another table. Then changing that same query to a Delete query will remove them from the original table. This has some nice practical uses. For example, may be once the Sales Rep leaves your company, you'd like to move them to a table of past employees, instead of looking at the fullest of everyone who have ever worked at your company.
The first thing I need to do is to create the new table to move the records into. It needs to have all the same fields and field names as the original content. So I'll close this table. I'll right-click on SalesReps in the navigation pane, and choose Copy. I'll right-click on SalesReps again, and then choose Paste. I'll change the name to SalesRepsArchive. I'll move the dot in front of Structure Only.
I don't want to move the Data. I just want the fields, and I'll click OK. That now we'll create our query to move the SalesReps who are no longer with us into that new table. Go to the Create Ribbon, and choose Query Design. Add the SalesReps table, and then close the chooser. I'll extend the table down to see more fields. In the Ribbon, change the Query Type to an Append query. In the pop-up window, choose the SalesRepsArchive table from the dropdown.
Note that you could also move the records into an entirely separate database, and take them out of this one if you'd like. We'll leave them in the Current Database and click OK. Now we have to add all the fields to the query. Double-click on each one. Make sure you don't miss any at the bottom. Scroll over to the right-hand side, so that you can see your EndDate.
And in the Criteria line at the bottom, type in Is Not Null, null means blank. By asking for all the EndDates that are not blank, Access will bring up all the records that have a date entered, indicating that the SalesReps employment with us has ended. If I go up to the Ribbon and I click on the View button on the upper left hand side, I'll see which records will be moved, and I can double-check everything for accuracy. If everything looks right, go back to the Design View again, and then choose Run.
I'll get a message; tell me the number of rows that will be appended. I'll click Yes. After the query runs, open up the SalesRepsArchive table, and you'll see all the SalesReps who are no longer with the company. I'll close it again. Now that the records have been added or appended to that archive table, we're ready to delete them from the main SalesReps table. Go back to the Query tools, Design Ribbon, and change the Query Type from Append to Delete. Run the query again, and it will now ask if you want to delete these same eight rows from the specified table.
Note that you cannot undo this deletion. We'll click Yes. Now when we open up our SalesReps table, all that are left are active employees. When we open up the SalesRepsArchive table, there are all of our past employees. Close both of those two tables. You can save this query for future use, or don't save it if you won't need it again. Because I'm probably going to do this update periodically, I am going to save it. But first, I need to go back to my Query tools, Design Ribbon, and change it back to an Append query.
Then I'll click OK. If I saved it as a Delete query and then accidentally ran it, I'd wipe out all my SalesReps before I move them to the archive table. So that's not good. So once I can verify that it's an Append query again, I'll save it, and I'll give it the name Archive Sales Reps. Using Append queries to automate the move of records from table to table is a very practical feature.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74249 Viewers
80 Video lessons · 129589 Viewers
52 Video lessons · 63861 Viewers
59 Video lessons · 49655 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.