Министерство образования и науки Республики Казахстан
Алматинское областное управление образования
Талдыкорганский колледж сервиса и технологий

Материалы слайд-курса
«Изучение запросов
в СУБД Microsoft Access»

г.Талдыкорган, 2015 г.

Использованные материалы:

Справка Microsoft Access 2003

Материалы слайд-курса
«Изучение запросов в СУБД Microsoft Access», могут быть полезны при подготовке уроков теоретического и практического обучения по профессии «Оператор ЭВМ»

Составитель: преподаватель спец.дисциплин
Исламова М.А.

Рецензенты: Рысбекова К.К., методист ТКСиТ
Токарева Н.А., председатель методического объединения мастеров производственного обучения
по профессии «Оператор ЭВМ»

Содержание

Введение 4

Примеры лекций 4

Тема: Использование вычислений в запросах 4

Тема: Примеры вычислений в запросах 13

Примеры упражнений для закрепления 13

Упражнение 1. Планирование запроса 13

Упражнение 2. Выполнение предварительно подготовленных апросов 14

Упражнение 3. Создание запроса на основе набора значений 12

Упражнение 4. Сравнение данных со значением

Упражнение 5. Значения в заданном диапазоне 10

Упражнение 6. Сумма и количество 9

Упражнение 7. Приоритет операторов 8

Упражнение 8. Создание вычисляемого поля 6

Упражнение 9. Использование построителя выражений 6

Упражнение 10. Вычитание дат 4

Упражнение 11. Проверка наличия пустых значений 4

1. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

2. Дважды щелкните запрос Оценки, чтобы выполнить его.

Вверху списка находятся записи, не содержащие оценок. Эти записи отсортированы в порядке возрастания, поэтому пустые значения отображаются сверху.

Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора.

3. В бланке запроса в поле Оценка (третья ячейка слева) введите оператор Is Null в ячейку Условие отбора. Не забудьте поставить пробел между словами Is и Null.

4. Запустите запрос.

5. Запустите запрос.

Подсказка. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Должны быть отображены две записи, не содержащие оценок.

6. Вернитесь в режим конструктора и замените оператор Is Null на Is Not Null.

Подсказка. Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора. В бланке запроса в поле Оценки введите оператор Is Not Null в ячейку Условие отбора.

Вновь выполните запрос, чтобы увидеть результаты.

Подсказка. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

На этот раз должны быть отображены только записи, содержащие оценки. Как вы убедились, с помощью операторов Is Null и Is Not Null можно легко изолировать записи с пустыми значениями.

1. Сохраните и закройте запрос, однако оставьте базу данных открытой для следующего упражнения.

1.Упражнение 10. Вычитание дат

В ходе этого практического занятия вам предстоит определить время задержки между датами заказов.

1. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

2. Щелкните запрос Время задержки, чтобы выбрать его, а затем нажмите кнопку Конструктор на панели инструментов "База данных", чтобы открыть запрос в режиме конструктора.

3. Щелкните пустую ячейку справа от поля "ДатаРазмещения" и введите следующее:

ВремяЗадержки: [ДатаИсполнения]-[ДатаРазмещения]

4. Запустите запрос.

Подсказка. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Должно быть отображено число дней между датой подачи и датой отправки заказа. При необходимости выполните прокрутку вправо.

5. Дополнительный шаг. Сортировка результатов.

Подсказка. Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора. В поле ВремяЗадержки щелкните ячейку Сортировка, затем щелкните появившуюся стрелку и выберите значение по возрастанию в списке.

6. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Теперь заказы отсортированы по времени задержки, от минимального значения до максимального.

7. Сохраните и закройте запрос.

2.Упражнение 11. Проверка наличия пустых значений

В ходе этого практического занятия вам предстоит использовать операторы Is Null и Is Not Null для поиска и исключения записей с пустыми значениями.

Введение

При изучении сложных тем «Запросы», «Создание запросов», «Вычисления в запросах» большим подспорьем является использование демонстрационных материалов. Бесспорно, ничто не заменит живой показ преподавателем в режиме «реального времени». Но, увы - скорость восприятия у каждого учащегося - величина сугубо индивидуальная. Поэтому необходимо давать возможность каждому возможность рассмотреть, повторить изложение, проверить свое запоминание, заглянуть куда-то в процессе выполнения задания.

Каждый преподаватель и мастер производственного обучения имеет какие-то предпочтительные для себя материалы для индивидуальной работы учащихся. Это могут быть не только лабораторные работы, так любимые многими. Упражнения, карточки, карточки-консультанты, обучающие программы, разнообразные слайд-шоу - арсенал средств современного учителя огромен.

Использование презентаций в этом плане, наверное, наиболее дешевый и быстрый способ создать учебный или дидактический материал, при этом - качество зависит только от квалификации самого преподавателя.

Разбивку по слайдам я приводить не стала, предлагаю сделать это самостоятельно с учетом количества часов, отведенных по программе на эти темы в вашем учебном заведении.

Примеры лекций

Тема: Использование вычислений в запросах

