Обработка пропущенных или неопределенных значений

В БД есть два типа полей: обязательные и необязательные. Как правило, в БД поля необяза­тельные (как обсуждалось в разд. "Запрет незаполненных полей" главы 4), что означает для неаккуратного пользователя возможность пропуска большого числа значений. Эти пропу­щенные значения называют неопределенными (null), и их следует тщательно обрабатывать.

Если вы хотите написать условие отбора, вылавливающее неопределенные значения, просто введите в ячейку Условие отбора следующий текст: Is Null

Это условие выберет все записи с пропущенными значениями. Воспользуйтесь им в поле CustomerID таблицы Orders для поиска всех заказов, не связанных с клиентом. Или игно­рируйте несвязанные записи, заменив условие отбора на обратное:

Is Not Null

Иногда вам не нужно специально искать (или игнорировать) неопределенные значения. Вместо этого вам надо заменить их для рассматриваемой задачи чем-то более информатив­ным. К счастью, как раз для этого есть функция со странным названием Nz ( ).

У функции Nz () два аргумента. Первое значение (как правило, поле запроса) может со­держать неопределенное значение. Второй параметр — это значение, которое вы хотите ото­бразить в результатах запроса, если программа Access найдет неопределенное значение. Да­лее приведен пример, использующий функцию Nz ( ) для преобразования в 0 неопределенных значений в поле Quantity:

Nz([Quantity], 0)

Преобразование в 0 — стандартное поведение для функции Nz ( ), поэтому можно опус­тить второй параметр, если это как раз то, что вам нужно:

Nz([Quantity])

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

OrderItemCost: [Quantity] * [Price]

Это выражение приведет к ошибке, если значение поля Quantityбудет неопределенным. Неопределенные значения странным образом распространяются наподобие инвазивного грибка. Если у одного из операндов в вычислении неопределенное значение, результат автоматически становится неопределенным. В данном примере это означает, что иоле OrderItemCostдля данной записи становится неопределенным. Хуже того, если OrderItemCostввести в другое вычисление или промежуточный итог, они тоже станут не­определенными. Прежде чем вы об этом узнаете, ваши значимые данные запроса превратят­ся в кучу ячеек с неопределенными значениями.

Для устранения этой проблемы очистите необязательные ноля от неопределенных зна­чений с помощью функции Nz ( ): OrderItemCost: Nz([Quantity]) * Nz([Price])

Наконец, функцию Nz ( ) можно использовать для замены всех неопределенных значе­ний другой величиной, В текстовое поле можно ввести что-то более информативное. Далее приведен пример, отображающий текст ("Not Entered" — не введено) рядом с каждой записью, не содержащей имени и фамилии:

Name: Nz([FirstName] & [LastName], "[Not Entered]")

Итоговые данные

Все запросы, которые вы применяли до этого момента, имели дело с отдельными записями. Если вы выбирали 143 записи из таблицы Orders,то видели все 143 записи в ваших резуль­татах. Но вы также можете группировать записи для получения промежуточных и общих итогов. В этом случае легче просматривать большие объемы информации и делать важные далеко идущие выводы.

Далее приведены примеры полезных подводящих итоги запросов:

■ подсчет студентов в каждом классе;

■ подсчет количества заказов, сделанных каждым клиентом;

■ сумма, потраченная на один продукт;

■ общая сумма долга или платежа клиента;

■ подсчет среднего объема заказа, сделанного каждым клиентом;

■ поиск самого дорогостоящего или самого дешевого заказа, сделанного клиентом.

Эти операции — подсчет, суммирование, определение среднего и поиск максимального и минимального значений — основные варианты в итоговом запросе (totals query). Итоговый запрос — это вид запроса, который должен перелопатить большое количество записей и вы­дать лаконичные итоги.

Для создания итогового запроса выполните следующие действия:

1. Создайте новый запрос, выбрав Создание→ Другие→Конструктор запросов(Create → Other → Query Design).

2. Добавьте нужные вам таблицы с помощью диалогового окна Добавление таблицы(Show Table) и щелкните мышью кнопку Закрыть(Close).

В приведенном далее примере используется таблица Productsиз БД Boutique Fudge.

3. Вставьте поля, которые хотите использовать.

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

4. Выберите Работа с запросами→Показать или скрыть→Итоги(Query Tools → Show/Hide → Totals).

Программа Access вставляет ячейку Групповая операция (Total) для каждого поля сразу под ячейкой Таблица.

5. Для каждого поля задайте вариант из списка Групповая операция.Этот вариант определяет использование поля для вычисления итога или для группировки.

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

■ Поле используется в итоговом вычислении (таком как определение среднего, подсчет количества и т. д.). Тип нужного вычисления выбирается с помощью ячейки Групповая операция.В табл. 7.7 перечислены все варианты из ячейки Групповая операция.

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

■ Поле используется для фильтрации или отбора. В этом случае в ячейке Групповая операциянужно выбрать Условие (WHERE). (Фанаты БД, возможно, помнят, что WHERE — это ключевое слово, применяющееся для определения условия в языке SQL, как было описано в разд. "Анализ запроса" главы 6). Нужно также сбросить флажок Вывод на экран,поскольку программа Access не может выводить отдельные значения в итоговых сводках.

Примечание

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

В этом примере (рис. 7.8) в поле Price применяются три разные групповые операции: Max, Min и Avg.

Рис.7.8. Вверху: в данный итоговый запрос поле Priceвключено трижды и использует три разных вычисления. Обратите внимание на то, что в каждое поле применяется выражение, в котором дано более информативное название поля. Внизу: результаты отображают одну запись с максимальной ценой, минимальной ценой и средней ценой продуктов, проданных компанией Boutique Fudge

Примечание

Втабл. 7.7 не указаны два варианта, предназначенные для статистиков — StDevи Var— ко­торые вычисляют стандартное отклонение и дисперсию ряда чисел.

Таблица 7.7. Варианты получения итоговых данных

Значение в ячейке Групповая операция	Описание
Группировка	Группирует записи, основываясь на значениях в этом поле
Sum	Суммирует значения этого поля
Avg	Находит среднее для значений этого поля
Min	Возвращает наименьшее значение в этом поле
Max	Возвращает наибольшее значение в этом поле
Count	Подсчитывает количество записей (независимо от того, какое поле вы используете)
First	Возвращает первое значение в этом поле
Last	Возвращает последнее значение в этом поле

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

=3

Это условие отбирает записи, у которых CategoryIDравно 3 (что означает их включение в категорию Candies (конфеты)).

Примечание

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