Перекрестные запросы. Макросы
Лекция 18. Перекрестные запросы. Макросы
С помощью перекрестного запроса можно более наглядно представить данные итоговых запросов, предусматривающих группировку по нескольким признакам (по двум, в частности).
В этом случае значение полей по первому признаку группировки могут стать заголовками строк, а по второму - заголовками столбцов.
Для преобразования итогового запроса-выборки необходимо перейти в режим его Конструктора и выполнить команду Запрос/Перекрестный.
После этого в бланке запроса строка «Вывод на экран» будет замещена строкой «Перекрестная таблица». В этой строке нужно задать, как будет использовано данное поле в перекрестной таблице: в виде заголовок строк, полей или значений (рисунок 18.1).
Рисунок 18.1 - Вид окна перекрестного запроса
Существует возможность вывести данные в перекрестной таблице без создания в базе данных отдельного запроса. Для этого следует использовать «Мастер сводных данных». В сводной таблице пользователь имеет возможность изменять заголовки строк или столбцов, что позволяет анализировать данные различными способами.
В перекрестном запросе отображаются результаты статистических расчетов (такие как суммы, количество записей и средние значения), выполненных по данным из одного поля таблицы. Эти результаты группируются по двум наборам данных, один из которых расположен в первом столбце таблицы, а второй - в верхней строке.
Рекомендуемые материалы
Удобно создавать перекрестную таблицу в интерактивном режиме с помощью мастера «Перекрестный запрос». Обращение к Мастеру значительно упрощает процесс создания перекрестной таблицы.
Мастеру необходимо указать исходную таблицу, которая может быть таблицей из базы данных или являться результатом выборки данных из нескольких таблиц. На основе исходной таблицы или запроса Мастер создает итоговую выборку, в которой группирует и сортирует данные по полям, используемым в качестве строки и столбца перекрестной таблицы, а также вычисляет итоговое значение по заданному полю данных.
При создании перекрестной таблицы следует перейти на вкладку «Запросы» и выбрать кнопку «Создать» , а затем в окне диалога выбрать опцию Перекрестный запрос (рисунок 18.1).
Эти действия запустят Мастер создания перекрестной таблицы, который шаг за шагом будет задавать вопросы о параметрах создаваемой таблицы.
На первом шаге Мастер предлагает выбрать исходную таблицу или запрос.
На следующем шаге вам необходимо указать поля, значения которых будут использоваться в качестве заголовков строк. Можно указать от одного до трех полей путем их переноса их переноса из списка Доступные поля в список Выбранные поля. Затем вы переходите в окно диалога определения заголовков столбцов. В качестве заголовка столбцов может использоваться только одно поле.
На предпоследнем шаге создания перекрестной таблицы нужно задать поле, которое будет использоваться в качестве источника данных для итоговых вычислений, и тип итоговых вычислений для каждой пары строка/столбец (рисунок 18.2).
Рисунок 18.2 - Вид окна для определения типа группировки
В этом же окне диалога можно добавить столбец, который будет содержать итоговое значение по каждой строке результирующей таблицы.
На завершающей стадии создании перекрестной таблицы нужно задать ее имя и выбрать опцию просмотра результатов запроса или изменения структуры запроса в окне конструктора запросов.
Для завершения процесса создания необходимо нажать кнопку «Готово», на экране появится построенная перекрестная таблица.
В перекрестной таблице, созданной с помощью Мастера, присутствует дополнительный столбец Итого, в котором отображаются суммарные итоги данных.
Макросы
Макросом называют набор из одной или более макрокоманд, выполняющих определенные операции, такие как открытие форм или печать отчетов. Макросы могут быть полезны для автоматизации часто выполняемых задач. Например, при нажатии пользователем кнопки можно запустить макрос, который распечатает отчет.
Макрос может быть как собственно макросом, состоящим из последовательности макрокоманд, так и группой макросов, т.е. набором связанных макрокоманд имеющих общее имя. В некоторых случаях для решения, должна ли в запущенном макросе выполняться определенная макрокоманда, может применяться условное выражение.
Запуск окна создания макросов осуществляется кнопкой «Создать», в окне диалога «База данных» при выбранной категории «Макросы» внешний вид окна «Макрос» представлен на рисунке 18.3.
Рисунок 18.3 - Окно диалога «Макрос»
В верхней части раскрывшемся окне «Макрос» имеется два столбца: «Макрокоманда» и «Примечание». В столбце «Макрокоманда» осуществляется выбор макроса из раскрывающегося списка, в котором более 50 различных макрокоманд, составляющих базовый набор MS Access. Создаваемый макрос может включать в себя множество макрокоманд, каждая новая команда должна записываться в новой строке.
Заполнение столбца «Примечание» не является обязательным, однако в большинстве случаев его заполнение позволяет быстрее разобраться в действиях макроса, особенно когда в нем множество макрокоманд.
В большинстве случаев макрокоманды имеют аргументы, в этом случае они отображаются в нижней части окна в разделе «Аргументы макрокоманды». В общем случае рекомендуется задавать аргументы макрокоманды в том порядке, в котором они перечислены, поскольку выбор одного аргумента может определять возможные значения следующего аргумента.
В некоторых случаях, при создании сложных макросов может потребоваться ветвление, т.е. использование условий, например, выполнять или не выполнять ту или иную макрокоманду в зависимости от ситуации. В этом случае потребуется добавление нового столбца «Условие» в бланк макрокоманд. Добавить столбец «Условие» можно командой Вид - Условие. Условие можно создать через построитель выражений командой контекстного меню – Построить.
Таблица 18.1 – Макрокоманды MS Access
|
| Макрос | Аргументы | Краткое описание |
| 1 | Восстановить | - | Восстановление исходных размеров активного окна |
| 2 | ВывестиВФормате | ||
| 3 | ВыводНаЭкран | Включить вывод, текст строки состояния | Управляет отображением вывода результатов выполнения макросов на экран |
| 4 | ВыделитьОбъект | Тип объекта, имя объекта, в окне БД (да/нет) | Делает активным объект БД |
| 5 | ВыполнитьКоманду | Команда меню | Выполнение команды меню MS Access, команда должна соответствовать текущему состоянию |
| 6 | Выход | Режим сохранения | Выход из MS Access |
| 7 | ДобавитьМеню | Название пункта, имя выполняемого макроса, текст строки состояния | Добавление пункта к главному или контекстному меню |
| 8 | ЗадатьЗначение | Элемент которому присваивается значение, выражение | Задание значения для элемента управления (например, поле) |
| 9 | ЗадатьКомандуМеню | Индексы меню, команды, подкоманды, состояние пункта меню | Изменяет состояние пункта меню созданного макросом |
| 10 | Закрыть | Тип окна (форма, запрос и т.д.), имя окна, режим закрытия | Закрытие указанного окна |
| 11 | ЗапускЗапросаSQL | Строка – запрос, использовать транзакцию (да/нет) | Запускает запрос SQL, указанный аргументом |
| 12 | ЗапускМакроса | Имя макроса, количество повторов запуска, условие повтора | Запускает указанный макрос |
| 13 | ЗапускПриложения | Полное имя приложения (командная строка) | Запускает приложение указанное аргументом |
| 14 | ЗапускПрограммы | Имя функции Visual Basic | Запуск программы Visual Basic |
| 15 | КомандыКлавиатуры | Клавиши, необходимость задержки на время обработки (да/нет) | Передает нажатие указанных клавиш MS Access или другим приложениям |
| 16 | КопироватьОбъект | Имя БД, новое имя объекта, тип объекта, старое имя | Копирование объекта в туже или другую БД |
| 17 | КэлементуУправления | Имя элемента управления | Перемещение фокуса ввода к элементу управления |
| 18 | НаЗапись | Тип объекта, имя объекта, к какой записи (следующая, первая и т.д.), номер записи или смещение | Переход к указанной записи формы, таблицы или запроса |
| 19 | НайтиЗапись | Образец поиска, условие совпадения, учет регистра, область поиска, учет формата поля, по каким полям, какое вхождение (первое, последнее и т.д.) | Поиск записи по условию на форме или в таблице |
| 20 | НаСтраницу | Номер страницы, левый отступ, правый отступ страницы | Вывод указанной страницы формы (используется при построении многостраничных форм) |
| 21 | ОбновитьОбъект | Тип объекта, имя объекта | Обновление объекта экрана (форма, таблица и т.д.) |
| 22 | Обновление | Имя элемента | Обновление элемента управления (его таблиц, или вычислений) |
| 23 | ОстановитьВсеМакросы | - | Требуется для остановки работы всех макросов |
Продолжение таблицы 18.1
|
| Макрос | Аргументы | Краткое описание |
| 24 | ОстановитьМакрос | - | Остановка макроса выполняемого в данный момент |
| 25 | ОткрытьЗапрос | Имя запроса, режим вывода (констр. и т.д.), режим работы (изменение, чтение и т.д.) | Открытие запроса в различных режимах |
| 26 | ОткрытьМодуль | Имя модуля, имя процедуры | Открытие модуля Visual Basic |
| 27 | ОткрытьОтчет | Имя отчета, режим (просмотр, печать, костр.), имя фильтра, условие отбора записей | Открывает отчет в различных режимах |
| 28 | ОткрытьПредставление | Имя представления, режим (просмотр, таблица и т.д.), режим данных (изменение, только чтение и т.д.) | Открытие представления в различных режимах |
| 29 | ОткрытьСохраненнуюПроцедуру | Имя процедуры, режим, режим данных | Открытие сохраненной процедуры в различных режимах |
| 30 | ОткрытьСтраницуДоступаКДанным | Имя страницы, режим | Открытие страницы доступа к данным в различных режимах |
| 31 | ОткрытьСхему | Имя схемы | Открытие схемы БД |
| 32 | ОткрытьТаблицу | Имя таблицы, режим, режим данных | Открытие таблицы БД в различных режимах |
| 33 | ОткрытьФорму | Имя формы | Открывает форму с указанным именем |
| 34 | ОтменитьСобытие | - | Отменяет событие запустившее данный макрос |
| 35 | ОтправитьОбъект | ||
| 36 | ПанельИнструментов | Имя панели, показ (да/нет) | Управляет отображением панелей инструментов |
| 37 | Переименовать | Новое имя, тип объекта, старое имя | Переименование указанного объекта |
| 38 | ПесочныеЧасы | Включить (да/нет) | Устанавливает указатель мыши в виде песочных часов |
| 39 | Печать | ||
| 40 | ПоказатьВсеЗаписи | - | Отмена фильтров для форм, таблиц и запросов |
| 41 | ПрименитьФильтр | Имя фильтра, условие отбора | Применяет фильтр к форме или отчету или предложение WHERE (SQL) |
| 42 | Развернуть | - | Развернуть активное окно до максимальных размеров |
| 43 | Свернуть | - | Свернуть активное окно и поместить его значком в нижней части Access |
| 44 | СдвигРазмер | Отступы слева, сверху, высота, ширина | Смещение или изменение размеров активного окна |
| 45 | Сигнал | - | Выдача звукового сигнала |
| 46 | СледующаяЗапись | - | Поиск следующей записи по условию заданному макросом «Найти запись» |
| 47 | Сообщение | Строка сообщения, сигнал (да/нет), тип окна, заголовок окна | Вывод диалогового окна с сообщением |
| 48 | Сохранить | Тип объекта, имя объекта | Сохранение объекта |
| 49 | УдалитьОбъект | Тип объекта, имя объекта | Удаление объекта |
| 50 | УстановитьСообщения | Включить сообщения (да/нет) | Включение или выключение системных сообщений в т.ч. сообщений об ошибках |
Контрольные вопросы
1. В чем заключается назначение перекрестного запроса?
2. Чем связаны перекрестные запросы и итоговые запросы?
3. Что такое макрос?
4. В чем назначение макросов?
5. Какие средства существуют для создания макроса?
6. Какие макросы вы знаете?
7. Как запускается макрос?
Задания для самостоятельной работы
Задание 1. Создайте перекрестный запрос для учета часов учебной работы преподавателя по описанию.
Пусть требуется вести учет часов учебной работы преподавателя для учебной части. Фрагмент бланка учета часов представлен в таблице 18.2. Определим схему БД для одного преподавателя (рисунок 18.4).
Таблица 18.2 – Фрагмент бланка учета часов преподавателя
| Наименование дисциплин | Группа | План на текущий год | В том числе | Фактически выполнено | ИТОГО | ||||||||||
| теоретических | лабораторно-практических | IX | X | XI | XII | I | II | III | IV | V | VI | ||||
| ТСИ | 2Т1 | 64 | 44 | 20 | 0 | 0 | 0 | 0 | 0 | 12 | 4 | 16 | 26 | 6 | 64 |
| ТСИ | 2Т2 | 64 | 44 | 20 | 0 | 0 | 0 | 0 | 0 | 8 | 4 | 22 | 18 | 12 | 64 |
| ТСИ | 2Т3 | 64 | 44 | 20 | 0 | 0 | 0 | 0 | 0 | 2 | 10 | 16 | 22 | 14 | 64 |
| БД | 3Т1 | 90 | 60 | 30 | 20 | 26 | 12 | 0 | 0 | 0 | 0 | 12 | 16 | 4 | 90 |
| БД | 3Т2 | 90 | 60 | 30 | 22 | 24 | 14 | 0 | 6 | 6 | 0 | 2 | 4 | 12 | 90 |
| БД | 3Т3 | 90 | 60 | 30 | 20 | 20 | 16 | 4 | 6 | 6 | 2 | 0 | 0 | 16 | 90 |
| РЭАИС | 3Т1 | 58 | 38 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 6 | 12 | 32 | 58 |
| РЭАИС | 3Т2 | 58 | 38 | 20 | 0 | 0 | 0 | 0 | 12 | 10 | 2 | 6 | 18 | 10 | 58 |
Рисунок 18.4 – Схема БД «Учет часов учебной работы»
После создания таблиц и установки связей выполним следующие действия:
1. С помощью мастера создадим запрос на выборку, содержащий только те поля, которые должны войти в перекрестный запрос (рисунок 18.5) и присвоим ему имя «Сводный_запрос».
2. Выполним команду Создать – Перекрестный запрос, в результате откроется окно диалога «Создание перекрестных таблиц».
3. На первом этапе выбираем источник данных – «Сводный_запрос» и нажимаем кнопку «Далее».
4. На втором шаге в запрос включаем те поля, которые будут использоваться в качестве заголовков строк, таких полей может быть несколько, например, «Дисциплина», «Группа», «План на год». Поля, которые хотелось бы также разместить в заголовках строк, но мастер этого не позволяет, можно в последствии добавить в конструкторе запросов. Нажимаем кнопку «Далее».
Рисунок 18.5 – Запрос на выборку «Сводный запрос»
5. На третьем шаге выберем поле, значения которого требуется разместить в качестве заголовков столбцов, в данном примере это поле «Месяц». Нажимаем кнопку «Далее».
6. На четвертом шаге выберем поле, по которому требуется провести вычисления на пересечении строк и столбцов, поле «Отведено». Также требуется определить функцию, по которой будет проводиться вычисление итоговых значений, в данном случае «Сумма». Нажимаем кнопку «Далее».
7. На последнем шаге можно задать имя, например, «Учет часов учебной работы» и способ дальнейшее работы (мы планировали добавить в запрос поля, не «вместившиеся» при использовании мастера). Нажимаем кнопку «Готово».
Обратите внимание на лекцию "Часть 4".
Рисунок 18.6 – Перекрестный запрос «Учет часов учебной работы»
Результат работы запроса после некоторых модификаций представлен на рисунке 18.6. Несложно создать для него соответствующий отчет.
Задание 2. Модифицируйте базу данных «Учтет часов учебной работы так, чтобы можно было учитывать данные многих преподавателей.
Задание 3. Разработайте перекрестный запрос учета успеваемости групп по разным предметам, по итогам выполнения контрольных работ
