Query are play the important role in database and it is often call by user in a form. The use can search , grab and accumulator the data from the different tables. The Microsoft Access allow to the user and developer to create the many types of database queries. But we will cover the essential types of queries and these are follow.
Four Types OF Database Queries
Table of Contents
- Select Query
- Action Query
- Parameter Query
- Aggregate Query
The select query is the simple and most use in the database the database developer are mostly use this types of query. It is use to collect the data from different tables and queries. The developer create all the reports by using it. By using the select you can define the criteria or filter the specific field. It allow to user to merge the two or more table column in a single column in selecting query.
Types of Database Queries | Action Query:
Action query is use to perform the specific action in the database . It depend what define in the query i.e delete the single record or multiple record and delete the entire table, update a record in the tables or entire tables.
In a good database management system (DBMS) action query is very popular because it allow to manage many record in table at once and it save the time of user instead of only single record like in select query.
Following are the four kinds of Action Query:
- Append Query: It is use to adding multiple record one table to an other table. You must create table first. But this method is not sufficient to adding data from one database table to another database table you must use the copy paste method to add data from one table to another table.
Now we clear the append query by using the student database . In this database we have two table one is student profile and second is class table. Now we append the data of student table . We need those student list which are enrolled in last year , So we use the criteria in select query.
First we define the criteria in query data enrolled . <=DateAdd(“yyyy”,-1,Date())
This criteria display only those student which are enrolled in 2019.
Now we have required student list which we define criteria, In this table only show the two record which are enrolled in last year from now date, And save it .
- Delete Query: It is the set of instruction to delete the record from tables. by using it the table data will deleted permanently from the table. Once a delete query run the will never recover.
How To Use Delete Query:
Following are the simple step to create a delete query
Step 1: Click on create tab on top of ribbon.
Step 02: Then click on create button and choose table which data you want to delete.
Step 03: Select the fields of table and define the criteria in query design in last row.
Step 04 : Click on delete button on top ribbon the system will ask you are “are you sure you want to delete the selected record “?.
Step 05: Press the yes button it will delete the select record which you define in criteria. And close the query window.
- Make Table Query: It is create the table from other table according to your criteria define in query. For example you have a student profile table in your database . And you want to sea the separate data of female students from main table. So will define criteria by gender in query female and click on make table query in the top ribbon. Save the name of female_student . You will sea this table in left panel.
Step 01 : Open your existing database in ms access .
Step 02 : Select the table which you want to make table query in query design.
Following is the sample table.
Step 03 : Choose the column of your table in query design and define the criteria in field.
Step 04 : Click on make table query on the top ribbon. It will ask you the name of your new table so enter the name of table and run the query.
The new table successfully created.
Update Query: It allow to add one or more field in your existing table. For example you have a product table with following filed.
1. Sr.no 2. product_name 3. unit_price and 4. brand
You want to update the price of some brand then you will create the update query.
Types of Database Queries | How To Create Update Query?
Step 01 : Select your table which you want to update in query design.
Step 02 : Define the criteria in category or brand field . Add formula in new column with new price name and run the query.
Types of Database Queries | Parameter Query:
In this types of database queries a parameter query play a important role to the end user. It is special design for the end user to perform the specific task from the tables. For example in a student_profile table the end user want to apply the filter by different date and year.
So the developer define the parameter in query design for the end user to get the desired reports from the tables which he want to do. In this query I select the student profile table and define the criteria in date enrolled for the user. It will ask the user input the specific period which he want to get.
Types of Database Queries | Aggregate Query
Aggregate query perform a special function in types of database queries . It essential important in designing a good query for the end user. It is a combination of selection, parameters and criteria. By using types of query you can get the statics report from your database tables such as average, mean deviation, slandered , deviation, sum , min, max, first, last , grouped by, expression and where.
Following pic show the example of Aggregate Query.