MS Access 2007 Step By Step - Asim Abbasi

Chapter 3: MS Access Queries

Page #: 1 | 2 | 3 | 4 | 5 | 6

Five Different Queries in Focus

Figure 3-1: Five different queries in focus and their location in MS Access Graphical User Interface (GUI)
________________________________________

MS Access is not only a database but an excellent data analysis tool. In this chapter, our sole focus would be on one of very important objects in MS Access i.e. Queries. Once you are in Query Design View as shown in Figure 3-2 by clicking “Create” command and then “Query Design” on the ribbon, you can see the all these query type in the ribbon as shown in Figure 3-1.

Figure 3-2: Query Design View in ribbon under “Create” command.
________________________________________

MS Access then asks for tables/other existing queries required for this new query, click cancel for now. We can always add tables later. Now let’s talk about each of these different types of queries briefly one by one to comprehend the bigger picture of the whole story. After this, we will be talking about each of these types in much more detail.

Select Query:

We use Select Query to extract data from table or tables linked together. Like it’s mentioned before while developing any of the queries, MS Access treats all the tables as if they are MS Access tables. There is no discrimination between Local and Linked tables.

The time you run the query, the resultant data will be displayed. The displayed data will only be in the RAM or in other words the time you close the Run View of the query, the data will get vanished. To extract the data again, you have to run the query again. For query that takes a long time to execute, we usually prefer to have a Make-Table query rather than a Select query. We will discuss about the Make-Table Query in the next article.

Make-Table Query:

Make-Table query is designed exactly the same manner we design the Select query. The only difference is the Run view. In the Run View of a Make-Table query the resultant data will be saved in the form of MS Access Local Table.

If your Make-Table Query is comprised of Local as well as Oracle Linked tables, once you run it, all the data will get transferred from Oracle to MS Access in the form of local table. We will learn in detail in step by step manner, how to design and deal with Make-Table Queries.

Update Query:

As the name states the Update query is used to update already existing records in the tables. No matter the tables are MS Access Local tables or MS Access Linked Tables (Oracle tables visible via ODBC in MS Access), update operation will get performed upon execution of such query.

We used many times Update Queries to perform bulk updates on Oracle tables based on some logic. The process that used to take months to attain the across the board salary increase now can be done within minutes without even writing any SQL code. That’s the power of MS Access.

Append Query:

Append queries are used to add new records in the table at the end. Whenever you would like to append new records or in other words whenever you would like to insert new records at the end of the table use the append query. This query can be performed on MS Access local as well as linked tables.

Delete Query:

The purpose of the delete query is to delete any number of records from table, no matter whether it’s local or linked table. Records are usually deleted based on some logic or criteria. Note that you should be very careful in running such types of queries as if you don’t mention any criteria; you may end up loosing all the records in your table.

Now we will be looking at each of these queries one by one starting with Select query in great detail in terms of design and everything.

Chapter 3: MS Access Queries

Page #: 1 | 2 | 3 | 4 | 5 | 6

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2007 Step By Step - Asim Abbasi