Creating Make Table Queries

The Make Table query is a select query that pulls data from a database based on certain criteria, and then saves that data into a new table. The new tables that result from Make Table queries can be used for different purposes. For example, a table can be used to hold inactive records for a specific point in time and then used as the basis for historical reports about that period. The Make Table query is also useful for making some information in an existing table available to other users without exposing all fields of the table. New tables can also enhance the performance of very complex queries based on multiple tables. The table becomes the record source for forms or reports, and the query doesn't have to run each time those objects are opened.

Using a Query to Create a Table

As we pointed out earlier, the Locations table was added to the database after the Access: Structuring & Relating Data course. Let's take a look at the data currently stored in the Locations table.

Step1. To open the table,

Double-Click tblLocations

As we can see, the Locations table keeps track of information about campus locations. However, when the Locations table was created, it wasn't properly normalized. Notice that every record for a location includes repetitive information for building code, building name, street address, city state, and zip codes. If one of these values needed to be changed for a building, we would have to update every location record for that building. It also increases the chances of data entry errors since that information has to be entered over and over.

To fix this, we will create a Buildings table to hold the information that is really about the building and not about a specific room in a building.

Step2. Close tblLocations.

Step3. To begin creating a new query, on the Ribbon,

Click the Create tab, Click 

The tblLocations holds both location and building information. We want to move the building information to a new table.

Step4. To select the table we need to create this query, in the Add Tables pane,

Double-Click tblLocations

Step5. Resize the tblLocations window as needed.

The fields we need to create tblBuildings are BuildingCode, BuildingName, StreetAddress, City, State, and Zip, we'll add those to the design.

Step6. To add the needed fields to the grid,

Double-Click the BuildingCode, BuildingName, StreetAddress, City, State, and Zip fields

Step7. To preview the query, on the Ribbon,

Click 

The result is disappointing in that buildings are listed for every location in tblLocations. This is in part due to the data not being normalized. We will fix this shortly.

For our purposes here, we only want to see unique building records instead of duplicates. We can modify the properties of the query to get that result.

Step8. To return to Design View,

Click 

There are properties associated with different elements of a query's design. We want to see the properties of the query — to do this, first we'll need to open the Property Sheet, then make the query the focus in order to see its properties.

Step9. To view the query properties, in the Show/Hide group of the Query Design tab,

Click

The Property Sheet appears, but may not be displaying information about the query. Let's fix this.

Step 10. To display the query properties, in the top half of the Query window,

Click in the empty gray area

The Property Sheet now displays information about the query:

There are two properties that we need to examine: Unique Records and Unique Values. If the Unique Records option is set to Yes, only records that are unique in their entirety would be displayed. It may at first seem like this is what we want, but since each record has a different office, changing this setting would not actually change the query results. We would get the same result that we have now since every record has a unique location id in the Locations table.

When the Unique Values option is set to Yes, Access examines the data in the query results and displays only unique values in those results, without taking into consideration the entire record in the source table.

Since we want every unique building to display in this query, we will set the Unique Values option to Yes.

Step11. To change the Unique Values option, in the Unique Values field,

Double-Click No

The Unique Values property is now set to Yes.

Step12. To see the results of the query, on the Ribbon,

Click 

We see the names and addresses of the 7 buildings of the University. Now that we have verified that the query is finding the records we intended, we can run the Make Table query.

Running a Make Table Query

We need to tell Access that we want this to be a Make Table query instead of the default Select query type.

Step1. To return to Design View,

Click 

Step2. To designate the query as a Make Table query, on the Design tab of the Ribbon, in the Query Type group,

Click 

You see the Make Table dialog box:

This dialog box is asking us to name the table that will be created. In the bottom half of the dialog box, we have options for saving this new table in the current database or in another database. We will accept the default and save the table in our current database. But keep in mind that we could save the created table in a separate database if we wanted to.

Step3. To name the table, type:

