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
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.
Step 1: Creating Select Queries (Getting into Query Design View)
Figure 3-3: 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.
1. The label 1 in the Figure 3-4 points to table tab.
2. 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-4: Tables tab contains the list of all the tables inside your
database whereas Queries tab contains the list of all queries.
________________________________________
Step 3: Create Joins & 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-5 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-5.
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-5.
4. Also make sure you have selected a ‘Select Query’ option.
Figure 3-5: Design View of Select query.
________________________________________
If you double click the line joining two tables as shown in Figure 3-5 with label 1, the Join Properties window will pop up with first option selected already as shown in Figure 3-6. 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.
Figure 3-6: Select the type of Join using Join Properties window.
________________________________________
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 selected option as shown in the Figure 3-7.
Figure 3-7: Contrary to Equi-Join, Left/Right Joins have an arrow sign.
________________________________________
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 Figure 3-7, 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 in HR_EMPLOYEES
but against all those particular records you will see DEPARTMENT_NAME
column (of HR_DEPARTMENTS table) containing NULL values 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 as
shown in Figure 3-8. Once you are in the run view of the query you can
always go back to the Design View for further modifications.
Figure 3-8: Run View of the query
________________________________________
You can also go from Run to Design View by right-clicking on the query name and then clicking on “Design View” command as shown in Figure 3-9. You can achieve the same goal via “View” command on the ribbon and then selecting “Design View”.
Figure 3-9: Right click on the query name to access “Design View”
________________________________________
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: