Lights, Camera, Action!

Microsoft Access Action Queries

Action Queries can change the data in the Tables. For example, an Update Query can find any movie released before the year 2000 and set the rental price (RentalPriceID). The other Action Queries include Append (add more Records), Delete (subtract Records), and Make Table (Copy the Records from a Query into a new Table.)

Deleting Data: Consider This!

1. Consider This: Should You Delete Data?

Deleting data is not a good idea in a relational database. When you create a Receipt in our database, you need information from five Tables. Consider this scenario.

Say you deleted the Movie, “Brave” from tblMovies. Say the Primary Key, MovieID, for this Movie was 407.

Now, you want to run a Report that looks up all of the Receipts. Any Receipt that had MovieID 407 will be incomplete. There is no data because that Key is missing.

This is not good.

2. The preferred method is to Archive a Record by marking it as Archived, Done or Obsolete.

Create a Make Table Query
The Archived Movies will be selected and copied into a new Table.

Create a Delete Query
The last Action Query deletes the Archived Movies from tblMovies.

Before You Begin: A sample database is open. This “Front Row Video” database has the following eight Tables: tblCustomers, tblGenre, tblMovieRented, tblMovies, tblMovieSearch, tblRating, tblReceipt and tblRentalPrices.

Select the Oldies

Create a Make Table Query that will mark all of the Movies released before 1990 as “Archived.” The best place to start is to make a Select Query and see if it finds the right Movies. When it works we’ll save the Select Query as an Make Table Query.

1. Try it: Create a Select Query

Go to Create ->Queries ->Query Design.
You will be prompted by the Show Table.
Select a Table: tblMovies.
Click Add and Close the Show/Table Window.

Try This, Too: Add Fields
Add these Fields to the QBE Grid:
MovieID, Movie, Year, Archived, Rating, Genre, Stars and RentalPriceID.

And This: Add a Criteria
Select a Field: Year.
Enter a Criteria: <1990

When you Run this Query you should find 73 movies that have our Criteria.

Keep going…

Copy to a New Table
A Make Table Query selects the Records that match the Criteria and copies those Records into a new Table.

4. Try it: Create a Make Table Query
The Query is still open.
Go to Query Tools ->Design->Query Type.
Select a Query Type: Make Table.

What Do You See? You will be prompted to name the new Table.
Enter the Table Name: tblArchiveMovies.
Click OK to Run the Make Table Query.

Do This, Now: Save the Make Table Query
Go to File->Save Object As.
Enter the name: ArchiveMovieMT,
where MT means this is a Make Table Query.
Keep going…

Review the Data
Did all of the Records get copied to the new Table when we ran the Make Table Query?

5. Try it: Review the Data
Go to All Access Objects->Tables.
Open a Table: tblArchivedMovies.

What Do You See? There should be 73 Records in tblArchivedMovies.
OK. Save and Close ArchiveMovieMT.

The Last Action Query

A Delete Query does exactly what the name spells out: It deletes data. As mentioned earlier, deleting data is not the best practice for a database administrator. However, this option may be better than marking bad records as archived.

The following example will use a copy of the Movie Table to test the Delete Query.

1. Try it: Create a New Select Query
Go to Create ->Queries ->Query Design.
You will be prompted by the Show Table.
Select a Table: Copy of tblMovies 04-16-2014.
Click Add and Close the Show/Table Window.

Try This, Too: Add Fields
Add these Fields to the QBE Grid: MovieID, Movie, Year, Rating, Genre, Stars and RentalPriceID.

And Try This: Add a Criteria
Select a Field: Year.
Enter a Criteria: <1990

Do This, Now: Run the Select Query
Did this Select Query select 73 Records?

Create the Delete Query
So the Select Query works. Now, you can change it into a Delete Query.

Before You Begin: Change the View
Go to Home ->Views->View.
Select a View: Design View.

2. Try it: Create a Delete Query
Go to Query Tools ->Design->Query Type.
Select a Query Type: Delete.

Try This, Too: Run the Delete Query
Go to Query Tools ->Design->Results-> Run.

What Do You See? You will be prompted that you are about to delete 73 Rows. You are also reminded that there is no UNDO. This is permanent data heaven.

Click Yes to delete the data from the Copy of tblMovies 4-16-2014, Table.
So, let’s go see what’s left in this Table.

Review the Data
3. Try it: Review the Data
Go to All Access Objects->Tables.
Open a Table:Copy of tblMovies 4-16-2014.

Try This, Too: Sort the Records
Select a Field
: Year.
Go to Home ->Sort & Filter->Ascending.

What Do You See? There were 420 Records in Copy of tblMovies 4-16-2014. The Delete Query deleted 73 movies, leaving 347.

The 73 movies were effectively deleted as promised.
Close the Copy of the Movies Table.

Do This, Now: Save the Delete Query
Go to File->Save.
Enter a name: DeleteArchiveMoviesDQ.
Close the Delete Query.

SUMMARY: We also created several Action Queries to simplify the task of archiving the old movies. The Action Queries included a Make Table Query, ArchiveMovieMT, to copy the Archived Records into a new Table: tblArchiveMovies. We also designed and tested a Delete Query: ArchiveMovieMT

I have a video on YouTube that shows these steps: https://youtu.be/BLwn4IxSoTo

You done good! Go get the cookies!

Please let me know if I answered your question.

eBeth
Elizabeth Nofs, the Computer Mama

New Subscribers Welcome: https://thecomputermama.us
Buy the books at Amazon: https://thecomputermama.us/books-page/

Please consider my course on UDEMY, The Beginning Guide to Microsoft Access. This course is free.

The Beginning Guide to Microsoft Access 2013 – Udemy