Introduction of MS Access Query:
Table of Contents
A database Access Query is a summarize the data to get the good report from one table or different tables. Its main aim to view the report by different angle. The can access the daily, weekly, monthly and annual purchase, sales and inventory report by using the query.
In this lesson you will learn how to create simple query in MS Access Query with one table and one to many table. A good relationship accesses the data to retrieve and analyze the data from tables. Before creating a query you must have a table or create a table in your database.
In a table data you can’t make a filter and also can’t apply multiple criteria and apply condition, to get the information from tables but in access query you can get the information from multiple tables applying multiple conditions and criteria which you want.
For example you have two table in your database one is Customer Profile the 2nd is Customer Order . In customer table customer id is primary key and in Customer Order have FK with customer id. In query you don’t need all customer information .
So you can make query with custom criteria to get the some specific field. i.e customer name, address, city and mobile number with pending order. Here you can select only those field which you want.
There are two method to create a ms access query
- Query Wizard
- Query Design
In a Query wizard you can create a query step by step select the tables and its fields its called query wizard method following is the example .
How to Create Access Query by Wizard:
Step 1: open your existing database click on create tab and then select the query wizard method .
Step 2: In 2nd step the query dialog box will open in which the all types of query listed . Just you select first Simple Query Wizard and then click Ok.
Step 3: The simple query wizard will open in this dialog in left side you must select the table or query which you want to add in query.
Step 4: After select the table in wizard the all field of that table are listed below you can select the desired field and add them in query list in the right side by clicking the add button. In this step you can select the single field and you can select the all fields at once by clicking double arrow button in middle of the lists.
Step 5: In my database i have choose the tbl_profile its consist on 7 filed are follow:
- profileid
- first-name
- last_name
- gender
- dob
- country
- city
And in this step i have choose only four field in query. Then click on next button.
Step 6: After selecting the table fields the final dialog box will open in this step in the top filed you should enter the name query and then you open the query view information or you can modify it by choosing the modify query in design. And click on finish.
How to Create Access Query In Design?
The second method is to create query by using design option in this just you can select the table and modify it in design. I have a table with the name of tbl-profile we using in query.
Step 1: Open your database and click on create tab on the top and select Query Design in option bar. The show table dialog box will open just you select the table which you want to add in query and then click add.
Step 2: The query design mode will open with table. The query design is consist two part one is tables on the top and second is query design with tables field. In the first parts all table are show with there relationship you can select the field by double clicking on table table fields or you can select the fields in bottom window.
Now table fields are added in query design and its ready to use . To add the table fields data you do it by direct select the fields from query section the following figure show how to select direct fields in query section.
The access query design consist on five major function . These function are left side of the design we discus in detail now these are follow.
- Filed: When you add the table in query design the all fields are listed in field row you can select the relevant field by using this row.
- Table: The second row is table hare the all table are added in this row first you select the table then you can select the above fields.
- Sort: This function allow to recording sorting . In this row there are tow option first is Ascending and second is Descending. by using this function you ordering the column which you want.
- Show: In this row you see the check boxes in each column its mean a field you want’t to in query then you can use this it will hide the column.
- Criteria : Criteria is use to define a filter by using the criteria you can apply single or multiple filter in different column which you want.
Add Column In Ms Access Query
Step 01: To add a additional column in ms access query in design click on add column button on top ribbon and be sure you cursor is stay in a column. In this query I added a column after the last name.
Step 02: After adding the column after the last name enter the name of the new column i.e Full Name and we will merge the first two column first name + last name .
Step 03: After adding the formula in new column click on run button on the top left ribbon menu you will see the following result.
Step 04: Save the query by click on save button on top left corner and enter name of you query click on Ok. Now your query is ready see the following pics.
Criteria Query Example:
A access query criteria is a expression of table fields data. For example in employee profile table you need only those employee which belong to a specific city. So you apply the name of city in criteria row and get the list of specific city.
