Краткое содержание 29 Об этих стрелках 30 о сочетаниях клавиш 32 о щелчках кнопкой мыши 33 Примеры 33
 Скачать 19.64 Mb.
|
|
Для тех, кто понимает. Вычисления для дат и времени Используя функции для дат, всегда следует помнить о датах, содержащих информацию о времени. (Напоминаю, все даты могут содержать данные о времени суток. Но, выбирая подходящий формат для поля с датами, вы сообщаете программе Access о том, нужно ли отображать временной компонент даты и разрешать пользователям вводить его, как объясняется в разд. "Дата/время " главы 2. Чаще всего вы будете пользоваться форматом, скрывающим любую информацию о времени суток.) Проблема: функция Date ( ) возвращает текущую дату со значением времени суток, равным 0. Другими словами, если текущая дата — 4 июля 2008 г., то функция Date ( ) возвращает первую секунду 4 июля 2008 г. — момент, когда часы показывают полночь (12:00 a.m.) Если вы не храните значения времени суток, эта проблема не важна, поскольку у всех ваших дат время суток равно 0. Но что произойдет, если вы используете Полный формат даты (см. табл. 2.3) в поле DueDate, разрешающий пользователям вводить и дату, и время. Теперь у условия отбора = В данной ситуации, возможно, нужно изменить условие отбора на следующее: <(Date()+l) Date () +1 — это завтра. Другими словами, это условие отбирает любые записи со сроком платежа, истекшим до первой секунды завтрашнего дня. Между прочим, у программы Access есть функция Now (), возвращающая текущую дату и время. Таким образом, следующее условие фильтрации отбирает все записи, соответствующие текущему моменту (текущего дня) или любому моменту времени во все предшествующие дни: = Таблица 7.6. Функции обработки дат
Подсказка У программы Access есть другие функции обработки дат, выполняющие часть алгоритма функции DatePart ( ). Примером может служить функция Month ( ), извлекающая номер месяца из даты. К другим аналогичным функциям относятся Year(), Day ( ), Hour ( ), Minute ( ) и Second ( ). Эти функции не дают никаких преимуществ, но вы можете встретить их в запросах других пользователей, применяющих их для получения аналогичных результатов. Обработка пропущенных или неопределенных значений В БД есть два типа полей: обязательные и необязательные. Как правило, в БД поля необязательные (как обсуждалось в разд. "Запрет незаполненных полей" главы 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). Итоговый запрос — это вид запроса, который должен перелопатить большое количество записей и выдать лаконичные итоги. Для создания итогового запроса выполните следующие действия:
В приведенном далее примере используется таблица Products из БД Boutique Fudge. 3. Вставьте поля, которые хотите использовать. В этом примере используется поле Price, но хитрым образом: поле Price вставляется три раза для того, чтобы в запросе отображались результаты трех разных вычислений. 4. Выберите Работа с запросами → Показать или скрыть →Итоги (Query Tools → Show/Hide → Totals). Программа Access вставляет ячейку Групповая операция (Total) для каждого поля сразу под ячейкой Таблица. 5. Для каждого поля задайте вариант из списка Групповая операция. Этот вариант определяет использование поля для вычисления итога или для группировки. Итоговый запрос немного отличается от обычного запроса. Каждое поле должно попадать в одну из следующих категорий.
Примечание Если вы попытаетесь вставить в итоговый запрос поле, которое не используется для вычисления или группировки и не скрыто, то получите ошибку при попытке выполнить запрос. В этом примере (рис. 7.8) в поле Price применяются три разные групповые операции: Max, Min и Avg.  Р ис. 7.8. Вверху: в данный итоговый запрос поле Price включено трижды и использует три разных вычисления. Обратите внимание на то, что в каждое поле применяется выражение, в котором дано более информативное название поля. Внизу: результаты отображают одну запись с максимальной ценой, минимальной ценой и средней ценой продуктов, проданных компанией Boutique Fudge Примечание В табл. 7.7 не указаны два варианта, предназначенные для статистиков — StDev и Var — которые вычисляют стандартное отклонение и дисперсию ряда чисел. Таблица 7.7. Варианты получения итоговых данных
Разрабатывая итоговые запросы, можно использовать все приобретенные ранее в этой главе навыки написания запросов. Если вы хотите суммировать только продукты в конкретной категории, можно использовать в поле CategoryID, например, такое условие отбора: =3 Это условие отбирает записи, у которых CategoryID равно 3 (что означает их включение в категорию Candies (конфеты)). Примечание Если вы хотите выполнить фильтрацию в поле, которое не используется в вычислении или группировке, убедитесь, что в ячейке Групповая операция выбран вариант Условие и сброшен флажок Вывод на экран. Группировка в итоговом запросе Наипростейший итоговый запрос суммирует все выбранные записи в одну строку результатов, как показано на рис. 7.8. В более сложном итоговом запросе применяется группировка для вычисления промежуточных итогов. Для корректного применения группировки следует помнить о том, что поле, которое вы используете, должно содержать много повторяющихся значений. Например, хорошо группировать клиентов по штатам, в которых они живут. Поскольку в каждом штате много клиентов, у вас получатся осмысленные промежуточные итоги. Глупо группировать клиентов по номерам социального обеспечения, поскольку в результате получится столько групп, сколько у вас клиентов. На рис. 7.9 показан итоговый запрос с применением группировки. Р  ис. 7.9. Вверху: продукты сгруппированы по категории продукта. Внизу: результат — отдельная строка с итогами по каждой категории продуктов В итоговом запросе можно использовать многоуровневую группировку, вставив несколько полей со значением Группировка в ячейку Групповая операция. Но результаты могут оказаться неожиданными. Предположим, что вы группируете длинный список записей о продажах по наименованиям товаров и по именам клиентов. В результате вы получите отдельную группу для каждой комбинации "клиент — товар". В табл. 7.8 приведена часть результатов запроса, подобного описанному, в котором группируются записи из таблицы OrderDetails БД Boutique Fudge, а затем они сортируются по CustomerID. Таблица 7.8. Результаты запроса с многоуровневой группировкой
Таблица 7.8 (окончание)
Из этой таблицы видно, что клиент с номером 10 потратил в целом $432.12 на товар с номером 108 во всех заказах. Этот же клиент потратил $16.79 на товар с номером 134, $53.30 — на товар с номером 210 и т. д. (Вы можете взять эти данные и отсортировать их по коду товара ProductID, чтобы увидеть объемы продаж каждого товара для разных клиентов. У вас все та же информация, но анализировать ее можно по-разному.) Это результат, который вы хотели получить. Но ему недостает хороших промежуточных итогов. Было бы полезно узнать, сколько клиент с номером 10 потратил на каждый тип продукта и сколько он потратил всего. Но из-за жесткой табличной структуры итогового запроса получить такой результат невозможно. Если вы хотите увидеть эту разбитую на подгруппы информацию с промежуточными итогами, у вас есть две возможности. Можно использовать перекрестный запрос или запрос к сводной таблице - два улучшенных варианта подведения итогов, которые описаны в главе 9. Если же вас на самом деле интересует вывод вашей информации на печать, можно создать отчет, включающий многоуровневую группировку и итоги, как описано в части III. Объединения в итоговом запросе Итоговые запросы невероятно полезны, когда они комбинируются с объединениями (см. разд. "Запросы и связанные таблицы" главы 6) для получения связанной информации из нескольких таблиц. В БД Boutique Fudge таблица OrderDetails хранит отдельные компоненты каждого заказа. Вы можете сгруппировать эту информацию (как показано в предыдущем разделе) для поиска самых ходовых товаров или самых активных клиентов. Но, к сожалению, вы увидите только значения кодов клиентов и товаров, которые малоинформативны. Примечание Если у вас есть подстановка, определенная для полей ProductID и CustomerID, вы увидите описание из поля подстановки (например, наименование товара, имя и фамилию клиента). Эта информация немного полезнее, но, возможно, вы хотите извлечь дополнительные сведения — например, адрес клиента, описание товара и т. д. — находящиеся вне связанной таблицы. Если вставить в запрос объединение или два, можно извлечь подчиненную информацию из связанных таблиц (таких как Customers, Products и Orders) и добавить ее в ваши результаты. На рис. 7.10 показан пример вычисления общей стоимости каждого заказа. Затем результаты отсортированы по коду клиента CustomerID.  Рис. 7.10. Вверху: итоговый запрос можно усовершенствовать, дополнив его информацией из трех связанных таблиц: Customers, Orders и OrderDetails, для отображения списка сумм заказов, упорядоченных по клиентам. В запросе игнорируются заказы стоимостью меньше $25. Можно вставить условие отбора в поле DatePlaced, чтобы узнать, сколько потратили клиенты до нынешнего дня текущего года, сколько они потратили в прошлом году, на прошлой неделе и т. д. Внизу: результаты сгруппированы по OrderID и отсортированы по LastName и FirstName, что сохраняет хороший уровень детализации Вы уже знаете достаточно для того, чтобы построить запрос, показанный на рис. 7.10. Выполните следующие действии:
В примере на рис. 7.10 используются таблицы Customers, Orders и OrderDetails. После добавления этих таблиц программа Access вставит линии объединения между ними на основе связей, установленных в вашей БД. 3. Выберите Работа с запросами | Конструктор → Показать или скрыть → Итоги (Choose Query Tools | Design → Show/Hide → Totals). У каждого поля появится ячейка Групповая операция. 4. Добавьте поля, которые хотите использовать, и затем в ячейке Групповая операция выберите для каждого поля подходящие группировку или итоговое вычисление. Поля можно выбирать из любых связанных таблиц. В данном примере применяются следующие поля.
Примечание Этот трюк с группировкой немного странный, но широко применяется в итоговых запросах. Просто запомните, что программа Access создает самые маленькие доступные ей группы. Если вы хотите сгруппировать только по клиентам (так можно посмотреть, сколько потратил каждый клиент), нужно только удалить группировку в поле OrderID и установить ее в поле CustomerID. Или если вы хотите подсчитать объем продаж конкретного товара, удалите всю информацию о клиенте, сгруппируйте данные по полю ProductID и затем вставьте дополнительные поля из таблицы Products, которые вы хотите видеть на экране (например, ProductName и Description). 5. Теперь можно выполнить запрос. Параметры запроса Параметры запроса — секретное оружие программы Access. Они позволяют создавать сверхгибкие запросы за счет умышленного пропуска одной или нескольких порций информации. При каждом запуске запроса Access запрашивает у вас пропущенные значения. Эти недостающие значения и называют параметрами запроса. Обычно параметры запроса применяют в условиях отбора. Допустим, вы хотите вывести на экран список клиентов, живущих в конкретном штате. Можно создать целый набор запросов, таких как New York Customers, CaliforniaCustomers, Ohio Customers и т. д. Если вас на самом деле интересуют только несколько штатов, такой подход имеет смысл. Но если нужно работать с любым и каждым штатом, лучше создать один запрос, использующий параметр для задания штата. Когда запрос выполняется, вы в определенный момент вводите нужный штат. Для создания запроса с параметрами выполните следующие действия:
Б данном примере используется таблица Customers. 3. Выберите Работа с запросами | Конструктор → Показать или скрыть → Параметры (Choose Query Tools | Design → Show/Hide → Parameters). На экране появится диалоговое окно Параметры запроса (Query Parameters). Р  ис. 7.11. Можно определить столько параметров, сколько нужно. В окне задан один параметр, названный CustomerState и содержащий текст  4. Выберите имя и тип данных для вашего параметра (рис. 7.11).Вы можете использовать любое понравившееся вам имя (но не применяйте имя, которое используется для обозначения поля в вашем запросе). Тип данных должен соответствовать типу данных поля, для которого используется параметр. Тип данных задается выбором одного из вариантов в раскрывающемся списке. Самые распространенные варианты: Текстовый, Целый, Денежный и Дата/время. 5. Щелкните мышью кнопку ОК для закрытия окна Параметры запроса. Теперь можно ссылаться на параметр по имени так же, как вы ссылаетесь на поле в своем запросе. Например, можно добавить следующее условие отбора для поля State: [CustomerState] Убедитесь, что вы не забыли вставить квадратные скобки, чтобы программа Access знала, что вы не пытаетесь ввести фрагмент текста. Во время выполнения запроса Access откроет диалоговое окно Введите значение параметра (Enter Parameter Value) для ввода конкретного значения (рис. 7.12). Введите интересующий вас штат и щелкните мышью кнопку ОК. Программа использует ваше значение для отбора в поле State.  Рис. 7.12. При каждом выполнении запроса можно ввести другой штат. В данном случае будут отобраны клиенты из штата Нью-Йорк Подсказка Несмотря на то, что такая возможность есть, лучше не применять несколько параметров в одном запросе. Когда запрос выполняется, программа Access отображает отдельное диалоговое окно Введите значение параметра для каждого значения. Если у вас несколько параметров, вам придется сопровождать выполнение запроса щелчками мышью в назойливых диалоговых окнах. Практических решений, использующих запросы с параметрами, великое множество. Вы можете приспособить запрос о годовом объеме продаж для выполнения с выбранным вами годом. Можно сотворить аналогичное чудо и создать один запрос для отображения объема продаж за любой месяц. Однако параметры запроса не следует применять для решения повседневных задач, касающихся ввода данных (таких как обновление единственной записи о клиенте). Формы, которые вы начнете создавать в части IV, предоставляют гораздо больше возможностей для просмотра и корректировки информации. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||