understanding istn

• Queries


  One of the powerful features of a database is ability to create queries based on table joins
• Queries


  • Using a query makes it easier to view, add, delete, or change data in your Access database
  • Access offers 2 easy methods to create queries: Query By Wizard feature and Query By Design
• Query by Design


  1. Click Query Design

  2. Add tables (where info. has to be joined) from two tables

  3. Specify the fields to be displayed by dragging them into lower pane or selecting from the drop‐down list

  4. Select Show if the field needs to be displayed

  5. Specify criteria ‐> based on this records will be displayed

  6. Click (!) Run to execute the query
• Query by Wizard


  1. Click Query Wizard to quickly create queries on various templates

  2. Select Simple Query

  3. Select table on which query is based

  4. Select appropriate field names and click right arrow to add the fields in query

  5. Click Finish to execute the query
• Join


  Helps a query return only the records from each table you want to see, based on how those tables are related to other tables in the query
• Relationship


  Represented in a query by a join
• A query can get its data from one or more tables, from existing queries, or from a combination of the two
• Criteria


  Conditions that determine which records are included in the query result
• Criteria


  • > 25 and < 50
  • DateDiff ("yyyy", [BirthDate], Date()) > 30
  • Is Null
• Criteria


  • City = "Chicago"
  • BirthDate < DateAdd (" yyyy ", ‐40, Date())
• If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid
• Criteria

  • City = "Chicago" OR BirthDate < DateAdd (" yyyy ", ‐40, Date())
• Totals Query


  Summarizes data by grouping and calculating totals for fields
• Creating a Totals Query


  1. Open query in Design View

  2. Click Totals in the Show/Hide group

  3. Select an aggregate function (e.g. Sum) for each field in the Totals row

  4. Run the query to display the totals
• You can create a form based on a query
• You can review data from select fields or from multiple related tables simultaneously using queries
• YouTube Videos


  • Access 2016 Queries: Grouping Records & Totals
  • Access: Designing a Simple Query
  • Access: Multi‐table Queries (Part 1)
  • Access: Multi‐Table Queries (Part 2)
• Field


  A column in a table that represents a specific piece of data. Each field has a unique name and data type, which determines the type of data that can be stored in that field.
• Table


  A database object that stores data in an organized manner, with rows and columns. Each column represents a field, and each row represents a record.
• Record


  A row in a table that contains a unique combination of field values. Records are used to store individual data points in a table.