MS Access 2003 Step By Step - Asim Abbasi
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Chapter 3: MS Access Queries
Make-Table query is just like Select query the only difference is that
the output of the query gets saved in the form of table or more
precisely MS Access Local Table.
By combining the power of MS Access Macros and Make-Table Query you can
automate the process of running long queries. In other words, queries
that take long time to execute, the output of those queries can
automatically me saved in MS Access using both Macros and Make-Table
query technology. We will learn about it in great detail in the chapters
ahead.
Let’s look at the steps of developing a Make-Table query.
Creating Make-Table Queries
1. Once you have MS Access software up and
running. Click the Queries object under the Objects group as shown in
the screenshot above.
2. Then click the ‘New’ command above objects group.
3. Select the Design View option from the popped up window.
4. Finally click the <OK> button.
The first four clicks are exactly the same as in case of Select query
discussed before. The target is to enter into the Query Design View.
Step 2: Add Tables/Queries Required
Once you complete the step 1, another window will
popup, allowing you to select tables (local/external linked) or already
existing queries for the design of this new query.
1. As shown in the slide above label one points to table tab.
2. The other tab shown in the slide above is Queries.
Under the Table tab you would be able to see all the tables in the
database no matter whether they are internal or external linked tables.
Whereas the Queries tab list all the already existing queries inside
your database.
Step 3: Build Relationships & Select Fields
1. Once you have select all the tables/queries
next step would be to build the joins relationship in order to extract
the data from multiple tables. The join shown in the screenshot above is
called Equi-Join. To create such join, simply drag the column from one
table (usually start with parent table column) and drop it on the column
in the other table.
2. Once you have selected all the columns/fields for your query
click on the Exclamation (!) icon also known as Run button.
3. To filter the number of records or in other words to get less
number of records in the output of the query, define the Criteria as
shown in the screenshot above.
4. Also make sure you have selected a
‘Make-Table Query’ option from the drop down menu.
Step 4: Select Make-Table Query Option
Step 4 is the only step that is different from Select Query design. From the drop down menu select the option of ‘Make-Table Query’ as shown in the screenshot above as well as the zoomed-in version below.
As
explained before, the output of the Make-Table query is not displayed
rather saved in the form of table or more precisely MS Access Local
Table. Once you select this Make-Table Query option another window with
the name ‘Make Table’ pops up and you have to enter the name of the
table where the output of this query will be saved. In the example shown
in the screenshot we have entered the make-table name as ‘My Test
Table’. You can have any name as per your likeness or need. It also
gives you the option of saving the table into another database. Once you
have command over creating Make-Tables, we recommend that you should try
the ‘Another Database’ option too.
Step 5: Save & Run Make-Table Query
1. As shown in the slide above, finally its
better to save your Make-Table query by clicking the save icon on the
tool bar. Once you click this save button, you have to provide the name
for this make table query. Don’t confuse this name with the name the
output table we mentioned Step 4.
2. Notice the icon that represents the Make-Table query is
different from that of Select query.
Finally Run your make table query either by clicking Query and then Run
from the menu bar of the MS Access or simply by clicking the Run icon on
the tool bar as shown below.
Depending on the size (i.e. the number of
tables/queries that are used) of the query, the data its extracting from
and the network speed, once the output is ready, a window will popup to
make sure that you want to have this data saved to your hard disk. Click
<OK> and then move to the Table object under the Objects group. You will
see the My Test Table there. Double click the table to view the output
of your make table query.
The benefit of make-table query is, if you want to analyze resultant
data from a Select query having many tables and lots of joins, its
always better to first bring the data locally into the MS Access
database using this make-table technology and then analyze it.
Chapter 3: MS Access Queries
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Share with others: