Рис.7. 8. Пример бланка запроса с использованием шаблонов и его результат
Оператор Like обычно используется при задании шаблонов для текстовых или Memo полей. Однако с его помощью можно создавать шаблоны для отбора записей по содержимому поля даты (см. табл. 7.1).
7.4.3. Диапазон значений. Операторы And и Between
Иногда возникает необходимость отобрать записи, у которых значения в указанном поле лежат в некотором диапазоне значений. Диапазон значений можно определить, если перед значением указать один из операторов сравнения (см. предыдущие блоки), например, > 100 или <= 10. Операторы сравнения могут использоваться в текстовых и числовых полях, а также в полях дат. Если диапазон имеет две границы, то оба граничных условия должны быть помещены на одной строке и разделены логическим оператором And.
Пример . Допустим, что нужно получить информацию о студентах, родившихся в первой половине августа 1991 года. В этом случае в поле Дата рождения бланка запроса нужно ввести условие >=#01.08.1991# And <=#15.08.1991# (см. рис.7.9).
Рис. 7.9. Запрос на выборку
Результат поиска – на рисунке 7.10, мы добавили на вывод номер группы и фамилию:
Рис.7.10. Результат выполнения запроса по году рождения.
Другим способом задания диапазона значений является использование оператора Between. Например, с его помощью приведенное выше условие отбора можно записать так: Between #01.08.72# And #15.08.72#.
7.4.4. Список значений. Операторы Or и In
Для проверки, содержится ли значение в заданном списке, нужно использовать логический оператор Or или оператор In. Если список невелик (не более 9 элементов), то требуемый результат можно получить, введя каждое значение из списка в отдельной ячейке строки условий или.
7.4.5. Отрицание образца. Оператор NOT
Чтобы найти записи, которые не удовлетворяют определенному условию, перед условием выбора нужно поставить логический оператор Not. Этот оператор можно использовать вместе со всеми другими рассмотренными выше условиями выбора.
Пример. Чтобы получить информацию обо всех студентах кроме тех, кто учится в группе 9701, в поле Группа бланка запроса к таблице Студенты надо ввести текст not 9701.
7.4.6. Отбор записей с Null значениями и пустыми строками
Иногда могут понадобиться записи, в полях которых отсутствуют значения. Вы можете найти записи в таблице, не содержащие значения в определенном поле, если наберете слово null в этом поле в бланке запроса. Access создаст условие отбора вида Is Null, и в таблицу попадут только те записи, которые не содержат значения (имеют пустое значение) в этом поле. Соответственно, вы можете найти только те записи, которые содержат значения в определенных полях, если наберете not null (Is Not Null) в этих полях в бланке запроса (например, получал ли студент стипендию?).
7.4.7. Использование нескольких строк условий
При создании сложных запросов может возникнуть ситуация, когда описание критерия отбора занимает не одну, а несколько строк в бланке запроса. В этом случае Access последовательно анализирует каждую из строк и включает в итоговый набор те записи, для которых эти условия выполняются. Если строка содержит несколько ячеек с условиями отбора, то в набор попадают лишь те записи, которые удовлетворяют одновременно всем условиям в этой строке. В результате в итоговый набор будут включены все записи, удовлетворяющие хотя бы одной из строк условий.
7.4.8. Использование вычисляемых полей
В ряде случаев для отбора записей необходимо включить в запрос вычисляемое поле и ввести условие, использующее значения этого поля. Пример. Таблица Заказы содержит сведения о заказанных товарах. В частности, поле Количество содержит информацию о числе заказанных товаров, а поле Цена — цену товара. Чтобы выбрать из таблицы сведения о заказах, стоимость которых не ниже 500 руб., следует включить в запрос нужные поля, а также создать вычисляемое поле
Стоимость: [Количество]*[Цена] и ввести в него условие >=500.
7.5. Запросы с параметром
В условиях отбора можно использовать не только конкретные значения, но и параметры. Перед выполнением запроса, содержащего один или несколько параметров, Access каждый раз будет запрашивать конкретные условия отбора и затем произведет отбор нужной информации. Используя параметры, можно заменить несколько однотипных запросов к данным одним запросом.
Чтобы задать параметр, нужно ввести в строку Условие отбора вместо конкретного значения произвольный текст, заключенный в квадратные скобки [ ]. Этот текст Access рассматривает как имя параметра и выводит его в специальном диалоговом окне при выполнении запроса. Поэтому в качестве имени параметра лучше использовать содержательную фразу, причем имя каждого параметра должно быть уникальным. Например, создадим запрос, который выводит дату рождения студента по номеру группы и его фамилии (см. рис.7.11, 7.12).
Рис.7.11. Окно запроса с параметром
При выполнении запроса Access попросит ввести одно за другим значения для каждого из параметров, используя для ввода диалоговые окна Введите номер группы, а затем – Введите Фамилию. После ввода всех значений запрос будет выполнен и на экране появится результирующий набор записей.
Рис.7.11. Запрос с параметром
Рис. 7.12. Результат выполнения запроса
7.6. Итоговые запросы
Иногда требуется, чтобы результатом выполнения запроса были не значения отдельных записей таблиц БД, а итоговые значения по группам записей. Например, может понадобиться определить средние баллы по экзаменам в каждой учебной группе или найти суммарную стипендию, полученную студентами в каком-то месяце. Получить ответ на такие вопросы можно с помощью итогового запроса – запрос на Выборку и нажать кнопку Итоги (см. рис.7.13).
Рис.7.13. Создание итогового запроса
Группировка записей в итоговом запросе
Для вычисления в запросе итоговых значений нужно щелкнуть по кнопке Групповые операции на панели инструментов. В бланке запроса появится строка Групповая операция и для всех полей в этой строке будет установлено значение Группировка. Это же значение будет по умолчанию устанавливаться и для добавляемых в бланк запроса полей. Необходимо помнить, что в итоговом запросе Access группирует (объединяет в одну группу) записи с одинаковыми значениями в полях, имеющих в строке Групповая операция значение Группировка. Поэтому, если выполнить запрос, в бланке которого все поля имеют значения Группировка, то будет создан набор записей, включающий по одной записи из каждой группы.
Например, если включить в бланк запроса поле Группа таблицы Студенты, щелкнуть по кнопке Групповые операции и выполнить запрос, то Access выдаст на экран список номеров групп. Однако никаких итогов Access не подведет. Для выполнения этой операции ему нужна следующая информация:
– по каким полям нужно подводить итоги;
– какие итоги нужно подводить по данному полю.
Поэтому в бланке запроса наряду с групповыми полями, по которым производится группировка, должны находиться итоговые поля, причем для каждого итогового поля должен быть указан тип подводимых итогов (сумма, среднее, максимум и т.п.). Итоговым полем может быть как поле таблицы (запроса), находящейся в окне конструктора, так и специально созданное для этой цели вычисляемое поле. Для каждого итогового поля в строке Групповая операция должна быть указана статистическая функция, с помощью которой будет проводиться групповая операция — вычисление итогов в этом поле. Имя нужной функции (Sum, Avg, Max и т.д.) можно ввести в соответствующую ячейку в строке Групповая операция или выбрать из раскрывающегося списка.
Пример. Найти число студентов на каждом курсе.
Добавим в окно конструктора таблицу Студенты, затем щелкнем по кнопке Групповые операции и включим в бланк запроса поле Курс. Оно будет использовано для группировки записей. В качестве итогового поля выберем поле КодСтудента, а в качестве итоговой функции — функцию Count. Дадим итоговому полю имя всего. Окно запроса представлено на рис. 7.14.
Рис. 7.14. Формирование итогового запроса
На рисунке ниже – его результат, количество студентов на каждом курсе (см. рис.7.15):
Рис.7.15. Итоги по курсам
7.7. Перекрестные запросы
Перекрестный запрос — это итоговый запрос специального типа, выводящий результат в виде перекрестной таблицы, похожей на электронную таблицу. Для создания перекрестной таблицы нужно указать:
– заголовки строк — одно или несколько полей, значения которых будут использованы в качестве заголовков строк таблицы;
– заголовки столбцов — поле, значения которого будут использованы в качестве заголовков столбцов;
– значения — числовое поле, значения которого будут использованы для проведения итоговых расчетов;
– групповую операцию — групповую функцию, задающую тип итоговых расчетов, или выражение, содержащее групповые функции. Перекрестные запросы обычно используются при создании диаграмм и отчетов. Сохранить перекрестную таблицу в виде обычной таблицы Access с помощью запроса на создание таблицы нельзя.
Для создания перекрестного запроса можно воспользоваться соответствующим мастером. При этом нужно иметь в виду, что он создает перекрестный запрос на базе полей одной таблицы/запроса, причем группировка проводится по всем записям. Если для создаваемого перекрестного запроса такая таблица/запрос не существует, то следует сначала создать запрос, содержащий всю необходимую информацию, и лишь затем воспользоваться услугами мастера.
7.8. Запросы на изменение
Запросы этого типа позволяют автоматизировать изменения данных в таблицах, а также сохранить результат запроса в виде таблицы Access.
7.8.1 Запрос на добавление
С помощью запроса на добавление можно скопировать данные из одной таблицы (источника) и поместить их в другую таблицу (получатель). Причем можно добавить в таблицу-получатель как записи целиком, так и содержимое отдельных полей таблицы-источника.
Для создания запроса на добавление нужно выполнить следующие действия:
1.Включить в окно конструктора таблицу-источник. Затем выбрать пункт меню Запрос, а потом нажать кнопку Добавление. На экране появится диалоговое окно Добавление (см. рис 7.16).
Рис. 7.16. Запрос на Добавление
2. В поле Имя таблицы указать имя таблицы-получателя и затем нажать кнопку OK. Если таблица находится в другой БД, то нужно перед нажатием кнопки выбрать параметр В другой базе данных и ввести полное имя этой БД.
3. В бланк запроса следует включить:
• поля, участвующие в добавлении;
• поля, для которых задаются условия отбора;
• поле, соответствующее ключевому полю таблицы-
Затем следует ввести условия отбора записей из таблицы-источника в строку Условие отбора.
4. Для просмотра добавляемых записей нужно щелкнуть по кнопке Вид на панели инструментов. Затем следует вернуться в режим конструктора, повторно щелкнув по этой кнопке.
5. Для добавления записей нужно щелкнуть по кнопке Запуск на панели инструментов. Access сообщит число добавляемых записей и попросит подтвердить выполнение операции. После подтверждения записи будут добавлены в таблицу-получатель.
7.8.2. Запрос на удаление
Запрос на удаление позволяет удалить ненужные записи из таблицы. С его помощью можно удалять только всю запись целиком, а не отдельные поля внутри нее. Для создания запроса на удаление нужно выполнить следующие действия:
1. Включить в окно конструктора таблицу, из которой предстоит удалить записи. Затем выбрать пункт меню Запрос, а потом Удаление.
2. Ввести условия отбора удаляемых записей в строку Условие отбора. Эти условия появятся под словом Условие строки Удаление.
3. Для просмотра удаляемых записей нужно щелкнуть по кнопке Вид на панели инструментов. Затем следует вернуться в режим конструктора, повторно нажав эту кнопку.
4. Для удаления записей нужно нажать кнопку Запуск на панели инструментов. Access сообщит число удаляемых записей и попросит подтвердить выполнение операции. После подтверждения записи будут удалены из таблицы.
7.8.3. Запрос на создание таблицы
Запрос на создание таблицы позволяет создать таблицу на основе данных, содержащихся в других таблицах БД. Он часто используется для создания таблицы, предназначенной для экспорта в другую БД Access или архивной копии таблицы. Например, можно создать таблицу со сведениями обо всех старых заказах на книги, прежде чем удалить их из таблицы Заказы. Для создания запроса этого типа нужно выполнить следующие действия:
1. Включить в окно конструктора таблицы или запросы, содержащие записи, которые следует поместить в новую таблицу.
2. Поместить в бланк запроса поля, которые должна содержать новая таблица. Добавить к ним поля, для которых задаются условия отбора. В строке Условие отбора ввести условия выбора нужных записей.
3. Выбрать пункт меню Запрос, а затем Создание таблицы. На экране появится диалоговое Создание таблицы.
4. В поле Имя таблицы ввести имя создаваемой таблицы и затем щелкнуть по кнопке OK. Если таблица находится в другой БД, то нужно перед нажатием кнопки выбрать параметр В другой базе данных и ввести полное имя этой БД.
5. Для предварительного просмотра новой таблицы нужно щелкнуть по кнопке Вид на панели инструментов. Затем следует вернуться в режим конструктора, повторно щелкнув по этой кнопке.
6. Для создания таблицы нужно щелкнуть по кнопке Запуск на панели инструментов. Если в БД уже существует таблица с таким именем, то Access попросит разрешения удалить ее перед началом операции. Затем он сообщит число записей и попросит подтвердить выполнение операции. После подтверждения будет создана таблица.
7.8.4. Запрос на обновление
Этот запрос позволяет внести изменения в группу записей одной или нескольких таблиц. Для создания запроса на обновление нужно выполнить следующие действия:
1. Включить в окно конструктора таблицу или запрос, содержащие записи, которые следует обновить.
2. Выбрать пункт меню Запрос, а затем Обновление. В бланке запроса появится дополнительная строка Обновление (рис.7.17).
Рис. 7.17. Запрос на обновление
3. Поместить в бланк запроса обновляемые поля и добавить к ним поля, для которых задаются условия отбора.
4. В строке Условие отбора ввести условия выбора нужных записей, а в строке Обновление ввести выражения, которые должны быть использованы для изменения полей.
5. Для обновления записей щелкнуть по кнопке Выполнить на панели инструментов. Access сообщит число обновляемых записей и попросит подтвердить выполнение операции. После подтверждения в записи будут внесены новые значения.
Глава 8. Отчеты
8.1. Структура отчета
Отчеты — итоговые документы (печатные формы) по тем или иным показателям и запросам, для которых и создавалась БД. Если форма — документ разработчика и лиц, работающих с информацией в БД, то отчеты — инструмент управленцев (высших менеджеров, директоров), позволяющий им в нужный момент иметь оперативную информацию для принятия правильного решения. Структура отчета (см. рис. 8.1) напоминает структуру формы за одним существенным исключением — возможностью добавления нескольких пар новых разделов, если возникает потребность группировки данных по каким-либо признакам. Итак, структурно отчет состоит из областей:
– Заголовок отчета
– Верхний колонтитул
– Область данных
– Нижний колонтитул
– Примечание отчета
Рис.8.1 . Структура отчета
Возможность группирования данных, - это, пожалуй, главное отличие отчета от формы. Что такое группировка и для чего она нужна? Предположим, что мы строим отчет по таблице, описывающей реализацию товаров различным клиентам. Можно составить отчет по разным «срезам» такой таблицы. Например, составить отчет, характеризующий заказы каждого клиента. В этом случае основа группирования данных — клиенты. Все заказы разбиваются на группы, относящиеся к одному клиенту. Другой вариант — рассмотреть, как реализовывались товары различных видов. В этом случае основа группирования — отдельный товар, а все данные о заказах можно разбить на группы, относящиеся к конкретному товару. Группы могут быть вложенными. Например, в первом случае, внутри группирования по клиентам, можно сгруппировать заказы каждого клиента по отдельным товарам. Каждое группирование сопровождается, как правило, появлением двух разделов — заголовка группы и примечания группы. В заголовке обычно указывается информация о поле — источнике группирования (клиенты, товары) и др. В области примечания можно подвести итоги группирования — число заказов каждого клиента, сумму стоимости всех заказов каждого клиента, число заказов каждого товара и так далее.
Свойства разделов отчета совпадают со свойствами разделов формы, хотя у форм и отчетов существуют различные режимы. Напомним, что форма могла находиться в трех режимах: конструктора, режима формы и предварительного просмотра.
У отчета тоже может быть три режима, причем два из них такие же: режим предварительного просмотра и режим конструктора. Кроме того, отчет может находиться в режиме образца. Этот режим похож на режим предварительного просмотра, но отличается от него тем, что в режиме образца показывается не весь отчет, а только его часть, с целью оценки того, как будет выглядеть весь отчет. Режим, подобный режиму формы, естественно отсутствует, так как с отчетом не работают как с формой или запросом.
Некоторые разделы отчета, такие как колонтитулы и примечания, имеют дополнительные свойства. Так, заголовок отчета может быть напечатан отдельно на первой странице, без колонтитулов. Надо отметить, что в отчете дублируются и многие другие свойства различных объектов, изучавшихся нами в формах. В отчетах, как и в формах, размещаются элементы управления: поля, надписи и т. д. Как и в формах, они могут быть связанными, свободными и вычисляемыми в зависимости от источника информации, реализуемой в элементе управления.
8.2. Создание отчета
Отчеты, как и формы, можно создавать с помощью мастеров или самостоятельно. Источником данных для отчета также являются таблицы и запросы. Если в отчете требуется представить данные из разных таблиц, имеет смысл предварительно создать многотабличный запрос, а затем строить отчет на его основе.
Чтобы создать отчет, нужно в окне выбрать вкладку Создание и выбрать способ создания отчета – Конструктор или Мастер.