Access Querying Getting Started

1
Access Querying Getting Started

• By
• Bob Larson

2
Query

• (n.) A request for information from a database.
  There are three general methods for posing
  queries
• Choosing parameters from a menu
• In this method, the database system presents a
  list of parameters from which you can choose.
  This is perhaps the easiest way to pose a query
  because the menus guide you, but it is also the
  least flexible.
• Query by example (QBE)
• In this method, the system presents a blank
  record and lets you specify the fields and values
  that define the query.
• Query language
• Many database systems require you to make
  requests for information in the form of a
  stylized query that must be written in a special
  query language. This is the most complex method
  because it forces you to learn a specialized
  language, but it is also the most powerful.
• (v.) To make a request for information from a
  database
• Source Webopedia

3
Objectives

• Create and run queries
• Include fields in the design grid
• Sort data in queries
• Save a query and use the saved query

4
Starting a New Query (2007)

• From Create Tab
• Click on Query Design in the Other section

5
Starting a New Query (2003 older)

• From Query Tab
• Click on Create query in design view
• Click on New button
• On New Query window choose Design View
• From Tables or Query Tab
• Select table or query to base new query on
• Use New Objects dropdown and select Query
• On New Query window choose Design View

6
Adding Tables or Queries

• Use tab to choose Tables, Queries or Both
• Select and click Add or just double-click
• Click Close when done
• Cant move, resize or delete source files until
  Show Table is closed

7
Query-By-Example (QBE) View
Changing the QBE screen allocation
Changing the source box height
8
Selecting Fields

• Click on Field row in QBE and use drop-down
• Double click on field in field list
• Drag field from field list to column
• Can select multiple fields
• Click and Shift-Click selects everything between
• Click and Ctrl-Click selects non-consecutive
  fields

Drag n Drop allows you to insert fields in
your grid. The column you drop on and all to the
right will move right.
9
Selecting All Fields

• Double-click on asterisk
• Cant set sort or criteria
• Double-click on title bar at top of field list
• Selects all fields in the list
• Drag one of the fields to desired first column

Query Run or ! results are identical
10
Sorting Fields

• Columns sort left to right
• Click on Sort row in QBE grid
• Use drop-down to make choice, or
• Type a or d
• Double-click on Sort row in QBE grid
• Cycles through options

Probably not what we had in mind.
11
Sorting Fields Hidden Field

• Assuming we cant change the column order
• Drag another copy of Lname anywhere left of Fname
• Set sort on the new Lname column
• Remove check from Show row
• (optional) remove original Lname column sort

12
Run (Execute) Queries (2007)

• Click on Run ! Button (in Design View)

13
Run (Execute) Queries (2003 older)

• Click on Run ! Button
• Choose Query Run from menu
• Datasheet View button (circled) is bad habit
• First button on tool bar (swaps with Design View)
• Doesnt work with all types of queries

14
Query View Button (2007)

• Combo button left end of tool bar
• Click on icon to go direct to that choice
• Use drop down to use list (down-arrow)
• Buttons in lower-right do the same thing

15
Query View Button (2003 older)

• Combo button left end of tool bar
• Click on icon to go direct to that choice
• Use drop down to use list (same as View menu)
• SQL View shows underlying SQL code (below in red)
• This is what gets saved when query is saved

16
Removing Columns from QBE

• To remove one column
• To select, click on bar right above column note
  arrow
• Once selected press the Delete key
• To remove all columns
• Choose Edit Clear Grid from menu

17
Closing a Query

• Click on Close button (X in upper right corner),
  or
• Choose File Close from menu (2003 or older)
• Answer Yes on Microsoft Office Access pop-up to
  save
• Starting name with qry helps when Queries and
  Tables are displayed together in list (like merge
  print in Word)
• Start name with your initials when multiple
  active users
• Your queries will all sort together

18
Opening an Existing Query

• Display the Queries (objects)
• Double-click on the query name
• Opens in executed view

19
Opening Query in Design View

• Handy for Action queries you dont want to Run

20
Fin