MS Access 2007 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.
Step 1: Creating Make-Table Queries (Getting into Query Design View)
Figure 3-10: Query Design View available under “Create” command.
________________________________________
1. Once you have MS Access software up and running. Click the “Create”
command as shown in Figure 3-3.
2. Then “Query Design” on the ribbon bar.
Most of the time we create queries in Design View and these 2 clicks at
the appropriate spot and order will take you in the Design View of query
builder.
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.
3. The label 1 in the Figure 3-4 points to table tab.
4. The other tab shown 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.
Figure 3-11: Tables tab contains the list of all the tables inside your
database whereas Queries tab contains the list of all queries.
________________________________________
Step 3: Build Relationships & Select Fields
1. Once you have selected all the tables/queries, next step would be to
build the join relationship in order to extract data residing in
multiple tables. The join shown in Figure 3-12 with label 1 is called
Equi-Join. To create such join, simply drag the field from one table
(usually start with parent table field) and drop it on the field in the
other table.
2. Once you have selected all the columns/fields for your query by
double clicking the field names, click on the Exclamation (!) icon, also
known as Run button shown with label 2 in Figure 3-12.
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
Figure 3-12.
4. Also make sure you have selected a ‘Make-Table Query’ option from the
ribbon as shown in Figure 3-12 with label 4.
Figure 3-12: Design View of Make-Table query
________________________________________
Step 4: Select Make-Table Query Option
Figure 3-13: Make-Table query output gets saved as local table.
________________________________________
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
Figure 3-14: Location of Run, Save, View commands.
________________________________________
As shown in Figure 3-14, its better to save your Make-Table query by
clicking the save icon. 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 resultant local table we mentioned Step 4.
Finally Run your make table query 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, it’s
always better to first bring the data locally into the MS Access
database using this make-table technology and then analyze it to save
time.
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: