access 'action queries'

The queries we studied in the previous lesson are all 'selection queries'.

Selection queries are used to extract information from tables. They never change the table in any way.

However, there's another type of query that can be used to modify tables - an 'action query'. As the name implies, an action query actually does something - it can add, delete or update data in tables.

We'll look at an Update Query first because we need it to update the data in the Financial database for our tests on different functions in queries.

an update query

For many of the examples in the following tutorials we're going to look at the Profsr Financial Services database. It contains a lot of data that is appropriate for the examples on dates, functions and so on. If you've already downloaded the database you should look at what it contains.

Open the SalesM table and sort it in descending order on the [date submited] column. That field is important because it's the transaction date that we'll use in all our queries.

The data in the table are probably way out of sync with your actual date. There's no way to tell when you are.

You will need to write and test queries for yesterday, this month, last month, etc. and if the dates in the table are not current, nothing will ever come out.

So, what you will do is change the dates to make them current using an update query.

As you can see, the latest date in the table is in Dec 2010. If you're in Dec 2012 you'll have to add 24 months to the table date. It doesn't have to be 100% exact - even if the table dates are one or two months ahead of your date, that should be OK, but they shouldn't be behind you because then you won't have anything in 'this month'. Got that?

You'll need the DateAdd() function for the update. Look it up in the DATE/TIME functions reference.

Then Create a query, select the table to use in the query and you'll see the query types appear. Select 'Update' for the type and the grid will change to show the options you have. You only need to use a single column from the table.

If you make a mistake just correct it. For example, if your dates are not high enough, do it again just adding another 3 months. Or if they're too high, bring them back by adding -3 months.

a delete query

The other form of Action query we'll look at now is the Delete query.

As the name implies, it is used to delete information from a table.

But be very careful with it!

You cannot delete a single field from a record - a delete query always deletes a complete record. For example, you cannot use a delete query to remove a customer's phone number - you can only remove the entire customer.

Normally you would use it to remove test data that you inserted into a table to test queries and so on.

And if you run a delete query on a table without a criteria, it deletes all records from the table!