Методичка по БД 1480

– 31 –

таблицами базы, мы физически имеем дело с жестким диском, то есть с очень медленным устройством (напомним, что это связано с особенностью сохранения данных, описанной выше). Когда же на основании запроса мы получаем результирующую таблицу, то имеем дело с электронной таблицей, не имеющей аналога на жестком диске – это только образ отобранных полей и записей. Разумеется, работа с «образом» происходит гораздо быстрее и эффективнее – это еще одно основание для того, чтобы широко использовать запросы.

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

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

Работа с запросами. Если структура базы данных предприятия хорошо продумана, то исполнители, работающие с базой, должны навсегда забыть о том, что в базе есть таблицы, а еще лучше, если они об этом вообще ничего не знают. Таблицы – слишком ценные объекты базы, чтобы с ними имел дело кто-либо, кроме разработчика базы.

Если исполнителю надо получить данные из базы, он должен использовать специальные объекты – запросы. Все необходимые запросы разработчик базы должен подготовить заранее. Если запрос подготовлен, надо открыть панель Запросы в окне База данных, выбрать его и открыть двойным щелчком на значке. Появится результирующая таблица, в которой исполнитель найдет то, что его интересует.

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

– 32 –

Внешний вид окна для создания запроса с помощью мастера представлен на рис. 6.

Рис.6. Создание простого запроса с помощью мастера.

В учебных целях запросы лучше готовить вручную, с помощью Конструктора. Как и в случае с таблицами, для этого есть специальный значок в окне База данных. Он называется Создание запроса в режиме конструктора и открывает специальный бланк, именуемый бланком запроса по образцу. За этим длинным названием скрывается тот приятный факт, что, хотя запросы к таблицам баз данных пишутся на специальном языке программирования – SQL, пользователям Microsoft Access изучать его не обязательно, а большинство операций можно выполнить щелчками кнопок мыши и приемом перетаскивания в бланке.

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

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

– 33 –

дают имя и в дальнейшем запускают двойным щелчком на значке в окне

База данных.

Рис.7. Бланк запроса на выборку.

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

Упорядочение записей в результирующей таблице. Если необходимо, чтобы данные, отобранные в результате работы запроса на выборку, были упорядочены по какому-либо полю, применяют сортировку. В нижней части бланка имеется специальная строка Сортировка. При щелчке на этой строке открывается кнопка раскрывающегося списка, в котором можно выбрать метод сортировки: по возрастанию или по убыванию. В результирующей таблице данные будут отсортированы по тому полю, для которого задан порядок сортировки.

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

–34 –

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

выделяют столбец щелчком на его заголовке (кнопку мыши отпускают);

еще раз щелкают на заголовке уже выделенного столбца (но кнопку не отпускают); перетаскивают столбец в другое место.

Управление отображением данных в результирующей таблице. В

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

Использование условия отбора. Дополнительным средством,

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

Рис.8. Пример запроса.

На рис.8 представлен пример, в котором мы хотим отобрать из всех инструментов тот, у которого диаметр более 60 мм.

– 35 –

Другие виды запросов. Мы рассмотрели запросы на выборку. Это самые простые и в то же время наиболее распространенные виды запросов. Однако существуют и другие виды, некоторые из которых выполняются на базе предварительно созданного запроса на выборку. К ним относятся прежде всего:

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

итоговые функции электронных таблиц (производят математические вычисления по заданному полю и выдают результат); запросы на изменение – позволяют автоматизировать заполнение полей таблиц;

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

специфические запросы SQL – запросы к серверу базы данных, написанные на языке запросов SQL.

Можно осуществлять анализ данных с помощью запросов с вычислениями. Для этого вставляется в поле выражение, чтобы отобразить результат выполнения вычисления. Например, выражение [Цена ]*1,25 определяет увеличение цены на четверть.

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

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

Контрольные вопросы

1.Что такое запрос?

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

3.Где и как назначается условие отбора в запросе?

4.В чем отличие между фильтрами и запросами?

5.При удалении полей запроса удаляются ли поля в таблице?

6.Можно ли создать запрос на базе другого запроса?

7.Типы запросов в Access. Как выбрать тип запроса?

8.Как просмотреть результат запроса?

–36 –

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

10.Как создать запрос на обновление части данных в таблице?

Л а б о р а т о р н а я р а б о т а № 4

СОЗДАНИЕ ОТЧЕТОВ ДЛЯ ВЫВОДА ДАННЫХ

В СРЕДЕ MS ACCESS

Созданные средствами MS ACCESS таблицы представляют для пользователя определенные неудобства в работе: ввод данных в таблицу строго регламентирован средствами системы (а это не всегда удобно для пользователя); таблицы не позволяют разместить дополнительную информацию по базе данных (комментарии, надписи, рисунки и т.п.); в таблицах невозможно разместить управляющие элементы системы, кнопки вызова тех или иных процедур обработки событий (например, «Печать», «Сохранить», «Выход» и т. д.).

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

Создание отчета для базы данных возможно двумя способами:

в режиме «Мастера», облегчающего пользователю создание отчета (созданный «мастером» отчет можно доработать в режиме «Конструктора»); в режиме «Конструктора».

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

Рассмотрим создание формы в режиме «Мастера». Вызов этого режима производится выбором позиции меню «Формы» базы данных, затем переходом в позицию «Создание формы при помощи мастера», или нажатием на соответствующую кнопку панели инструментов (см. рис. 1).

Для конструирования отчета в окне базы данных выбираем объект «Отчеты» и нажимаем кнопку «Создать». Вызываем конструктор для разработки отчета.

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

– 37 –

