Computer Tutorial - Access Queries Object

Custom Search

Site menu

Map

View Larger Map

Calendar

Site friends

Create a free website

Statistics

Total online: 1

Guests: 1

Users: 0

Ads

Access Queries Object

Microsoft Access - Queries

From this hour you will be able to learn about below topic –

About Queries and Types of Queries
Example of Select Query
Example of Parameter Query
Example of Crosstab Query
Example of Action Query
How to Change Currency Symbol
Example of SQL Query
How to Create Table (Relation) with SQL Command
How to Join Table with SQL Command

About Queries

Top

Queries is used to view, change, and analyze data in different ways. It is also used as a source of records for forms, reports, and data access pages. There are several types of queries in Microsoft Access.

Select queries
Parameter queries
Crosstab queries
Action queries

Update queries
Delete queries
Append queries
Make-Table queries

SQL queries

Select Queries: A select query is the most common type of query. It retrieves data from one or more tables and displays the results in a datasheet where you can update the records (with some restrictions). You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

Parameter Queries: A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Access can then retrieve all records that fall between those two dates.
Parameter queries are also handy when used as the basis for forms, reports, and data access pages. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Access prints the appropriate report.
Crosstab Queries: You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information— one down the left side of the datasheet and another across the top.

Action Queries: An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries:

Delete Queries: A delete query deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.
Update Queries: An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.
Append Queries: An append query adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you'd like to append it to your Customers table.
Make-Table Queries A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases or a history table that contains old records.

SQL Queries: An SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Access.
When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.
Some SQL queries, called SQL-specific queries, can't be created in the design grid. For pass-through, data-definition, and union queries, you must create the SQL statements directly in SQL view. For subqueries, you enter the SQL in the Field row or the Criteria row of the query design grid.

Example of Select Query:

Top

To Create Select Query with Simple Query Wizard: (1)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Simple Query Wizard > Click Ok button
Select Table: NewEmployee (or your choice) from Tables/Queries list > Select necessary fields from Available Fields: list to Selected Fields: list like below (or your choice) – > Click Next button
Click Next button
Type a query title and click Finish button
Type data into query table and close the query table
Open to main NewEmployee table and look new data is entered in the NewEmployee table

To Create Select Query with Simple Query Wizard: (2)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Simple Query Wizard > Click Ok button
Select Table: Authors (or your choice) from Tables/Queries list > Select AuthorName field from Available Fields: list to Selected Fields: list like below (or your choice) –
Select Table: Books (or your choice) from Tables/Queries list > Select BookName, Version, Price, Qty fields from Available Fields: list to Selected Fields: list like below (or your choice) –
> Click Next button
Click Next button
Type a query title and click Finish button

To Create Select Query with Design View: (1)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Select Authors table (or your choose) and click Add button
Close 'Show Table' dialog box
Then drag the fields from the Books field list (or your choice) to query design grid like below –
On the Query menu, click Run to run query
You should view the query like below –
On the View menu, click Design View to return 'design view' window
On the File menu, click Save if you want to save the query
Type a Query Name: BooksQuery1 or your choice > Click Ok button and close the 'design view window'

To Create Select Query with Design View:(2)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Add Authors and Books table
Close 'Show Table' dialog box
Then drag the fields from the Authors & Books field list (or your choice) to query design grid like below –
On the Query menu, click Run to run query
You should view the query –
On the View menu, click Design View to return 'design view' window
To use criteria enter a book name in Criteria: cell of BookName column, like below –
On the Query menu, click Run to run query
You should view the query –
On the View menu, click Design View to return 'design view' window
To use criteria enter two book name into Criteria: cell of BookName column, like below –
On the Query menu, click Run to run query
You should view the query –
On the View menu, click Design View to return 'design view' window
To use criteria enter an author name into Criteria: cell of AuthorName column and two book name into Criteria: cell of BookName column, like below –
On the Query menu, click Run to run queryYou should view the query –
On the View menu, click Design View to return 'design view' window
To use criteria enter a price into Criteria: cell of Price column, like below –
On the Query menu, click Run to run query
You should view the query –
On the View menu, click Design View to return 'design view' window
To use criteria set Criteria: like below –

