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.
Queries are often used as the source for forms and reports and for Lookup fields.
Examples below are from LanguageArtsClasses.accdb .
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):
Purpose 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])
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.
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.
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.
Point 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 and in Wingdings it is a pencil
.
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.