Запросы помогают фокусировать данные, тем самым повышая их ценность. Например, с их помощью можно определить товары с наиболее высокими и наиболее низкими уровнями продаж или найти все торговые сделки, произведенные на внутреннем рынке, которые превышают определенную сумму.

Один из способов фокусировки запросов заключается в определении наибольших и наименьших значений (например, поиск товаров с наиболее высоким уровнем продаж или студентов с самыми низкими оценками). Другой способ состоит в использовании текста, чисел, дат и вычислений для задания условий отбора, которым должны соответствовать данные.

В ходе этого урока вы познакомитесь с функцией "набор значений", которая помогает определять наибольшие и наименьшие значения, а затем подробнее изучите условия отбора. В конце курса вы сможете закрепить изученный материал с помощью практического занятия.

Можно извлечь наибольшие или наименьшие значения из данных.

3.Можно выбрать число или процент извлекаемых записей.

4.Щелкните ячейку Сортировка, затем щелкните появившуюся стрелку и выберите вариант по возрастанию для извлечения наибольших значений или по убыванию для извлечения наименьших значений

Вам необходимо определить наименьшие или наибольшие значения чего-либо, например наиболее дорогостоящие товары, производимые фирмой, продавцов с наибольшими цифрами квартальных продаж или студентов с наиболее низкой средней оценкой?

Запрос на основе набора значений позволяет найти нужную информацию с помощью всего нескольких щелчков мыши.

Можно указать число, например 10 торговых партнеров, или процент, например 20% наиболее низких средних оценок.

1. упражнении, а затем нажмите кнопку Построить в контекстном меню.

2. В левой нижней части построителя выражений дважды щелкните знак плюс рядом с элементом Таблицы, чтобы развернуть папку, а затем выберите папку Товары для отображения содержащихся в ней полей.

3. Дважды щелкните поле Цена, чтобы отобразить его в области выражения, расположенного в верхней части построителя выражений. Нажмите кнопку со звездочкой *(умножить), чтобы добавить звездочку в выражение, а затем введите 1,10.

4. Нажмите кнопку ОК, а затем щелкните вне поля, чтобы программа Access сформировала синтаксис выражения. Выражению по умолчанию присваивается имя Выражение1:; удалите имя по умолчанию и введите НоваяЦена2.

Построитель выражений автоматически добавляет идентификаторы объектов, что необходимо при создании сложных выражений с несколькими таблицами, запросами и т. д.

Щелкните за пределами ячейки, чтобы завершить создание поля. Поле должно выглядеть следующим образом:

НоваяЦена2: [Товары]![Цена]*1,1

5. Щелкните поле НоваяЦена2 правой кнопкой мыши, в контекстом меню выберите команду Свойства а затем в диалоговом окне Свойства поля щелкните поле Формат поля. Щелкните появившуюся стрелку, выберите значение Денежный в списке, а затем нажмите кнопку Закрыть.

6. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос. В полях НоваяЦена и НоваяЦена2 должны содержаться одинаковые результаты.

Совет. Хотя в данном случае можно было обойтись без помощи построителя выражений, мы видим, что это средство может оказаться весьма полезным при работе со сложными выражениями, поскольку оно автоматически создает синтаксис. Также можно использовать построитель выражений для создания базовой структуры, а затем настроить выражение вручную.

5.Упражнение 8. Создание вычисляемого поля

В связи с запланированным повышением цен на 10% вам требуется отобразить список новых цен.

1. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

2. Щелкните запрос Повышение цен, чтобы выбрать его, а затем нажмите кнопку Конструктор на панели инструментов "База данных", чтобы открыть запрос в режиме конструктора.

3. Щелкните пустую ячейку справа от поля Цена и введите следующее выражение:

НоваяЦена: [Цена]*1,1

4. Щелкните за пределами ячейки, чтобы завершить создание поля вычисления. Щелкните ячейку правой кнопкой мыши, выберите команду Свойства в контекстном меню, а затем щелкните поле Формат поля в диалоговом окне Свойства поля.

5. Щелкните появившуюся стрелку, выберите значение Денежный в списке, а затем нажмите кнопку Закрыть.

6. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Как вы видите, цены в столбце "НоваяЦена" увеличились на 10%.

7. Оставьте этот запрос открытым в режиме конструктора для выполнения следующего упражнения.

Подсказка. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид.

6.Упражнение 9. Использование построителя выражений

Теперь вам предстоит воспользоваться построителем выражений для создания аналогичного вычисляемого поля в том же запросе.

2. Если запрос "Повышение цен" отображается не в режиме конструктора, переключитесь в этот режим.

Подсказка. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид.

В бланке запроса щелкните правой кнопкой мыши ячейку справа от выражения НоваяЦена, созданного в предыдущем

В режиме конструктора щелкните стрелку рядом со значением Все в поле Набор значений , а затем выберите число или процент. Например, выберите 25%, если необходимо отобразить 25% наиболее низких или наиболее высоких средних оценок в базе данных учебного заведения.

В режиме конструктора также можно ввести собственное значение или процент в поле Набор значений.

Чтобы выбрать наименьшие или наибольшие значения, щелкните ячейку Сортировка в бланке запроса, затем щелкните стрелку рядом с этой ячейкой и выберите в появившемся списке вариант по возрастанию для извлечения наибольших значений или по убыванию для извлечения наименьших значений.

Условия отбора помогают ограничить результаты запроса путем более точного определения требований. В данном случае выполняется поиск только рецептов для ленча

Хотя с помощью запроса можно извлекать данные из нескольких полей большой таблицы (например, можно извлечь только имена и должности из большой таблицы сотрудников, содержащей 30 полей), использование условий отбора позволяет еще более ограничить набор результатов.

Например, можно извлечь записи сотрудников, подотчетных конкретному менеджеру или принятых на работу после определенной даты.

В ячейку Условие отбора в режиме конструктора вводится условие, которому должны соответствовать данные. Это условие может быть простым значением (простой текст, числа или даты) или выражением (значения и функции в сочетании со сравнениями и вычислениями). Более подробно выражения будут рассмотрены далее.

Тип

Пример

Описание

Текст

"администратор"

Поиск всех сотрудников с должностью "администратор"

Число

03

Поиск всех курсов с номером раздела 3

Дата

#03.04.06#

Поиск всех дат, точно соответствующих 03.04.06

Выражение с оператором сравнения

Использование функции Now() для извлечения всех дат, предшествующих сегодняшней

Выражение с вычислением

([ПолученноеКоличество])<([ЗаказанноеКоличество]-3)

Использование вычисления в условиях отбора

Примеры условий отбора.

В условиях отбора можно использовать различные типы данных, в том числе вычисления и сравнения. Ниже приведены некоторые типичные примеры:

Текст. Заключайте слова и словосочетания в кавычки. Возможно, вы заметили, что Access автоматически применяет кавычки к тексту в условиях отбора при запуске запроса, однако автоматическая расстановка кавычек может оказаться неправильной в сложных условиях отбора, содержащих несколько слов или точек, например:

"Звенигород, Московская обл." или "Нижний Новгород" Числа. В условиях отбора можно использовать числа и вычисления. Не следует заключать числа в кавычки, если данные хранятся в числовом поле (т. е. поле, содержащем только числа). Однако, если число хранится в текстовом поле (например, как часть адреса), его следует заключать в кавычки.