tblBuildings Enter key

We already know the query is giving us the data we want, but running it from the View button really just gave us a Select query view of the results. It did not create a new table. To initiate the Action of making the new table, we need to use the Run Query button, the red exclamation point icon, on the Ribbon.

Step4. To run the Make Table query, on the Ribbon,

Click 

A dialog box appears:

Access is telling us that we are about to create a new table with all of the selected records from our query.

Step5. To complete the creation of the new table,

Click 

The created table, tblBuildings, is now listed in the Navigation pane. Since it is unlikely we would run this query again we won't save this query.

Step6. To close the query without saving changes, at the top of the object workspace,

Right-Click the query tab, Click Close, Click 

Adding a Primary Key to the Buildings Table

While we have created our new table called tblBuildings, it does not yet have a primary key nor is it related to any other tables. There must be a primary key assigned before we can establish relationships to other tables.

Let's assign a primary key to the Buildings table.

Step1. To open tblBuildings in Design View, in the Navigation Pane,

Right-Click tblBuildings, Click Design View

We will designate the Building Code as the primary key.

Step2. To select the BuildingCode field, if necessary,

Click the BuildingCode field

Step3. To set the BuildingCode field as the primary key, on the Ribbon, in the Tools group,

Click 

We now see the key icon next to the field name indicating it has been set as the primary key.

Step4. To save the changes to the table design, press:

Control key + S

Step5. Close the table.

Creating Relationship for the Buildings Table

The Buildings table needs to be related to the Locations table.

Let's relate the Locations and Buildings tables.

Step1. To access the Relationships window, on the Ribbon,

Click the Database Tools tab, Click 

Step2. If necessary, in the Add Tables pane, switch to the Tables tab.

Step3. To add the Buildings table to the Relationships window,

Double-Click tblBuildings

NOTE: If tblBuildings does not appear in the Tables tab of the Add Tables pane, switch to the All tab — tblBuildings should be displayed there.

Step4. To position tblBuildings closer to the tblLocations table,

Press & Drag the tblBuildings title bar and move it to the left of tblLocations

The table is now next to tblLocations.

Step5. To expand the field list, if necessary,

Press & Drag the bottom border of the field list

Step6. To create a one-to-many relationship, from tblBuildings to tblLocations,

Press & Drag BuildingCode in tblBuildings onto BuildingCode in tblLocations

You see the Edit Relationships dialog box:

Step 7. To enforce referential integrity,

Click the "Enforce Referential Integrity" checkbox

We will also cascade updates in case a building's code ever needed to be changed.

Step8. To cascade update related fields,

Click the "Cascade Update Related Fields" checkbox

Step9. To create the relationship,

Click 

Notice that the fields we used to create the Buildings table are still in the Locations table. We will remove these duplicate fields next.

Step10. To close the Relationships window,

Right-Click the Relationships tab, Click Close

A dialog box will appear, asking if we want to save changes to the layout of the Relationships.

Step11. To save the layout changes,

Click 

Removing Duplicate Fields

When we used the Make Table query to create the Buildings table, the data also remained in the Locations table. We would like to remove the duplicate information. It might seem that a Delete query would be used to do this but that is not the case. Delete queries allow us to delete rows of data, not fields.

Let's remove the duplicate fields,

Step1. To open the Locations table, in the Navigation pane,

Right-Click tblLocations, Click Design View

Step2. To select BuildingName,

Click the gray box to the left of BuildingName

Step3. To select the rest of the duplicate columns,

Press & Hold the Shift key, Click the gray box to the left of Zip

Step4. To start to delete the columns, press:

Delete key

You see a dialog box asking if you want to permanently delete the selected fields and data in the fields:

Since all this information is now stored in the Buildings table, we can delete it from tblLocations without losing data.

Step5. To finish deleting the fields,

Click 

Step6. To save the changes to the design, press:

Control key + S

Step7. Close the table.