Once you've created tables in a Access databases, you can use the magic of Access queries to change how you view the data in these tables. A query normally lets you view the data in different ways without actually changing any data. This article takes at look at the different kinds of queries Access offers you. There are two main kinds - queries which list but don't actually change anything (Select queries) and queries which do change data (Action queries). We'll start with the Select queries.

Suppose you have an Access table listing staff details such as firstname, surname, post, town, salary etc - and suppose you've called the table TblStaff. When you create and save a query, for example to sort your staff names in Surname order, the query you save is the instruction to sort the data in the Staff table by surname order. The saved query contains no data, just the selection criteria. So if you run this query on a table containing five names, the query will display the five names in surname order. If you then add three more names to the staff table, and run the same query again, you'll display a list of eight names in surname order.

Queries usually specify one or more tables, and then a list of fields (table headings) to display, sort order if necessary, and with any particular filter criteria, such as only for the surname "Smith". This is the basis of an Access Select query. We select records from a named table, show specified table headings in a particular order, and throw in some filter criteria for good measure.

When you create and save a query its best to give the query a name which describes what it does. So QrySaffSurname is better than Query99. In six months time when you want to recall what your queries do, you'll thank yourself for this. It's also good practice to start the query name with Qry followed by the table name and then a word or words describing the query action. It's good practice too, to avoid spaces in query names, as the Access code behind the scenes is not always able to detect a name split over separate words. You may want to capitalise the first letter of each part of the query name so you can read it more easily. Alternatively you can create a visual space between words by using an underscore.

Once you've used Select queries in Access, which display data in different ways without changing any of the actual data, you might want to move to other types of queries which DO change the actual data. These are the Action Queries. If you intend to run an Action query, WHICH WILL CHANGE TABLE DATA, it's wise TO CREATE A BACKUP OF THE TABLE FIRST AS A PRECAUTION.

Action queries are used to change data in different ways. For example suppose at the end of the year all staff receive a 5% salary increase (they wish!). You can create an UPDATE query which specifies the table TblStaff, and instructs the salary fields to be updated to 1.05 times the original value.

When the Action query runs you see a prompt warning you the action is not reversible (you can't undo an Action query). When you proceed, the query will run and the table records are updated updating each salary record in the Staff table by multiplying the original values by 1.05.

The next Action query we'll describe is the MAKE TABLE query. Suppose some of the staff in your staff table move to your London office, and you want to make a new table just for these staff, to be called TblStaffLondon. Assuming Town is one of the headings in the Staff table, we can create a MAKE TABLE query to create a new table for these staff from the original table. This query would list all the headings from the original table, but use a filter criteria for the town name, "London". The MAKE TABLE would select all the records for staff with "London" as their town and then make a brand new table for them only. So you would end up with two tables, TblStaff and TblStaffLondon.

Now we'll look at another action query, the DELETE QUERY. Once you've created your new staff table for London staff, you can create a DELETE query to remove all the London staff from the original table. So you end up with the original staff table minus all the London based staff, and your new table TblStaffLondon.

The last action we'll look at is the APPEND QUERY. Suppose the London staff move doesn't work out, and all the London staff want to return to the fold and re-join the original TableStaff. This can be achieved by creating an APPEND QUERY. An append query adds records from one table to another table. So we need to APPEND the London staff table to the original staff table. It's a straightforward operation provided the same headings are used in both tables. When the query is run, all the London staff records are added to the original TableStaff.

So you can see there are two main kinds of Access queries, Select queries which look at data, and Action queries which change data. Using these with care allows you to create very effective Access databases. To take the next step forward why not sign up for a training course to really boost your Access skills.