Scribd Logo
Upload

Welcome to Scribd!

  • 32 views

    Access Session 4: Using Queries To Select Data

    Uploaded by

    Shaira Sarip Mamantar-Balindong
    This document discusses how to use queries in Microsoft Access to select, sort, filter, and summarize data. It describes the different types of queries, including select, action, parameter, and crosstab queries. It provides instructions on building queries in the design view, including how to add fields, set criteria and sorting, and perform calculations. Crosstab queries are introduced as a way to summarize data into categories similar to a pivot table.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd

    Access Session 4: Using Queries To Select Data

    Uploaded by

    Shaira Sarip Mamantar-Balindong
    32 views19 pages
    This document discusses how to use queries in Microsoft Access to select, sort, filter, and summarize data. It describes the different types of queries, including select, action, parameter, and crosstab queries. It provides instructions on building queries in the design view, including how to add fields, set criteria and sorting, and perform calculations. Crosstab queries are introduced as a way to summarize data into categories similar to a pivot table.

    Original Title

    4 Session4 Queries

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses how to use queries in Microsoft Access to select, sort, filter, and summarize data. It describes the different types of queries, including select, action, parameter, and crosstab queries. It provides instructions on building queries in the design view, including how to add fields, set criteria and sorting, and perform calculations. Crosstab queries are introduced as a way to summarize data into categories similar to a pivot table.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    32 views19 pages

    Access Session 4: Using Queries To Select Data

    Uploaded by

    Shaira Sarip Mamantar-Balindong
    This document discusses how to use queries in Microsoft Access to select, sort, filter, and summarize data. It describes the different types of queries, including select, action, parameter, and crosstab queries. It provides instructions on building queries in the design view, including how to add fields, set criteria and sorting, and perform calculations. Crosstab queries are introduced as a way to summarize data into categories similar to a pivot table.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    of 19

    access session 4

    Using Queries to Select Data

    Database Operation in Table Datasheet view Retrieve, sort and group data Limit results Evaluate expressions

    Table Datasheet view Operations (Sort and Filter)

    how to.
    Records menu Table Datasheet toolbar

    Filter by Selection Excel concept of AutoFilter Filter by Form Excel concept of Custom Filter

    Comparison and Logical Operators


    Comparison = > >=
    Equals Greater than Greater than or equal

    Logical Or And
    Meets any one condition Meets all conditions Negates arguments

    < <= <>


    Is

    Less Than Greater than or equal Not equal


    SQL equivalent to equal sign

    Not

    Falls into two extremes and Between includes the Like String matches a pattern extremes

    Query Preliminaries
    Queries are used to sort, filter, add, delete, and modify data in MS Access databases use queries to request data for forms, reports, and controls when an object relies on specific data rather than on all the data in the underlying table.

    Query Types

    1/2

    Select: retrieve data that meets specific conditions, group records for viewing summary data, and display calculations performed on data fields Action: modify existing data in some way, used to delete, update, and append data or to create a new table.

    Query Types

    2/2

    Parameter: prompt you for criteria before running the actual query Crosstab: summarize data and then group the summarized values into categories SQL: include union, pass-through and datadefinition queries, require specific SQL commands, that you must compose in SQL view

    the query environment

    query Design view: identify the tables and queries that you want to include in your query. and create & view queries

    field list: upper part window displays a field list for each table or query design grid: lower part window, define the querys fields and accept expressions as criteria for limiting the results of the query

    SQL view: translated query design view into Jet Structured Query Language.

    Common SQL keywords


    AS: creates a clause that specifies an expression or value and the field name associated with it DISTINCTROW: exclude duplicate records from the query FROM: Create a clause specifying the table or query from which fields are taken for the query ORDER BY: Crates a clause containing a list of fields to be included in the query

    SELECT: creates a clause containing a list of fields to be includes in the query UNION: Combines two sets of records into a single set WHERE: Creates a clause with a condition (or set of conditions) for filtering query records.

    The Simple Query wizard Summarizing Data using the Wizard Retrieving Data using the Design view
    1. 2.

    click new, then add fields from the Show table window execute the query using the Run button from the Query Design toolbar

    5 methods for adding fields to the design grid


    double-click the field in the field list drag the field to a column double-click or drag the * in the field list field drop-down list type the field name in a columns Field cell

    other design grid components


    Sorting Records: using the Sort cell Computing totals for group records: using Group by Drop-down list (view menu, totals) Limiting Data: using the Criteria or Or cell

    Crosstab

    Crosstab query is a query that groups summarized data by categories, a more compact way to group and summaries record Crosstab vs PivotTable

    both summarize data using aggregate functions PivotTables are more powerful because they can display the detailed data behind those subtotals and grand totals without affecting the groups

    Crosstab vs PivotTable (cont.)


    column headings: PivotTables (multiple) while Crosstab (one) PivotTables also include crossfoot totals, Crosstab none PivotTables: quicker to produce and more flexible that crosstab queries

    To sum it up

    datasheet sorting & filtering vs. queries


    Both can be manipulated using the design grid area Queries: saved objects

    Manipulate the query design view, design grid area

    You might also like