Site menu
Search
Map

View Larger Map
Calendar
«  November 2017  »
SuMoTuWeThFrSa
   1234
567891011
12131415161718
19202122232425
2627282930
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

    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
      1. Update queries
      2. Delete queries
      3. Append queries
      4. 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:

    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:

    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:

    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:

    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:
    • 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:
    • 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:

    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:
    • 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
    ********************

    Copyright MyCorp © 2017