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
Select query is used to retrieve the information from tables or already
existing queries. In this section of the book we will learn how to
design the Select queries. As shown in the picture above, this is the
icon you will see next to your query name in MS Access GUI (Graphical
User Interface). Each type of query has its own icon. So if you have
bunch of queries visible in MS Access Query object, you can easily
identify the type of the query, just by looking at its icon.
Creating Select 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.
Most of the time we create queries in Design View and these 4 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.
1. The label one in the slide above 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 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 the
screenshot above 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.
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 ‘Select Query’ option from
the drop down menu by clicking the drop down menu pointed by label 4 in
the screenshot.
If you double click the line joining two tables, the Join Properties
window will pop up with first option selected already.
This
first option is for Equi-Join. Once you have equi-join between table(s),
the output of the query will contain only those records where joined
columns have same values e.g. if there is a department id 33 in the
HR_EMPLOYEES table and there is no corresponding record in the
HR_DEPARTMENTS table pertaining to the same department, in the output of
the equi-join query no record will appear having department id 33.
If you select the second option or third option on the Join Properties window then you will see an arrow either pointing to one table or the other depending upon your option selection as shown in the picture below.
In
Oracle world such type of joins are called Outer Joins whereas in MS
Access these are named as Left/Right Joins. The importance lies not in
names but in the concept. As depicted in the picture above, the output
of such query will be all the records of the left-hand side table and
only those records of the right-hand table where there is a join (or
same values) e.g. if there is department id 33 in the HR_EMPLOYEES table
which is not defined in the HR_DEPARTMENTS table even then you would be
able to see the record pertaining to department id 33 but against that
particular record you will see DEPARTMENT_NAME column (of HR_DEPARTMENTS
table) containing NULL value provided you have selected it as the output
field of the query.
So the bottom-line of Outer-Join (Left/Right Join) query is,
You will see all the records from the table having no arrow but only
those records from the ‘arrow’ table where there is a join. This
statement first should be understood and then engraved on the mind.
Step 4: Run the Query
Once you run your query after completing the design,
you would be able to see the output of the data retrieved as a result of
your query. Once you are in the run view of the query you can always go
back to the Design View for further modifications.
1. As shown in the screenshot above, this is the icon for Design
View. Clicking it will take you back in the Design View.
2. Label two points to the place where you can see the total
number of records returned as a result of the query.
You can also go from Run to Design View by clicking View and then Design
View from the Menu bar of MS Access GUI.
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: