Creating a Make Table Query

Tweet
Share
Share
0 Shares

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 1 below).

    make table dialog form
    Figure 1: 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:

new table
Figure 2: 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.

Related Posts