Using the Make Table Query to Collect Archive Data

Information stored in a database is likely to change over time.  When details change, data may be overwritten, and once this happens it is lost.  There are certain scenario's where it is important to have access to 'historic' data, by which I mean a snapshot of information which was stored in the database at a given point sometime in the past.  Let's take an Order Management System for example.  Suppose you need to find a customer contact name from two years previous?  If the customer in question happened to be an organisation or business, it is possible that there was a different person in post at that time.  In this case, it would be useful to have some facility to access data from that point in time.  Whilst we can't create this sort of archive from the present backwards,  we could have taken a snapshot of this data at periodic intervals previously.  One way of doing this was to have created a Make Table Query to extract relevant data from the database with a query and store the results in a new table.  These can then be saved in a separate archive database ready to be accessed if and when needed.

Taking the Order Management System scenario, there is data stored in four related tables, and only certain  fields from each table need to be archived.  We could simply make a copy of each of the four tables, but this would result in the storage of unnecessary information.  It would also be less convenient to access at a later point in time.

The related tables in question are tblCustomer, tblOrganisation, tblPerson and tblAddress.

Figure 1: The Make Table Query for an Order Management System.

Creating a Make Table Query

Before we create the Make Table Query, you might like to create a new blank database file.  This is so we have a separate database into which we can paste the new table from the query.  In this exercise I have called it HistoricData.accdb.

Here is the procedure to create a Make Table Query:

1. Open the database which we are going to query (In my case this is the Order Management System).
2. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).
3. Select the tables to be used from the SHOW TABLE dialog form. I have selected  tblCustomer, tblOrganisation, tblPerson and tblAddress.
4. Next select all the fields from the existing tables to be used in the query.  These not only form the query results, but also the structure for the new table.  I have chosen fields from all four tables so that the new table will be self sufficient. 
5. Amend the table relationships (or Joins) as required.
6. Click the MAKE TABLE icon (located in the QUERY TYPE group of the DESIGN ribbon).  This opens the MAKE TABLE dialog form (see Figure 2 below).

   

   Figure 2: The Make Table Dialog Form.

7. Enter the name of the new table that we are going to create.  I have called mine tblCustomerTeamsMay2012.
8. Click the option button for ANOTHER DATABASE. This ensures the new table will be created in a different database.
9. Browse to the location of the external database.  I have called mine HistoricData.accdb.  Double click the file to select and then click OK to close the dialog form.
10. Test the Query by clicking the DATASHEET VIEW icon (located in the RESULTS GROUP of the DESIGN ribbon).
11. If you are happy with the data displayed, you can run the Make Table Query by clicking the RUN icon (located in the RESULTS group of the DESIGN ribbon).  This will now create the new table in the external database.

You can now open the external database and view the newly created table:

Figure 4: The newly created tblCustomerTeamsMay2012 table (Design View).

Now all the database administrator has to do is run this query every month, changing the new table to the appropriate month.  Over time, we collect a 'warehouse' of data ready to be accessed if and when needed.  Since this data is stored in a separate database, it does not impact upon the performance of the actual system from which the information was collected.

Append Queries: Automatically Append Data from One Table to Another

Append Queries are really useful when you acquire a new table full of data, and want to add it to an existing table in your database.  This scenario may arise, for example, if your company purchases a mailing list, and you are asked to add the new names to those you already have stored.  Rather than having to retype the new names by hand, an append query can be set up to copy the data from the new table into that which is already in use.

I have created an exercise to demonstrate how this process works.  Lets begin by looking at the two tables we are going to be working with:

Figure 1: tblContacts - the existing table consisting
of  ID (autonumber), FirstName (text) and Surname (text).

Figure 2: tblMoreNames - new data to be appended, consisting of
CustomerId (autonumber), Title (text), Initials (text), and LastName (text).
Notice the fields names are somwhat different and there is also a
risk of duplicating the ID fields from both tables.
More about this soon.

As you can see, figure 1 shows tblContacts, our existing table containing 15 records.  We are going to create an append query to add the 10 new records from tblMoreNames to the data already stored in this table.  You may have noticed that the field names contained in both tables are slightly different, and tblMoreNames has a Title field which tblContacts does not.  You may also have spotted that there is a risk of us attempting to append duplicate data from the CustomerID field of tblMoreNames.  Access would prevent this because the ID field in tblContacts is a Primary Key, so needs to be a unique value.   The simple solution to this, is for us not to include the customerId field (or anyother incompatible field)  in the append query.  As such, we shall just append data from the Initials and LastName fields of tblMoreNames to the FirstName and Surname fields of tblContacts.  Although the field names are slightly different, the data they contain is compatible, and for the purposes of this exercise, it is ok for us to append an Initial in place of a FirstName - the idea here is to demonstrate that the field names do not have to match exactly.

Creating an Append Query

Here is the procedure for creating the append query:

1. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).   The QUERY DESIGN window then opens along with the SHOW TABLE dialog form.   
2. The next step is to add tblMoreNames to the QUERY DESIGN window.  Do this by clicking ADD in the SHOW TABLE dialog form.  Notice it is the table containing the data to be appended that we have selected.
3. Click the APPEND icon from the QUERY TYPE group of the DESIGN ribbon.  As you do this, you will see the APPEND dialog box open.
4. You are now asked to select the name of the original table to which the new data is to be appended. So select tblContacts from the drop down list.  
5. You are also asked whether this table is stored in the current database or in an external database. In this exercise both tables are stored in the current database.  This is the default button displayed in the option group, so there should not be any need to change it.
6. Click OK to close the dialog box.
7. Next we are going to select the fields from tblMoreNames to be appended. To do this drag and drop the Initials and LastName fields from the table (in the top half of the window) down onto the design grid.
8. Next we are going to tell Access which fields the data from Initials and Lastname will be appended to.  To do this go down to the APPEND TO row of the design grid (see figure 3 below), and select FirstName in the Initials column, and Surname in the LastName Column.

   

   Figure 3: The Query Design Grid.

