“Make-Table” Query

In Access, we have the ability to create a permanent table from our query.

The “Make-Table” query in Access creates a new table of results from our query.

This new permanent table may be useful for different purposes.

You can use these as a basis for new “Queries”.
It can serve as a backup for your data or we can use this information to the underlying tables in a table below to make and export to eg Excel.

We start by creating a new standard query, so click the “New” button.

Select “Design View” in the window and click OK.

We are going to build a query to create new table of customers who are not from the United States.
For example, therefore:

Choose a table and click “Add” (“Add”).

As criteria for the Country field, we use <>”USA” i.e. not equal to USA.

Then we click on the dropdown list and choose the query type as “Make-Table”.

That gives us the “Make Table” dialog.

We give a name for our new table.

You also have the option to create the table in the database in which you’re working, or another database somewhere on your hard disk.

Once you’ve made your choices and the name has been entered, we click Ok.

Access asks you if you want to paste the records into a new table.

Then you click Yes, and as you can see the table “International Customers” was added to our “Tables”.

Update “Queries”

If we have a lot of information in this table to be replaced or updated simultaneously, eg change postal codes, we can use the “Update Query”.

As always, we create a new query in “Design View”.

Before we place fields in the query list, we click on the drop down list and choose “Update Query”.

You can see that the grid is now slightly modified. You now have a line “Update To”.

we must drag the field from our table to the grid “Field” that we want to update.

We give a criteria, in our case 9100.

And in the “Update To” box, we give the new entry as 9999.

Result, all postal codes with the number 9100 will now be changed to the new 9999:

Append Queries

We use “Append Query” for copying and pasting data to a different table where the data is based on a selection.  You can only copy data from one table to another table when you are copying data from the same data type.  For example, you can only copy data from one number type field to another number type field.

If the fields have the same “name tags”, Access will automatically select the data to copy to the fields in the destination table.

As long as the fields have same “Field Size”, no information will be lost even though they have a different field name.

You can specify the fields in the “Destination table” where your copy will be placed, when we “Append Query” in Create query in design view.

To copy only selected records from original table to the destination table, we give criteria, in the appropriate field in the QBE.

In this way, only the fields that meet our criteria are copied.

As always, we create a new query in “Design View”.

Before we place fields in the query grid, we click on the drop down list and choose “Append Query”.

We select “New Table”.

You can also choose to copy the data to a table in the database that you’re working, or a table in another database somewhere on your hard disk.

and click OK.

We are going to do something unusual, but this is only meant as an example so …

We copy our customers from the customer table to our suppliers table.

We drag the field name from our customer table to the “Field” box of our QBE grid to the “Append To” in our destination table which is our suppliers.

If we now “Run” the “Append Query”, Access will ask us whether we are sure that we want to copy these fields.

Once we have clicked Yes, we can not undo this action.

If we want to close the Query, Access asks if we want to save. Choose “Yes” and enter a name or select “No” if you do not want to save.
You do the Append Query and when you do not want to store it, as you no longer need it in the futuer, the query is already performed on the destination table.

Result:

“Delete Queries”

You can use “Delete Query” to remove records in a table based on special criteria.

As always, we create a new query in “Design View”.

Before we place fields in the query grid, we click on the drop down list and choose “Delete Query”.

In the criteria, we give the name of the customer we want to remove.

Now, if you “Run” the query,, Access will display a warning dialog, asking if you are sure.

If you answer Yes, you will lose the data.

It is therefore advisable to first make a copy of your table to before you proceed to delete records.

“Crosstab query” (“Crosstab query”)

A “Crosstab query” is used to make calculations between fields from the table by using titles and headers.
The “Crosstab query” can only be based on a table or Query.
Something like a “Pivot Table” in “Excel”.

We make a new query by clicking “New “.
Choose “Crosstab query” from the list, and click OK.

In the first Wizard window, we choose the Table or Query from which we make our “Crosstab query”

In the second window, we choose the fields (max. 3) that we want to display as header for the row.
Here we choose our employee:

In the third window we choose the data that we want to display as header for the column.
We want to see how many orders a representative has each year, so we choose “OrderDate”.

In the fourth window, we calculate what we want to execute.
In this case the sum of orders per employee.

In the next window, we give the crosstab query a name and click “Finish”.

Result:

This is the result of our crosstab query in “Design view”:

Optionally, we can insert criteria manually.

The “Find Duplicates” Query

To find data that occurs repeatedly in our table, we use the “Find Duplicates Query”.

We make a new query, so click New, and now choose “Find Duplicates Query Wizard”.

Click OK.

In the next window you will have the choice of the table, query, or both, from which we want to find duplicate records:

choose your table and click “Next”.

In the next window we need to specify which fields the query should look for.

Choose the field (s) and click the > button to the fields where you want to transfer them to find the right part of the window.

In the next window you make the choice of the fields you want to see, once the query is running.

Click Next

In the next window give a name to your query and click “Finish”.

Result:

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats