РАБОТА 2. ФОРМИРОВАНИЕ ЗАПРОСОВ И ОТЧЕТОВ ДЛЯ ОДНОТАБЛИЧНОЙ БАЗЫ ДАННЫХ

КРАТКАЯ СПРАВКА

Формирование запросов на выборку

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

Запросы можно создавать самостоятельно и с помощью мастеров. Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. Самостоятельно разработать запросы можно в режиме конструктора.

В Access 97 можно создавать следующие типы запросов:

• запрос на выборку;
• запрос с параметрами;
• перекрестный запрос;
• запрос на изменение (запрос на удаление, обновление и добавление записей на создание таблицы);
• запросы SQL (запросы на объединение, запросы к серверу, управляющие запросы, подчиненные запросы)

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

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

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

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

Рис. 4.8. Окно выбора вариантов построения запросов

Для создания нового запроса надо в окне базы данных выбрать вкладку Запросы и щелкнуть по кнопке <Создать>. Откроется окно "Новый запрос", вид которого представлен на рис. 4.8.

В окне вы должны выбрать один из пяти пунктов: Конструктор, Простой запрос, Перекрестный запрос, Повторяющиеся записи. Записи без подчиненных. Конструктор позволит вам самостоятельно создать любой тип запроса, но этот режим рекомендуется пользователям, уже имеющим некоторый опыт создания запросов. Простой запрос позволит создать с помощью Мастера запрос на выборку из определенных полей таблиц или других запросов. (Это наилучший способ создания запроса для начинающих пользователей.) Что такое перекрестный запрос, рассматривалось выше. При выборе пункта Повторяющиеся записи будет создан запрос на поиск повторяющихся записей в простой таблице или в запросе, а при выборе пункта Записи без подчиненных - запрос на поиск записей, которым не соответствует ни одна запись в подчиненной таблице. Такой запрос используется для многотабличных баз данных.

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

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

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

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

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

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

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

Для сохранения запроса следует выполнить следующие действия. Выполните команду Файл, Сохранить или щелкните по кнопке <Сохранить> на панели инструментов. Если вы впервые сохраняете запрос, введите новое имя запроса в диалоговом окне "Сохранение".

Формирование отчетов

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

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

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

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

Для создания отчета надо открыть вкладку Отчеты и щелкнуть по кнопке <Создать>. Откроется окно "Новый отчет", в котором приведены шесть пунктов меню, т.е. шесть способов создания отчета: Конструктор, Мастер отчетов, Автоотчет в столбец. Автоотчет ленточный, Мастер диаграмм и Почтовые наклейки. Конструктор позволит вам самостоятельно создать отчет, но это непросто даже для опытного пользователя. Мастер отчетов автоматически создаст отчет на основе выбранных вами полей таблиц (запросов) и макетов отчетов. Этот способ создания отчетов является наиболее удобным как для начинающих, так и для опытных пользователей. Автоотчет в столбец и Автоотчет ленточный - самые простейшие способы создания отчетов: достаточно указать только имя таблицы (запроса), на основе которого будет создан отчет, а остальное сделает мастер отчетов. Мастер диаграмм поможет создать отчет в виде диаграммы, а Почтовые наклейки создадут отчет, отформатированный для печати почтовых наклеек.

ЗАДАНИЕ 1

Формирование запросов на выборку.

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

2. Данные запроса отсортируйте по должностям.

3. Сохраните запрос.

4. Создайте запрос на выборку с параметром, в котором должны отображаться фамилии, имена, отчества преподавателей и преподаваемые ими дисциплины, а в качестве параметра задайте фамилию преподавателя и выполните этот запрос для преподавателя Гришина.

ТЕХНОЛОГИЯ РАБОТЫ

1. Для создания простого запроса:

• в окне базы данных откройте вкладку Запросы;
• в открывшемся окне щелкните по кнопке <Создать>;
• из появившихся пунктов окна "Новый запрос" выберите Простой запрос и щелкните по кнопке <ОК>;
• в появившемся окне в строке Таблицы/запросы выберите таблицу Преподаватели (если других таблиц или запросов не было создано, она будет одна в открывающемся списке);
• в окне "Доступные поля" переведите выделение на параметр Фамилия,
• щелкните по кнопке. Слово Фамилия перейдет в окно "Выбранные поля";
• аналогично в окно "Выбранные поля" переведите поля "Имя", "Отчество", "Должность" (порядок важен - в таком порядке данные и будут выводиться);
• щелкните по кнопке. <Далее>;
• в строке параметра Задайте имя запроса введите новое имя Должности преподавателей;
• щелкните по кнопке <Готово>. На экране появится таблица с результатами запроса.

2. Для сортировки данных:

• щелкните в любойстроке поля "Должность";
• отсортируйте данные по убыванию. Для этого щелкните по кнопке  на панели инструментов или выполните команду Записи, Сортировка, Сортировка по убыванию.

3. Для сохранения запроса:

• щелкните по кнопке  или выполните команду Файл, Сохранить;
• закройте окно запроса.

4. Длясоздания запроса на выборку с параметром:

• создайте запрос на выборку для следующих полей таблицы Преподаватели: "Фамилия", "Имя", "Отчество", "Преподаваемая дисциплина". Запрос создавайте аналогично тому, как это делалось в п.1;
• задайте имя запросу Преподаваемые дисциплины;
• щелкните по кнопке <Готово>. На экране появится таблица с результатами запроса;
• перейдите в режиме конструктора, щелкнув по кнопке или выполнив команду Вид, Конструктор;
• в строке параметра Условия отбора для поля "Фамилия" введите фразу (скобки тоже вводить): [Введите фамилию преподавателя]
• выполните запрос, щелкнув по кнопке  на панели инструментов или выполнив команду Запрос, Запуск;

Примечание. Вышеописанным способом запрос выполняется только в режиме конструктора. Для того чтобы выполнить запрос из другого режима, надо открыть вкладку Запросы, выделить требуемый запрос и щелкнуть по кнопке <Открыть>.

• в появившемся окне введите фамилию Гришин и щелкните по кнопке <ОК>. На экране появится таблица с данными о преподавателе Гришине - его имя, отчество и преподаваемая им дисциплина;
• сохраните запрос;
• закройте окно запроса.

ЗАДАНИЕ 2

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

ТЕХНОЛОГИЯ РАБОТЫ

Для создания отчета:

• откройтевкладку Отчеты и щелкните по кнопке <Создать>;
• в открывшемся окне выберите пункт Мастер отчетов;
• щелкните по значку раскрывающегося списка в нижней части окна;
• выберите из появившегося списка таблицу Преподаватели;
• щелкните по кнопке <OK>, В появившемся окне выберите поля, которые будут присутствовать в форме. В данном примере присутствовать будут все поля из таблицы, поэтому щелкните по кнопке
• щелкните по кнопке <Далее>;
• в появившемся окне присутствует перечень полей. Переведите выделение на поле "Должность";
• щелкните по кнопке. Таким образом вы задаете группировку данных по должности;
• щелкните по кнопке <Далее>;
• параметры появившегося окна оставим без изменений, поэтому щелкните по кнопке <Далее>;
• в появившемся окне выберите стиль оформления отчета;
• щелкните по кнопке. <Далее>;
• в появившемся окне введите название отчета Преподаватели;
• щелкните по кнопке <Готово>. На экране появится сформированный отчет;
• просмотрите, а затем закройте отчет.