Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries

The power of a database comes from its queries.

A query can sort records, select certain records, and display only certain fields or certain records and can even perform actions on those records.

Types of Queries

  • Icon: Select Query Select - Most commonly used type. Can sort or filter and can calculate values using several fields.
  • Parameter - Any kind of query that first asks the user for input that is used in creating the final query.
  • Action - Selects records and then performs an action on them
    • Icon: Make-Table Query Make-Table creates a new table from selected records. Does not affect the original table(s).
    • Icon: Append Query Append selected records to an existing table.
    • Icon: Delete Query Delete selected records.
    • Icon: Update Query Update values in selected records.
  • Icon: Crosstab Query Crosstab - Selects records and calculates totals based on one set of values down the left and another across the top of the datasheet.

Queries are often used as the source for forms and reports and for Lookup fields.

Examples below are from LanguageArtsClasses.accdb Icon: On Site.


Query with Multiple Tables or Other Queries

In your previous work you used the Query Wizard to create a simple query based on just one table. In the next lessons you will create more complex queries using several related tables.

Happily, you have a lot of control over what the query does.

Example Query - Students Allowed on Computer
         (from the Language Arts Classes database):

Query Design View: multiple tablesPurpose of query: To produce a list of students who have qualified for free-play time on the classroom computers.

The query No Computer lists students who have incomplete assignments as of a certain date and therefore do not qualify. I wanted a list from the other point of view - those who DID qualify. (It was a shorter list!)

Objects used: Students table and No Computer query.

Join: On field StudentID. All records in Students and only the records in No Computer where the values are equal.

Fields used: Class field from the Students table and the StudentID field from the query No Computer.

Calculated field: The query uses the three fields for a student's name (LastName, FirstName, Called) in the Students table to create a single text string for a new field called Name. The result will be like "Richardson, Brad" even though the student's first name is Bradley.

Name: IIf([Called] Is Null,[LastName] & ", " & [FirstName],[LastName] & ", " & [Called])

Query Datasheet View: Multiple tables (Access 2016)Yes, you will learn how to create such a calculation! It's not as hard as it looks. Really!!

Sorting: Sort Ascending on Class and Name fields.

Criteria: For StudentID field, Is Null

Resulting Datasheet:
A parameter box appears for you to enter a date. That came from the query No Computer. The Design grid for the new query did not show that there was a question to answer!

The results datasheet for this query shows that 24 of the 92 students on the date selected(05/01/95) would be allowed to use the classroom computer. The StudentID field does not show in the datasheet because the Show box in the design grid was not checked.


Query with Calculated Fields

A powerful feature of queries is the ability to calculate new fields, like the Name field in the example above.

Calculate with Text: Combining text fields or text and number fields into a single text string, along with some punctuation and spaces. Like, "The score for Julie French was 95" combines fields for the name (Julie French) and the score (95) along with static text, "The score for" and "was".

Calculate with Numbers: Averaging six weeks grades for a semester grade or calculating profits and losses. The options are endless!

Such new fields are often used in forms and reports to control the display so that there is no wasted space.

In this project, you will create a number of calculated fields. Later you will create calculated controls for forms and reports, which are quite similar but do have some important differences.


Characters Not on the KeyboardCharacter Map showing keystroke to insert letter not on the keyboard

Modern fonts have a LOT more characters than are on the keyboard. Many languages use characters that are not in English. For example Spanish includes many accented characters like á, é, ú, Õ and ñ. French includes characters like è and ç. You can buy a keyboard that is customized for your own language.

Access does not have an Insert Symbol command like other Office programs. Characters that are not on your keyboard can be entered with a combo of ALT + keypad numbers. But, how do you know what the number is for your special character??

Use the Character Map program that comes with Windows, charmap.exe. It shows a grid of characters for the selected font and shows the code at the bottom of the dialog. The letters, numbers, and symbols on a normal keyboard do not show a keyboard shortcut in this dialog.

Icon: Confused smileyPoint of Confusion: Code depends on Font
The keyboard shortcut code for a character is for a spot in the grid. Different fonts may put different symbols in that spot. So you can get a surprise if you are using a different font than usual. For example, the first character on the first row in the Character Map is an exclamation point ! in most fonts. But in Webdings it is a spider Symbol: Spider (Webdings) and in Wingdings it is a pencil Symbol: Pencil (Wingdings).

Alternate keyboard layout: If you work a lot in a second language, you can install an alternate keyboard layout in Regional and Language Options in the Control Panel, and then switch back and forth between layouts. Of course, your physical keyboard won't change so you must be familiar with which characters are where.

Or you can make a cheat sheet of the keystroke codes that you need regularly.