9. We could add query criteria at this stage, but this particular exercise does not require any.  If we did, however, this is added in the CRITERIA row just like it is with a select query. 
10. If you want to view the data that is going to be appended, click the VIEW icon from the RESULTS group of the DESIGN ribbon.  It is especially important to do this if any if any criteria is applied in step 9 above.
11. Once you are satisfied the correct data is going to be appended, click the RUN icon, again from the RESULTS group of the DESIGN ribbon.
12. A dialog box opens informing us that 10 rows are going to be appended, and asking us to confirm that we want to go ahead with this operation.  Click YES to complete.

To see the result of our Append Query, re-open tblContacts.  

Figure 4: tblContacts after the
Append Query has been run.

We can now see the new records appended to the end of our original data.  Notice each of the newly appended records has been automatically allocated a unique ID number.  This, of course, is because the ID field in the original table had been set to the AUTONUMBER datatype.  

Creating an Update Query to Update a Salaries and Wages Database

Imagine you have responsibility for the management of a Company's Salaries and Wages database.  The company employs 200 colleagues, and it is your job to process the annual pay increase. The problem you face is that different groups of employees receive a different percentage rate increase; and each employee within a group may have a different salary level to start with.  So what is an efficient way to update each employee's salary without having to having to go through each record manually?  A good solution would be to use an Update Query.

Update Queries are a type of Action Query, similar in nature to Delete Queries, which I blogged about last month.  However, whereas a Delete Query will find a group of records matching a given criteria and delete them from the table, the Update Query will find a group of records matching a given criteria and update one or more fields in each of those records.  So for example, we can use an Update Query to update the salaries of all staff with the job title, Telesales Administrator.

The great thing about using an update query is that we can update a field to an absolute value or a value based on a calculation.  So in addition to being able to update all Telesales Administrator salaries to a fixed value such as, say, £15000, we could also update all existing Telesales Administrator salaries by, say, 5%.  So in a scenario where each Telesales Administrator is on a different salary level to begin with, it makes sense to do the latter to update each unique salary figure.

Please feel free to download the completed solution to the above scenario by clicking this link: Example Update Query.  I recommend you begin by opening tblEmployees and take a look at the 12 sample records and then see how the records for Telesales employees change after the qryUpdateTelesalesSalaries Update Query is run.  You can also follow the instructions below to find out how to create this Update Query yourself.

Figure 1: tblEmployees.  As you can see, there are 6  Telesales Employees.
Notice how their salaries change after the Update Query is run.  

Creating an Update Query

So how do we create this Update Query?  It is easy.  We do it in two stages.  First of all we create a standard Select Query which uses a criteria to find the group of employee records who have Telesales as their job title.  Then we convert the query to an Update Query, and in the process, we enter the calculation which updates the existing salary level for each Telesales Administrator.   Lets do this step by step:

Stage One - Testing the Criteria with a Select Query

1. Click the QUERY DESIGN icon (located on the QUERIES group of the CREATE ribbon).
2. Select the tlbEmployees table from the SHOW TABLES dialog form.
3. Double click the JobTitle and Salary fields from the employees table, so they appear as columns on the Query Design Grid.
4. Enter ="Telesales" in the criteria row of the JobTitle column.
5. Click the RUN icon to run the query.

The results of the query are shown in the screenshot below:

Figure 2: Results of the Select Query
in stage one.

As you can see, the query has found all six records where the employee's job title is Telesales.  We could have included their names and employee id's in the results, but this is not necessary.  Once we convert it to an Update Query and click run, we won't actually see the query results until we open the underlying table and note the updated fields in each of the records matching the criteria.

Stage Two - Converting to an Update Query

1. Re-open the Select Query in Design View.  If the Select Query is already open in DATASHEET VIEW, simply click the DESIGN VIEW icon from the VIEWS group of the HOME ribbon.  Otherwise you can highlight the Query name in the NAVIGATION PANE, right click the mouse, and then click the DESIGN VIEW icon from the drop down menu.
2. Once the Query opens in DESIGN VIEW, click the UPDATE QUERY icon from the QUERY TYPE group of the DESIGN ribbon. The Icon is then highlighted in orange. 
   You will also notice that the SHOW and SORT rows of the QUERY DESIGN GRID have disappeared, being replaced by a new row called UPDATE.
3. All we need to do now is enter the calculation into the UPDATE row of the Salary column.  The calculation to enter is:  [salary]*1.05
   
   

   

   Figure 3: This is what the Query Design Grid should
   now look like for the Update Query.
4. Click the RUN icon from the RESULTS group of the DESIGN ribbon.  
5. Click YES when you  receive a warning message saying "you are about to update 6 row(s)".

Once you have run the query and clicked YES to the warning message, you won't see the changes until you re-open (or refresh) the underlying tblEmployees table.  When you do so, you will see that the Salaries for Telesales staff have been updated by 5% as expected (see figure 4 below).

Figure 4: The Updated tblEmployees Table.