Даты. Существуют различные способы сравнения данных с датами. Необходимо заключать даты в знаки номера (#). Возможно, вы заметили, что Access автоматически применяет знаки номера к

Обратите внимания на поля НеправильноеИтоговоеЗначение и ПравильноеИтоговоеЗначение. Сейчас в обоих полях отображаются одинаковые неправильные значения. Вам необходимо исправить приоритет операторов в поле "ПравильноеИтоговоеЗначение".

1. Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора.

2. Щелкните поле ПравильноеИтоговоеЗначение правой кнопкой мыши, а затем в контекстном меню выберите Масштаб. Должно быть отображено следующее выражение:

ПравильноеИтоговоеЗначение: [НаСкладе]+[Заказано]*[Цена]

В соответствии с приоритетом операторов, значение поля "Заказано" умножается на значение поля "Цена", а затем суммируется со значением поля "НаСкладе". Однако нам необходимо сначала суммировать значения полей "НаСкладе" и "Заказано", а затем умножить полученную сумму на значение поля "Цена".

3. Введите скобки перед первой квадратной скобкой [НаСкладе] и после последней квадратной скобки [Заказано], как показано ниже:

ПравильноеИтоговоеЗначение: ([НаСкладе]+[Заказано])*[Цена]

4. Нажмите кнопку ОК, чтобы закрыть окно Область ввода.

5. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Теперь видна значительная разница между большинством полей "ПравильноеИтоговоеЗначение" и "НеправильноеИтоговоеЗначение". Например, в поле "ПравильноеИтоговоеЗначение" для товара "Chai" должно быть указано 31 590,00 р., а не 39,00 р., а для товара "Chang" - 48 735,00 р., а не 34 217,00 р.

6. Сохраните запрос, если требуется записать результаты, а затем закройте его.

1. На панели инструментов Конструктор запросов нажмите кнопку Групповые операции . В бланке запроса появится строка Групповая операция.

2. В поле СтоимостьДоставки щелкните ячейку Групповая операция (в этой ячейке вероятнее всего будет отображаться значение Группировка), затем щелкните появившуюся стрелку и выберите значение Sum в списке.

3. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Обратите внимание на то, что вычисляется общая сумма доставки заказов для каждой страны, и эти значения указываются в поле Sum-СтоимостьДоставки. Общая сумма для Аргентины должна составлять 598,58 р.

4. Теперь узнаем, сколько заказов было доставлено в каждую страну. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид для возвращения в режим конструктора.

5. Щелкните ячейку Групповая операция в поле СтоимостьДоставки, затем щелкните появившуюся стрелку и выберите элемент Count в списке.

6. Повторно выполните запрос и обратите внимание на то, что теперь указано число заказов для каждой страны. Для Аргентины должно быть указано три заказа.

Подсказка. Для повторного выполнения запроса нажмите кнопку Запуск на панели инструментов Конструктор запросов.

7. Сохраните запрос, если требуется записать результаты, а затем закройте его, но оставьте таблицу открытой.

7.Упражнение 7. Приоритет операторов

В ходе этого практического занятия вы узнаете, какую важную роль играют скобки и как неверное понимание приоритета операторов может привести к получению неожиданных значений.

7. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

8. Дважды щелкните запрос Приоритет, чтобы выполнить его.

датам определенного формата при запуске запроса, однако следует убедиться в правильности расстановки знаков номера, поскольку используемый формат дат может не распознаваться программой Access.

Выражения со сравнениями и вычислениями. Можно использовать комбинацию текста, чисел, дат и функций в сравнениях и вычислениях. Более подробно выражения будут рассмотрены в следующих разделах.

Примечание. Если при извлечении чисел или дат не удается получить нужные результаты, следует проверить тип данных поля. Например, некоторые числа могут обрабатываться как текст (как в случае адреса), в результате некоторые выражения (например, "более чем") будут работать иначе. Для определения типа данных следует просмотреть таблицу, содержащую нужное поле, в режиме конструктора.

Пример выражения, используемого как условие отбора. Это выражение позволяет найти невыполненные заказы, при этом не учитываются три единицы, о задержке которых известно заранее.

Текст, число или дата вводится в ячейку Условие отбора в том случае, когда требуется найти записи с данными, равными (=) заданным условиям. Даже при вводе простого значения программа Access автоматически формирует выражение.

Выражение объединяет значения (текст или числа) со встроенными функциями, полями, вычислениями, операторами (например, "более чем", >) и константами. Вот некоторые способы использования выражений: вычисление чисел; задание условий отбора; сравнение данных с определенным значением; задание логического условия (если x имеет значение "истина", выполнить y);

объединение текстовых строк, например имени и фамилии (иногда эта операция называется сцеплением).

Можно создавать собственные выражения, от простых сравнений (например, поиск торговых сделок суммой более 100 000) до диапазонов значений и вычислений (поиск дат доставки позднее даты заказа более чем на три дня).

Для создания выражения можно комбинировать текст, числа, даты, идентификаторы (например, имена полей), операторы (например, = или +), встроенные функции и константы (определенное неизменяемое значение, например "истина").

1. В верхней части построителя выражений содержится поле выражения, в котором выполняется построение.

2. В средней части находятся кнопки наиболее часто используемых операторов.

3. В нижней части находятся элементы данных, которые можно выбрать для использования в выражении.

Выражения можно вводить как самостоятельно, так и с помощью построителя выражений.

Построитель выражений используется в режиме конструктора. В бланке запроса щелкните правой кнопкой мыши ячейку, в которой требуется создать выражение, например ячейку Условие отбора одного из полей, а затем выберите в контекстном меню команду Построить.

Если вам известен синтаксис выражения, можно просто ввести это выражение в бланк запроса. Однако, если синтаксис выражения, которое требуется создать, неизвестен, можно воспользоваться построителем выражений. Также можно создать базовую структуру с

1. В ячейке Условие отбора в поле Цена удалите текущее условие и введите Between 25 and 40.

2. Нажмите кнопку Запуск на панели инструментов Конструктор запросов для просмотра результатов.

Обратите внимание на то, что результаты находятся в диапазоне от 25 до 40 р., а также включают записи товаров, цена которых равна точно 25 и 40 р.

3. Сохраните и закройте запрос и таблицу

8.Упражнение 6. Сумма и количество

В ходе этого упражнения вам предстоит рассмотреть сводную информацию о доставке заказанных товаров.

8. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

9. Дважды щелкните запрос Сводка по доставке, чтобы выполнить его.

10. На панели навигации запроса (обычно она располагается в левой нижней части экрана) показано число записей, возвращенных запросом.

Сейчас запрос находит все 122 заказа, страну, в которую был отправлен каждый заказ, а также стоимость доставки каждого заказа. Нам требуется определить общую стоимость доставки заказов в каждую страну.

11. Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора.

12. Нам не требуется отображать код каждого заказа, поэтому поле "КодЗаказа" следует удалить из запроса. В бланке запроса наведите указатель на тонкую горизонтальную полоску, называемую областью выделения столбца, непосредственно над полем "КодЗаказа". Когда указатель примет вид стрелки, щелкните мышью, чтобы выделить поле КодЗаказа, а затем нажмите клавишу DELETE.

Поле будет удалено, в результате другие два поля переместятся влево в бланке запроса.

1. Теперь восстановим все результаты. Этот шаг необходим для выполнения следующего упражнения.

Щелкните стрелку рядом с полем Набор значений на панели инструментов Конструктор запросов и выберите значение Все. Затем щелкните ячейку Сортировка в поле НаСкладе, щелкните стрелку рядом с ячейкой и в открывшемся списке выберите отсутствует.

2. Нажмите кнопку Запуск на панели инструментов Конструктор запросов. Примечание. Этот шаг необходим для выполнения следующего упражнения.

3. Закройте запрос, но не сохраняйте изменения и не закрывайте таблицу.

9.Упражнение 4. Сравнение данных со значением

Теперь необходимо отобразить список наиболее дорогих товаров, поэтому определим условие отбора для возвращения товаров стоимостью более 40 р.

1. Убедитесь, что в окне базы данных в группе Объекты по-прежнему выбран вариант Запросы.

2. Щелкните запрос Сравнение, чтобы выбрать его, а затем нажмите кнопку Конструктор на панели инструментов "База данных", чтобы открыть запрос в режиме конструктора.

3. В бланке запроса в поле Цена щелкните ячейку Условие отбора и введите >40.

4. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы выполнить запрос.

Обратите внимание, что показаны товары с ценой более 40 р.

5. Оставьте запрос открытым для следующего упражнения.

10.Упражнение 5. Значения в заданном диапазоне

В ходе этого упражнения вам предстоит изменить запрос, созданный в предыдущем упражнении, для поиска товаров, находящихся в определенном ценовом диапазоне.

4. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид для возвращения в режим конструктора.

помощью построителя выражений, а затем уточнить ее путем обычного ввода.

Примечание. С помощью построителя выражений также можно создавать вычисляемые поля (более подробно о них будет рассказано в ходе следующего урока).

Операторы используются для сравнения и объединения данных и условий отбора, а также выполнения над ними математических операций

Операторами называются символы и слова, указывающие, какое действие необходимо произвести с данными.

Операторы позволяют сравнивать данные со значением, выполнять математические операции, использовать несколько условий отбора, объединять текстовые поля (эта операция также называется сцеплением), а также производить многие другие действия.

Этот курс посвящен сравнениям и арифметическим операторам, однако сначала мы рассмотрим основные типы операторов:

Сравнение. Эти операторы используются для сравнения данных в базе данных со значениями или другими полями, например для извлечения записей всех товаров с числом единиц на складе не менее 100 (>=100) или всех оценок ниже 70 (<70).

Арифметические операторы. Эти операторы используются для выполнения математических операций, например для сложения значений полей ("Сумма" + "Чаевые") или умножения значения поля на указанное значение скидки ("Ставка" * .50).

Логические операторы. Эти операторы используют логику для определения истинности условия; наиболее распространенные логические операторы: And, Or и Not. С помощью логических операторов, например, можно извлечь список сотрудников, работающих в московском офисе и живущих в Подмосковье. У вас будет возможность поработать с операторами сравнения в ходе

Извлекаются товары в диапазоне от 25 до 40 р., включая товары, которые стоят ровно 25 или 40 р.

Что, если вам необходимо найти значение, находящееся в диапазоне между двумя другими значениями? Например, может потребоваться найти в базе данных недвижимого имущества все дома стоимость от 1 500 000 до 2 500 000 р. или все товары стоимостью от 25 до 40 р.

Можно использовать оператор Between, например "Between меньшее значение And большее значение". Пример такого выражения:

Between 25 and 40

Это выражение находит не только значения в диапазоне между двумя числами, но и значения, точно соответствующие меньшему и большему числу. Например, приведенное выше выражение также возвращает товары стоимостью 25 и 40 р., если они имеются в базе данных.

Примечание. Оператор Between часто используется в запросах с параметрами (в этом типе запроса на выборку пользователю предлагается ввести условия отбора), однако может использоваться и в других запросах на выборку.

Пройдите следующий тест, чтобы проверить, как вы поняли материал.

1. Требуется найти 10 студентов с наиболее низкими оценками. Что необходимо для этого сделать?

2. Нажмите кнопку Запуск на панели инструментов Конструктор запросов, чтобы повторно выполнить запрос.

Отображается сокращенный набор результатов, включающий записи товаров, которых имеется на складе меньше всего. Записи расположены по возрастанию количества единиц товара на складе. На панели навигации запроса указано, что была возращена 21 запись товаров с наименьшим количеством единиц на складе.

(Возможно, вы заметили, что 25% из 77 - не 21. В результатах запроса видно, что последние четыре товара имеют одинаковое количество единиц на складе. Программа Access не разрывает связь между результатами произвольным образом; вместо этого Access возвращает все связанные результаты, соответствующие запросу.)

3. Теперь необходимо отобразить список заданного числа товаров, которые имеются на складе в большом количестве, чтобы решить, следует ли выставить некоторые из этих товаров на продажу. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид для возвращения в режим конструктора.

4. Вновь щелкните стрелку рядом с полем Набор значений и введите число 30 для отображения 30 товаров с наибольшим количеством единиц на складе.

5. В бланке запроса в поле НаСкладе щелкните ячейку Сортировка, затем щелкните стрелку рядом с ячейкой и в открывшемся списке выберите по убыванию.

6. Нажмите кнопку Запуск на панели инструментов Конструктор запросов для повторного выполнения запроса и обратите внимание на результаты. Должно быть отображено 30 товаров с наибольшим количеством единиц на складе.

(В реальности отображается 31 товар. Почему это происходит? Подсказка: посмотрите на два последних товара в результатах запроса; на складе имеется по 36 единиц каждого из этих товаров. Как поступает программа Access в подобных случаях? Правильно, она включает в результаты обе записи.)

7. На панели инструментов Запрос в режиме таблицы нажмите кнопку Вид для возвращения в режим конструктора.

1. Дважды щелкните запрос Рецепты блюд для завтрака, чтобы запустить его. Обратите внимание, какие появляются рецепты.

2. Теперь нажмите кнопку Конструктор , чтобы увидеть, как устроен запрос.

3. Обратите внимание на сведения в строках Поле и Условие отбора.

Не волнуйтесь, если представление данных в этом окне покажется сложным. Пока просто просмотрите его. На следующем уроке это представление описывается более подробно. Закончив просмотр, закройте запрос и базу данных.

11.Упражнение 3. Создание запроса на основе набора значений

Сначала просмотрим товары, которых имеется в наличии меньше всего.

4. В окне базы данных в списке Объекты выберите элемент Запросы.

5. Дважды щелкните запрос Уровни запасов, чтобы выполнить его.

6. На панели навигации запроса (обычно она располагается в левой нижней части экрана) показано число записей, возвращенных запросом.

Сейчас запрос возвращает записи всех 77 товаров в таблице.

7. Нажмите кнопку Вид на панели инструментов Запрос в режиме таблицы, чтобы открыть запрос в режиме конструктора.

8. Щелкните стрелку рядом с полем Набор значений на панели инструментов Конструктор запросов и выберите значение 25%.

Теперь укажите, что требуется 25% записей с наименьшими значениями, задав порядок сортировки.

9. В бланке запроса в поле НаСкладе щелкните ячейку Сортировка, затем щелкните стрелку рядом с ячейкой и в открывшемся списке выберите по возрастанию.

A)Вычислить среднее значение всех оценок, а затем сравнить оценки со средним значением.

B)Воспользоваться функцией "Наименьшие значения".

C)Воспользоваться запросом на основе набора значений и задать 10 записей с сортировкой по возрастанию.

12.Даты в запросе должны быть заключены в _____.

A)Кавычки.

B)Скобки.

C)Знаки номера (#).

13.Чтобы будет найдено при выполнении следующего выражения?
Between 10 and 20

A)Все записи со значениями в диапазоне от 10 до 20, включая 10 и 20.

B)Все записи со значениями в диапазоне от 10 до 20, исключая 10 и 20.

C)Только те записи, итоговое значение которых находится в диапазоне от 10 до 20, включая 10 и 20.

14.Тема: Примеры вычислений в запросах

Итоговые суммы доставки вычисляются в запросе, а не хранятся в таблице.

Вам необходимо произвести вычисления над данными, например определить общую сумму доставки для каждой страны, сложить значения двух полей или вычислить цену при повышении на определенный процент?

"Запрос итогов" используется для выполнения различных вычислений, включая поиск среднего значения и подсчет количества записей, соответствующих определенным условиям.

Также можно создавать вычисляемые поля, отображаемые вместе с другими полями базы данных.

В обоих случаях вычисления не хранятся в базе данных, что помогает контролировать ее размер и быстродействие.

Чтобы продолжить чтение урока самостоятельно, щелкните Далее

Данный запрос возвращает итоговую сумму доставки для каждой страны в таблице "Заказы".

Кнопка Групповые операции.

Операции вычисления в ячейке Групповая операция.

Если требуется произвести вычисления над группой элементов, можно воспользоваться так называемым запросом итогов.

Название "запрос итогов" не должно вводить вас в заблуждение; эти запросы могут вычислять не только итоговое значение данных. Запрос итогов позволяет находить среднее значение группы элементов, подсчитывать число элементов, находить минимальное и максимальное число, а также выполнять другие задачи.

Этот запрос использует встроенные функции для вычисления среднего значения, подсчета количества и выполнения других операций. Подробные сведения можно найти в кратком справочнике в конце курса.

Важно понимать, по какому полю должна выполняться группировка данных, например итоговые суммы доставки группируются по полю Фрахт.

Сначала необходимо отобразить строку Групповая операция; для этого достаточно нажать кнопку Групповые операции на панели инструментов Конструктор запросов.

Щелкните ячейку Групповая операция в поле, по которому требуется группировать данные, а затем щелкните появившуюся стрелку. В списке Группировка выберите операцию вычисления или функцию, которую требуется применить.

Примеры упражнений для закрепления

15.Упражнение 1. Планирование запроса

В этом упражнении необходимо продумать запрос и зафиксировать его описание на бумаге. Запускать Access в этом упражнении не придется - все это будет в следующем упражнении этого практического занятия.

Сформулируйте вопрос или набор вопросов, на которые необходимо ответить с помощью данных. Если готового вопроса пока нет, подумайте, какие сведения вам могли бы понадобиться, например, в базе данных рецептов.

Запишите на бумаге следующее:

• Определите главный вопрос, по которому требуется получить данные, например, все видеоносители из домашней базы данных развлечений, которые вы отдали посмотреть друзьям, или все рецепты из базы данных кулинарных рецептов, в которых используются цыплята или вегетарианские продукты.

• Поля данных, которые необходимо отобразить, например, названия видеодисков, длина записей и состояние в базе данных развлечений; или название рецепта, время приготовления и необходимые продукты из базы данных рецептов.

16.Упражнение 2. Выполнение предварительно подготовленных запросов

4. Если окно базы данных не отображается, нажмите клавишу F11, чтобы открыть его. Убедитесь, что в окне базы данных в разделе Объекты выбран вариант Таблицы.

5. Дважды щелкните таблицу Рецепты, чтобы открыть ее.

6. Обратите внимание, что в этой таблице имеется пять записей. Закройте таблицу. Теперь выполните запрос, чтобы выбрать некоторые из этих рецептов.

7. В окне базы данных в разделе Объекты выберите элемент Запросы.

8. Дважды щелкните запрос Вегетарианские, который возвращает только вегетарианские ингредиенты. Закройте запрос.

9. Убедитесь, что в окне базы данных в разделе Объекты выбран вариант Запросы.

A)Неверно.

17.Выберите рекомендуемый способ работы с вычислениями.

A)Вычисления следует всегда хранить в таблице.

B)Для выполнения вычислений следует использовать запрос.

C)Следует хранить результаты в форме.

18.Вычисляемое поле

A)отображается в результатах запроса с полями из базы данных.

B)не может быть отображено в результатах запроса.

C)хранится как данные в базе данных.

Отображение области ввода вычисляемого поля.

Вычисляемое поле отображается вместе с другими полями в бланке запроса.

Окно Область ввода содержит расширенную область для ввода вычисления.

В предыдущем уроке вы ознакомились с возможностями использования выражений в качестве условий отбора. С помощью выражений также можно создавать вычисляемые поля.

Вычисляемым полем называется новое поле, создаваемое в запросе для отображения результатов вычисления. По умолчанию вычисляемое поле отображается в результатах запроса вместе с другими полями; оно также может отображаться в формах и отчетах на основе запроса.

Можно производить числовые расчеты, а также объединять текст (например, поля "Фамилия", "Имя" и "Отчество" для создания единого поля Ф.И.О. заказчика). В ходе этого урока будут рассматриваться числовые расчеты.

В отличие от поля базы данных, результаты вычисляемого поля не хранятся как данные. Вычисление производится при каждом выполнении запроса.

Чтобы создать вычисляемое поле, необходимо сначала задать для него имя, как и в случае с любым полем базы данных.

Примером вычисляемого поля может быть Сумма в следующем примере:

Сумма: [Количество] * [Цена]

Если не задавать имя поля, а просто ввести выражение, программа Access автоматически назначает имя Выражение1, Выражение2 и т. д. для каждого вычисляемого поля.

Ввести выражение в режиме конструктора можно одним из следующих способов:

Ввести выражение непосредственно в бланк запроса.

Или

Ввести выражение в поле Область ввода, щелкнув ячейку правой кнопкой мыши и выбрав команду Масштаб в контекстном меню.

Или

Воспользоваться построителем выражений, щелкнув ячейку правой кнопкой мыши и выбрав команду Построить в контекстном меню.

Выражение

Назначение

ОбщееКоличество: [Доставляется] + [Доставлено]

Отображение в поле "ОбщееКоличество" суммы значений полей "Доставляется" и "Доставлено". Это выражение позволяет определить, какая часть заказа была выполнена.

Сумма: [Количество]*[Цена]

Отображение в поле "Сумма" произведения значений полей "Количество" и "Цена".

СпецФрахт:Фрахт*1,1

Отображение в поле "СпецФрахт" стоимости доставки плюс 10%.

Итого: [Сумма]+[Налог]+[Чаевые]

Отображение в поле "Итого" суммы значений полей "Сумма", "Налог" и "Чаевые".

НедельныйЗаработок: [ПочасоваяОплата]*[ДниРабочейНедели]*[ЧасыРабочегоДня]

Отображение в поле "НедельныйЗаработок" общего недельного заработка путем умножения размера почасовой оплаты, количества часов рабочего дня и количества дней рабочей недели.

Примеры вычисляемых полей.

Значения Null также повлияют на результаты, если выполнить подсчет количества записей студентов на основе поля "Оценки", когда некоторые оценки еще не были записаны. Полученное значение не будет соответствовать общему числу студентов, поскольку студенты, которым еще не были проставлены оценки, не включаются в расчет.

Иногда необходимо исключить значения Null из результатов или ограничить результаты записями, содержащими только значения Null. Например, может потребоваться выполнить поиск студентов, еще не получивших оценки.

Для этого используются операторы Is Null и Is Not Null. Достаточно ввести такой оператор в бланке запроса в ячейку Условия отбора поля, значения которого требуется проверить.

Также можно обрабатывать значения Null с помощью выражения, выполняющего различные действия с записями в зависимости от условий. Можно воспользоваться встроенным выражением Is Null. Некоторые примеры приведены в таблице слева, а также в кратком справочнике в конце курса.

Некоторые типы полей (текстовые поля, поля MEMO и поля гиперссылок) также могут содержать пустые строки, которые используются в тех случаях, когда известно, что значение для поля отсутствует. Например, если студент отчислен, у него не должно быть оценки.

Для ввода пустой строки с клавиатуры следует ввести два символа двойных кавычек без пробела ("").

