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.