[Note: For numeric field we can use, the symbols/sign (= for equal to, < for gather than, > for less than, <= for gather than or equal to, >= for less than or equal to, <= and >= for between, >= or <= for not between, <> for not equal to]

On the Query menu, click Run to run query
You should view the query –
On the View menu, click Design View to return 'design view' window
On the File menu, click Save if you want to save the query
Type a Query Name: BooksQuery2 or your choice > Click Ok button and close the design view

Example of Parameter Query:

Top

To Create Parameter Query: (One Parameter)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > click Ok button
Select Books table (or your choose) > Click Add button > Close 'Show Table' dialog box
Then drag the fields from the Books field list (or your choice) to query design grid
In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets, as – type the expression [BookName:] into Criteria: cell of BookName column
On the Query menu, click Parameters…
Select Data Type: Text
> Click Ok button
On the Query menu, click Run
Then type a value (ms word) for the parameter
> Click Ok button
You should view the query
On the View menu, click Design View to return 'design view' window
On the File menu, click Save if you want to save the query
Type a Query Name: BookNameParameter (or your choice) > Click Ok button > Close the 'design view' window
Again view the query just open BookNameParameter and type a value for the parameter > Click Ok button
You should view the query

To Create Parameter Query: (One Parameter)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > click Ok button
Select Books table (or your choose)and click Add button
Close 'Show Table' dialog box
Then drag the fields from the Books field list (or your choice) to query design grid
In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets, as – type the expression >[BookName:] into Criteria: cell of Price column
On the Query menu, click Parameters…
Select Data Type: Currency
> Click Ok button
On the File menu, click Save if you want to save the query
Type a Query Name: BookPriceParameter or your choice
Click Ok button and close the 'design view' window
To view the query just open BookPriceParameter and type a value for the parameter > Click Ok button
You should view the query

To Create Parameter Query: (Two or More Parameters)

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > click Ok button
Select a table and click Add button
Close 'Show Table' dialog box
Then drag the fields from the Books field list (or your choice) to query design grid
In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets, as – type the expression Between [Less Book Price:] And [More Book Price:] into Criteria: cell of Price column
On the Query menu, click Parameters…
Select Data Type: Currency
Click Ok button
On the File menu, click Save if you want to save the query
Type a Query Name: BookPriceTwoParameter or your choice
Click Ok button and close the 'design view' window
To view the query just open BookPriceTwoParameter and type a value for the parameter > Click Ok button and type a value for the second parameter
> Click Ok button
You should view the query

Example of Crosstab Query:

Top

To Create Crosstab Query with Wizard:

Select Queries under Objects
Click New on the 'database window toolbar'
Select Crosstab Query Wizard > Click Ok button
Select Table: Books or your choice > Click Next button
Add BookName and Qty fields from Available Fields: to Selected Fields: list (or your choice) > Click Next button
Select Version for column headings (or your choice) > Click Next button
Select Price for calculated fields and Sum for functions > Click Next button
Type a name of the query, like Book_Crosstab (or your choice) > Click Finish button
You should view the query

Example of Action Query:

Top

To Create Update Query:

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Select a Books table (or your choice) > Click Add button > Close 'Show Table' dialog box
On the Query menu, click Update Query
Then drag the BookName field/fields (or your choice) from the field list to query design grid
Set Update To: "MS Word" (or your choice) > Set Criteria: "ms word" (or your choice)
On the Query menu, click Run
Click Yes button
Save the query with a unique name and close the 'design view' window
Open main Books table and view the change (all 'ms word' values should change to 'MS Word')

To Create Update Query:

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Select a Employee table (or your choice) > Click Add button > Close 'Show Table' dialog box
On the Query menu, click Update Query
Then drag the EmpCity field/fields (or your choice) from the field list to query design grid
Set Update To: "Mymensingh" (or your choice) > Set Criteria: "Dhaka" (or your choice)
On the Query menu, click Run
Click Yes button
Save the query with a unique name and close the design view
Open main Employee table and view the change (all 'Dhaka' values should change to 'Mymensingh')

To Create Delete Query:

Select Queries under Objects
Click New from on the 'database window toolbar'
Select Design View > Click Ok button
Select a Employee table (or your choice) > Click Add button > Close 'Show Table' dialog box
On the Query menu, click Update Query
Then drag the EmpCity field/fields (or your choice) from the field list to query design grid
Set Criteria: "Dhaka" (or your choice)
On the Query menu, click Run
Click Yes button
Save the query with a unique name and close the design view
Open main Employee table and view the change (all 'Dhaka' values should have deleted)

To Create Append Query:

If we set AutoNumber data type for a field, the number will start with 1 as default, but if we want to change the start number, like 1001 we have to create append query, so follow the below process –

Create two tables, like below –

Original Table (Products)

Field Name – ProductID, Data Type – AutoNumber (Primary Key)
[Note: If the AutoNumber field is not Primary Key field, set Field Properties – Field Size: Long Integer & Indexed: Yes (No Duplicates)]
Field Name – ProductName, Data Type – Text
Field Name – Price, Data Type – Currency
Field Name – Qty, Data Type – Number

Demo Table (ProductsDemo)

Field Name – ProductID, Data Type – Number (No Primary Key)

Open ProductsDemo table and enter a value in the Number (BooksID) field that is one (1) less than the starting value you want for the AutoNumber field (BooksID). For example, if you want the AutoNumber field to start at 10001, enter 1000 in the Number field.
Then –

Create Append Query:

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Select ProductsDemo table from 'Show Table' box
Click Add button > Close 'Show Table' box
Drag ProductID field from field list to query design grid Field:
On the Query menu, click Append Query…
Select TableName: Books & Current Database > Click Ok button
Type Criteria: 1000
On the Query menu, click Run or click (Run) from 'Standard toolbar'
Click Yes button
Close 'query design' window without save
Select Tables under Objects
Delete ProductsDemo table
Open Products table and delete first record (Right click on row heading > Delete Record > Yes)
Then enter value into fields

To Change Currency Symbol:

Top

Open Control Panel
Open Regional and Language Options
Select Regional Options Tab
Click Customize …
Select Currency tab
Remove Currency symbol $
Type BDT or your choice
Click Ok button
Click Ok button
Select Tables under Objects
Select Books table or your choice
Click Design on the 'database window toolbar'
Select Currency field
Change Format from Field Properties
Close 'design view' window with Yes
Open Books table and look the change

To Query with SQL Statement:

Top

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Close 'Show Table' dialog box
On the View menu, click SQL View
Then type a SQL statement, like below –
select * from Employee (here Employee is table name)
select EmpID, EmpName, EmpTitle from Employee
select EmpName, EmpTitle, EmpSalary, EmpPhone from Employee
select * from Employee where EmpCity="Dhaka"
select * from Employee where EmpCity="Dhaka" or EmpCity="Mymensingh"
select * from Employee where EmpCity="Dhaka" or EmpCity="Mymensingh" or EmpCity="Valuka"
select * from Employee where EmpCity="Dhaka" and EmpTitle="Manager"
select * from Employee where EmpCity="Valuka" and EmpTitle="Officer"
select * from Employee where not EmpCity="Dhaka"
select * from Employee where EmpSalary=20000
select * from Employee where EmpSalary<>20000
select * from Employee where EmpSalary>20000
select * from Employee where EmpSalary>=20000
select * from Employee where EmpSalary<20000
select * from Employee where EmpSalary>=20000
select * from Employee where EmpSalary>=20000 and EmpSalary<=30000
select * from Employee where EmpSalary<=20000 or EmpSalary>=30000
On the Query menu, click Run
On the View menu, click SQL View to return in the SQL statement
Then type a above SQL statement
On the Query menu, click Run
Save the query if you want

To Create Table (Relation) with SQL Command:

Top

Step – 1

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Close 'Show Table' dialog box
On the View menu, click SQL View
Type below statements –
CREATE TABLE Departments
(DepID CHAR(10),
DepName CHAR(25),
CONSTRAINT depid_pk PRIMARY KEY(DepID));
On the Query menu, click Run
Save the query as DepartmentsTableCode

Step – 2

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Close 'Show Table' dialog box
On the View menu, click SQL View
Type below statements –
CREATE TABLE Teachers
(TeaID CHAR(10),
TeaName CHAR(25),
Title CHAR(20),
Address CHAR(35),
DepID CHAR(10),
CONSTRAINT teaid_pk PRIMARY KEY(TeaID),
CONSTRAINT depid_fk FOREIGN KEY(DepID) REFERENCES Departments(DepID));
On the Query menu, click Run
Save the query as TeachersTableCode

Step – 3

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Close 'Show Table' dialog box
On the View menu, click SQL View
Type below statements –
CREATE TABLE Students
(StuID CHAR(10),
StuName CHAR(25),
Semester CHAR(10),
Address CHAR(35),
TeaID CHAR(10),
CONSTRAINT stuid_pk PRIMARY KEY(StuID),
CONSTRAINT teaid_fk FOREIGN KEY(TeaID) REFERENCES Teachers(TeaID));
On the Query menu, click Run
Save the query as StudentsTableCode

To Join Table with SQL Command:

Top

Select Queries under Objects
Click New on the 'database window toolbar'
Select Design View > Click Ok button
Close 'Show Table' dialog box
On the View menu, click SQL View
Type below statements –
SELECT Departments.DepName, Teachers.TeaName, Teachers.Title, Students.StuName, Students.Semester
FROM (Departments INNER JOIN Teachers ON Departments.DepID = Teachers.DepID) INNER JOIN Students ON Teachers.TeaID = Students.TeaID;
On the Query menu, click Run
Save the query

********************