Для создания в заголовке и примечаниях текстовых элементов используем кнопку «Надпись». Для оформления текста можно воспользоваться кнопками панели инструментов форматирования. Кроме того, можно задавать параметры надписей в окне свойств этих элементов. Окно свойств элемента открывается кнопкой панели инструментов «Свойства».

Контрольные вопросы

1.Что представляют собой отчеты в Access?

2.В чем их преимущества над другими методами вывода данных?

3.С чего начинается создание отчета?

4.Какие существуют мастера для создания отчета?

5.В чем отличие автоотчетов от других способов создания отчета?

6.Чем характеризуется режим «Конструктора»?

7.Назовите разделы отчета.

8.Назовите инструменты окна отчета.

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

10.Как добавить вычисляемое поле в область данных?

11.Для чего применяется свойство объекта «Не выводить повторы»?

12.Наличие каких объектов необходимо для внедрения подчиненного отчета?

13.Какие изменения можно внести в отчет в режиме предварительного просмотра?

Л а б о р а т о р н а я р а б о т а № 5

СОЗДАНИЕ СТРАНИЦ ДОСТУПА К ДАННЫМ И МАКРОСОВ В СРЕДЕ MS ACCESS

Страницы. Это специальные объекты баз данных, реализованные в СУБД Microsoft Access. Правда, более корректно их называть страницами доступа к данным. Физически это особый объект, выполненный в коде HTML, размещаемый на Web-странице и передаваемый клиенту вместе с ней. Сам по себе этот объект не является базой данных, но содержит компоненты, через которые осуществляется связь переданной Web-страницы с базой данных, остающейся на сервере. Пользуясь этими компонентами, посетитель Web-узла может просматривать записи базы в полях страницы доступа. Таким образом, страницы доступа к данным осуществляют интерфейс между клиентом, сервером и базой данных, размещенной на сервере. Эта база не обязательно должна быть базой данных Microsoft Access. Страницы доступа, созданные средствами Microsoft Access, позволяют работать также с базами данных Microsoft SQL Server.

– 38 –

Макросы и модули. Эти категории объектов предназначены как для автоматизации повторяющихся операций при работе с системой управления базами данных, так и для создания новых функций путем программирования. В СУБД Microsoft Access макросы состоят из последовательности внутренних команд СУБД и являются одним из средств автоматизации работы с базой. Модули создаются средствами внешнего языка программирования, в данном случае языка Visual Basic for Applications. Это одно из средств, с помощью которых разработчик базы может заложить в нее нестандартные функциональные возможности, удовлетворить специфические требования заказчика, повысить быстродействие системы управления, а также уровень ее защищенности.

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

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

Для создания макроса в окне базы данных нужно выбрать вкладку «Макросы» и нажать на кнопку «Создать». Появится окно конструктора макросов. Оно состоит из двух столбцов: «Макрокоманда» и «Примечание». В столбце «Макрокоманда» перечисляются команды, подлежащие выполнению. Чтобы в ячейке столбца «Макрокоманда» задать нужную макрокоманду, ее следует выбрать из списка.

Столбец «Примечание», содержит комментарии к командам. При выполнении макроса он игнорируется, однако его заполнение делает текст макроса понятнее.

Выполнение каждой макрокоманды зависит от ее аргументов (некоторые макрокоманды не имеют аргументов). Аргументы вводятся в

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

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

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

– 39 –

ячейку макрокоманды в окне макроса (рис.9).

Рис.9. Вид окна макроса

Для этого следует расположить окно базы данных и окно макроса рядом на экране с помощью команды «Сверху вниз» или «Слева направо» из меню «Окно». Затем выбрать в окне базы данных соответствующую вкладку, выбрать объект и переместить его значок с помощью мыши в ячейку макрокоманды. Например, для того чтобы создать макрос, открывающий форму, следует переместить с помощью мыши значок формы из окна базы данных в ячейку макрокоманды.

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

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

Перед запуском макрос необходимо сохранить. Выберите команду «Файл» – «Сохранить как»/ «Экспорт». Откроется окно диалога. Введите имя макроса и нажмите ОК.

– 40 –

Варианты запуска макросов:

–чтобы запустить макрос из окна конструктора макроса, нужно нажать кнопку

–чтобы запустить макрос из окна базы данных, следует выбрать вкладку «Макросы», активизировать имя макроса и нажать кнопку «Запуск» в окне базы данных (можно также выполнить двойной щелчок на имени запускаемого макроса);

–чтобы запустить макрос из любого режима Microsoft Access, нужно выбрать команду Сервис\ Макрос\ Запуск макроса и указать имя макроса.

ВAccess 97 предусмотрена возможность выполнения макроса в пошаговом режиме. Для этого предназначена кнопка «По Шагам» на панели инструментов «Конструктор макросов».

Вэтом режиме перед выполнением каждой макрокоманды происходит останов, который сопровождается появлением диалогового окна Пошаговое выполнение макроса.

Кнопка «Шаг» позволяет выполнить следующую макрокоманду, а кнопка «Продолжить» – все последующие шаги без остановок, то есть выключить пошаговый режим. Режим пошагового выполнения позволяет найти логические ошибки, допущенные при записи макроса.

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

Виды ошибок в макросах Макрос не всегда функционирует так, как было задумано. Можно выделить две категории ошибок в макросах: синтаксические; логические.

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

Синтаксические ошибки легко распознаются программой Access, которая выдает сообщение о них в окне «Ошибка выполнения макрокоманды». Необходимо при помощи кнопки «Прервать» в этом окне остановить выполнение макроса и исправить ошибку.

Логические ошибки не могут быть обнаружены программой, их поиск