CGS2545 Database Concepts

1
CGS2545 Database Concepts

• Access Introduction to Designing
  Queries(select queries)

2
Queries Searching Data
Poorly designed queries will produce inaccurate
results. This, of course, will lead to inaccurate
decisions.

• Queries searches of database data.
• The screen shots here show common techniques for
  designing Access queries.
• Pay particular attention to the calculated query
  and any queries using functions (average, count,
  sum, etc.).
• The database used in this presentation is the
  matchmaker database. You can find it at
  http//www.cs.ucf.edu/phillips/downloads/.
• Youll also find a word document that contains a
  list of search requests based on this database.

3
The Database Window

• Custom queries can be created in design view.
• The tables you want to search are in the top
  portion of the screen.
• The design grid in the lower portion contains the
  criteria to search for.
• Clicking the save button will allow you to save
  your individual queries so they can be run again.

4
Displaying All Fields

• Double-clicking the asterisk at the top of the
  field selector for the Single table will bring
  all fields into the design grid.
• This will cause all fields to be displayed in the
  results.
• The results are limited to those singles that
  drink socially.
• Note that the show box for the drinker field is
  unchecked. The drinker field is already going to
  display because it is included in the all
  fields option.

5
Displaying All Fields (continued)

• The red exclamation point button on the toolbar
  is used to run the query.
• You can also check the query results by going
  into datasheet view.
• The results show that 430 singles described
  themselves as social drinkers.
• The datasheet could be printed.
• The query could be saved and a more user-friendly
  report could be generated.

6
Less Than

• The query to the right displays all fields for
  those singles that are younger than 31.
• Other comparison operators include , , , and
  
• This database contains 232 singles that are less
  than 31 years old.

7
Between

• Combining comparison operators and Boolean
  operators can search for singles between the ages
  of 30 and 50.
• This query shows there are 151 single men between
  the ages of 30 and 50.

8
Show Selected Fields

• Fields used for criteria do not need to be
  displayed with the results.
• This query displays the city and marital status
  of women who dont drink. (37 hits)
• It is not necessary for the end user to see
  female displayed over and over again.

9
Wildcards

• Wildcards allow you to search for unknown text
  that contains some known characters.
• This query searches for singles that live in a
  city that stars with the letter M.
• M was typed into the criteria box and Access
  automatically entered the word Like and the
  quotation marks.
• The asterisk represents multiple characters,
  while the question mark (?) represents a single
  character.

10
Using the or Row

• When setting multiple criteria for a single
  field, the or row can be used to organize the
  design.
• Note that Female needs to be included in each
  row to specify that the results should contain
  only women in each of the three cities.

11
Sorting Results

• The sort row in the design grid is used for
  sorting results in either ascending (smallest to
  largest) or descending (largest to smallest).
• Text fields are sorted ascending (a to z) and
  descending (z to a).
• To sort by multiple fields, the fields need to be
  included side by side, with the primary sort
  field on the left.

12
Grouping and Average

• Display the totals row by clicking the sigma
  button on the toolbar.
• The totals row gives options for grouping by
  common data and for performing functions such as
  average, count, max, and min.
• The results show the average age of all females
  and males to be 31, but males are slightly older.

13
Calculated Query

• Sometimes, fields need to be calculated into a
  new field so that results can be displayed.
• The database contains the height of the singles
  in two separate fields (feet and inches). This
  format is more user-friendly.
• In order to find average height and search for
  heights within a range, a common data field will
  be necessaryheight in total inches.

How would you search for people taller than 510
using separate fields? HeightFeet 5
AndHeightInches 10 would leave out people
that are 62. You would need multiple Or
statements that are inefficient.
14
Calculated Query (continued)

• A calculated query is entered into the field box
  of a design grid.
• The format is the new field name, a colon, then
  the formula.
• To make writing the formula easier, right-click
  on the field box and choose the zoom option.

15
Calculated Query (continued)

• HeightTotal is the name of the new field.
• It is created by taking the Existing HeightFeet
  field times 12, plus the HeightInches field.
• Whether the new HeightTotal field is displayed or
  not, it can be used to sort singles by height and
  to search for singles within a height range.