Примечание. В проекте Access, подключенном к базе данных Microsoft SQL Server™, можно ввести пустую строку в поле с типом данных varchar или nvarchar

Пройдите следующий тест, чтобы проверить, как вы поняли материал.

8. Можно использовать запрос итогов для вычисления среднего значения группы элементов.

D)Верно.

Примеры выражений с датами приведены в таблице слева, а также в кратком справочнике в конце курса.

Выражение

Назначение

ВремяДоставки: IIf(IsNull([ДатаНазначения] - [ДатаОтправки]), "Проверьте отсутствующую дату", [ДатаНазначения] - [ДатаОтправки])

Отображение в поле "ВремяДоставки" сообщения "Проверьте пропущенную дату", если одно из полей ("ДатаНазначения" или "ДатаОтправки") имеет значение Null; в противном случае отображается разность значений полей.

ТекущаяСтрана: IIf(IsNull([Страна]), "", [Страна])

Отображение в поле "ТекущаяСтрана" пустой строки, если поле "Страна" содержит значение Null; в противном случае отображается значение поля "Страна".

=IIf(IsNull([Область]),[Город]&""& [Индекс], [Город]&" "&[Область]&"" &[Индекс])

Отображение значений полей "Город" и "Индекс", если поле "Область" содержит значение Null; в противном случае отображаются значения полей "Город", "Область" и "Индекс".

Примеры выражений Is Null.

Поле, в которое не было введено никаких данных, называется полем со значением Null.

При выполнении вычислений, запуске запроса итогов или сортировке с полем, содержащем значения Null, могут быть получены неожиданные результаты. Например, функция Average автоматически игнорирует поля, содержащие значения Null.

Вычисляемое поле может использоваться для сложения, вычитания, умножения, деления, а также выполнения других операций с данными. В нем могут содержаться поля данных, например "Цена*Количество", а также числа и встроенные функции.

Можно вычислить повышенную ставку (например, для срочного или необычного заказа), увеличив стоимость доставки на 10%. Дополнительные примеры приведены в таблице слева, а также в кратком справочнике в конце курса.

Оператор

Назначение

+

Сложение.

-

Вычитание.

*

Умножение.

/

Деление.

\

Деление с округлением числовых выражений и частного.

^

Возведение основания (первое числовое выражение) в степень (второе числовое выражение).

Mod

Округление числовых выражений до целых чисел, деление первого целого числа на второе и возвращение остатка. Может использоваться для возращения каждой N-й (например, каждой седьмой) записи.

Операторы и приоритет операторов.

Выражения основаны на арифметических операторах. Как вы знаете из первого урока, операторами называются символы или слова, используемые для выполнения различных операций с данными.

Некоторые арифметические операторы также используются для объединения (или сцепления) текстовых полей, однако в рамках настоящего курса рассматривается использование операторов со значениями.

Чаще всего используются операторы сложения (+), вычитания (-), умножения (*) и деления (/), однако полезно знать и другие операторы и понимать их назначение.

Оператор

Порядок

Возведение в степень (^)

1

Отрицание - знак (-) в начале

2

Умножение и деление (*,/)

3

Оператор

Порядок

Целочисленное деление (\)

4

Модуль (Mod)

5

Сложение и вычитание (+,-)

6

Таблица распространенных операторов с указанием приоритета.

Если выражение содержит несколько арифметических операторов, Access обрабатывает одни операторы прежде других. Это явление называется приоритетом операторов.

Можно изменить порядок обработки операторов, заключив в скобки ту часть выражения, которую требуется обработать в первую очередь.

При наличии нескольких вложенных скобок Access выполняет обработку начиная с наиболее глубокого уровня. При наличии

Выражение

Назначение

ВремяПриема: DateAdd("h", 3, [ВремяПрибытия])

Отображение времени на три часа позже времени прибытия.

Возраст: DateDiff("yyyy", [ДатаРождения], Now())+ Int( Format(now(), "mmdd") < Format( [ДатаРождения], "mmdd") )

Вычисление возраста на основе даты рождения относительно текущей даты.

ВремяЗадержки: DateDiff("d", [ДатаРазмещения], [ДатаОтправки])

Отображение количества дней между датами в полях "ДатаРазмещения" и "ДатаОтправки".

ГодНайма: DatePart("yyyy", [ДатаНайма])

Отображение года найма каждого сотрудника.

НомерМесяца: DatePart("M", [ДатаРазмещения])

Отображение номера месяца, например 8 для августа.

ТекущийМесяц: Format(Now(), "mmm")

Отображение сокращенного названия месяца для текущий даты, где mmm представляет месяц от "янв" до "дек".

Примеры выражений с датами.

Можно использовать даты в вычислениях, например можно вычесть одну дату из другой или извлечь все даты, предшествующие текущей.

В программе Access значения даты и времени хранятся в виде десятичных чисел, поэтому их можно использовать в математических уравнениях. Однако при простых вычислениях с датами вам не нужно вникать в принципы обработки выражений, поскольку Access берет на себя выполнения всех математических операций.

Программа Access позволяет не только складывать и вычитать даты. С помощью функции DatePart можно отображать часть даты, например год найма сотрудника.