Лабораторная работа № 7. Основы работы в СУБД Microsoft Access. Создание и использование запросов.

1. ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

Цель работы1

Данная лабораторная работа предназначена для приобретения начальных навыков работы с запросами реляционной базы данных, а именно:

а) использование простейших логических операндов,

б) приобретение практики выборки заданной информации,

в) изучение построения математического выражения в структуре запрос,

г) изучение групповых операций в запросах.

1.1. Назначение и типы запросов

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

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

Спектр возможностей, которыми обладает Access для обработки данных при помощи запросов, определяется разными типами запросов. Рассмотрим некоторые из них.

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

Запрос с параметром – это «интерактивный» тип запроса, при выполнении отображающий в собственном диалоговом окне приглашение ввести один или ряд параметров, например, условие отбора записей по определенному полю.

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

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

При создании запросов используется специальный язык программирования SQL (Structured Query Language). Это формальный стандартизованный язык высокого уровня, содержащий средства непроцедурной обработки (не требующей программирования) спецификации запросов. Однако пользователи Access могут не изучать этот язык. Вместо него в Access есть простое средство – бланк запроса по образцу. С его помощью можно сформировать запрос простыми приемами, перетаскивая элементы запроса между окнами. Делать это удобнее и понятнее с помощью Конструктора запросов, т.е. вручную, хотя для опытных пользователей можно воспользоваться помощью Мастера запросов. 

1.2. Выражение и его компоненты

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

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

1.3. Операторы

Операторы позволяют выполнить некоторые действия над одним или несколькими компонентами выражения. Программа Access поддерживает шесть типов операторов.

• § Арифметические операторы. Выполняют привычные математические операции с числовыми значениями: сложения (+), вычитания (-), умножения (*), и деления (/). Кроме того, к этой категории относят оператор целочисленного деления первого операнда на второй (\), оператор деления по модулю MOD (остаток целочисленного деления одного операнда на другой) и возведения в степень (^). В роли операндов могут быть как числа, так и значения полей или выражения.

Пример для оператора деления нацело: 25,6\3,8 = 6 (числа сначала округляются до целых значений, а затем вычисляется частное двух чисел, которое также округляется до целых).

Пример арифметического выражения для формулы вычисляемого поля:

СреднийБалл: ([1_e1]+[1_e2])/2

• § Операторы сравнения. Служат для сравнения двух операндов и возвращают в зависимости от отношения между операндами, логические значения (True или False) или Null (если, хотя бы один из операндов имеет значение Null). К этому типу относятся следующие операторы: > (больше чем), < (меньше чем), >= (больше или равно, <= (меньше или равно), = (равно), <> (не равно).
• § Логические (булевы) операторы. Так как в качестве операндов таких операторов могут быть значения, которые либо ложны, либо истинны, логические операторы возвращают логические значения (True или False) или Null.  Как правило, используются для комбинирования результатов выполнения двух и более операций сравнения. Список логических операторов включает And (Логическое И), Or (Включающее ИЛИ), Not (Логическое НЕ), Xor (Исключающее ИЛИ). Все логические операторы, за исключением Not, всегда работают с двумя операндами.

Например, Not”аспирант”. Будут отобраны все записи о преподавателях, имеющих в поле Должность любые значения, за исключением аспирант.

• § Операторы конкатенации. Оператор & служит для объединения нескольких строк символов в одной строку. Обрабатывает все переменные как символьные строки.
• § Операторы идентификации. Программа Access работает с двумя операторами идентификации: ! (восклицательный знак) и . (точка). С помощью этих операторов можно обращаться к конкретным объектам, например, к полю таблицы. Символ «!» используется вместе с различными зарезервированными словами, например, Forms, указывая на то, что далее следует имя формы. Так, например, идентифицировать форму Учетная карта, используется выражение Forms![Учетная карта], так как в базе данных могут быть другие объекты с таким именем, а именно: таблица  Учетная карта. Синтаксис выражения следующий: КлассОбъета!ИмяОбъекта.

Символ «.»(точка) отделяет имена объектов от их свойств или методов (синтаксис задан иначе: КлассОбъекта!ИмяОбъекта.Свойство или КлассОбъекта!ИмяОбъекта.Метод).

Например, Forms![Ведомость]![1_p].DefaultValue.

• Прочие операторы. Это операторы языка SQL, такие как Like, Is, In, Between…And, с помощью которых можно упростить создание выражений. Возвращают значение True или False.

u  Is. Используется в выражениях Is Null или Is Not Null. Определяет наличие или отсутствие значения Null, т.е. является ли объект пустым.

u  Like. Проверяет, соответствует ли строковое значение заданному шаблону. Его ставят впереди заданного фрагмента, а до или после фрагмента, в этом случае, можно использовать 5 символов подстановки: *(любое число символов), ?(любой одиночный символ), #(любая цифра), [список](любой символ из списка) и [!список](любой символ, не принадлежащий указанному списку). Пример использования этого оператора будет рассмотрен в ЗАДАНИИ 11.

u  In. Проверяет, совпадает ли значение с одним из элементов, указанных в списке. Например, критерий отбора может иметь следующий вид: In(“А1”;”Б1”). Согласно этому критерию будут отобраны записи, содержащие в поле №гр значение А-1 или Б-1 (тире в названии группы указывать не надо, т.к. этот символ заложен в маску ввода).

u  Between…And. Определяет, принадлежит ли числовое значение заданному диапазону значений. Пример в ЗАДАНИИ 11.

1.4. Литералы. Идентификаторы. Функции

Литералы

 Литералы – это используемые в Access значения в их явном представлении. Литералы бывают следующих типов.

• Числовые. Вводятся как ряд чисел, могут содержать знак разделителя (в десятичном числе) и знак «минус» (–) для отрицательных значений, символы «Е» и «е», а также знак показателя степени (при экспоненциальной форме представления чисел). Например: 3,4567Е-01, 12000,-25.
• Текстовые (строковые). Включают любые печатные символы (А–Я, A–Z, числа от 0 до 9, знаки пунктуации и специальные символы клавиатуры, а также непечатаемые символы, например, перевода каретки (задаются с помощью функции Chr()). Строковые литералы следует заключать в двойные кавычки ("  "). Например: "Иванов".
• Литералы даты и времени. В программе Access знак номера (#) ставится  до и после любой даты. Если при вводе в таблицу значение даты указывается в стандартной формате, распознаваемом Access, или  определяется в качестве критерия отбора в бланке запроса, указывать эти знаки необязательно. Например, #01.03.00#, 15-янв-2004.

Идентификаторы

 Идентификаторы – это имена объектов (баз данных, таблиц, полей, запросов, форм и отчетов). Используемые в выражениях, идентификаторы возвращают определенные числовые или текстовые значения: т.е. представляют собой ссылку на текущее значение поля, элементы управления или свойства. Например, такой идентификатор, как Forms![Ведомость]![1_p].DefaultValue  определяет ссылку на значение свойства Значение по умолчанию (DefaultValue)  элемента управления 1_р в форме Ведомость.

 Функции

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

• Функции даты и времени.

Date(). Отображает текущую дату в формате дд.мм.гг.

DateAdd("d";15;[Абонемент]![Дата выдачи]). Для БД Библиотека возвращает дату, на 15 дней отстоящую от даты, заданной значением поля Дата выдачи таблицы Абонемент.

DateDiff ("d";[Дата возврата];[Дата выдачи]). Возвращает значение, представляющее разницу числа дней между значениями полей Дата возврата и Дата выдачи.

Year(#23.02.04#). Возвращает число, представляющее год в указанной дате: 2004.

• Функции обработки текста.

InStr("Андреева";"е"). Возвращает число, указывающее позицию первого вхождения одной строки "е" в другую строку "Андреева": 5.

LCase([ФИО]) Возвращает строку, преобразованную к нижнему регистру.

Left([ФИО],2). Отображает два первых символа значения поля ФИО.

Right([ФИО],5). Отображает 5 последних символов значения поля ФИО.

• Функции преобразования типа данных.

Val(“1234.56”). Возвращает число, содержащееся в строке 1234.56.

Str(123,45). Возвращает строковое представление числа “123.45”.

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

1.5. Использование Построителя выражений

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

Структурно окно построителя состоит из нескольких областей (Рис. 25).

Рис. 25. Диалоговое окно построителя выражений

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

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

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

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

Запросы на выборку

В запросах на выборку данные могут: отбираться по многим критериям; сортироваться; с ними могут производиться вычислительные операции.

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

2. «СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ЗАПРОСОВ»

Цель работы1

Данная лабораторная работа предназначена для приобретения начальных навыков работы с запросами реляционной базы данных, а именно:

а) использование простейших логических операндов,

б) приобретение практики выборки заданной информации,

в) изучение построения математического выражения в структуре запрос,

г) изучение групповых операций в запросах.

2.1. ЗАДАНИЕ 9. Построение запроса на выборку данных из нескольких таблиц

1. Создание запроса начинается с открытия вкладки Запросы диалогового окна База данных и щелчка на кнопке Создать. В открывшемся диалоговом окне Новый запрос задают ручной режим создания запроса выбором пункта Конструктор.

Создание запроса начинают с выбора тех таблиц базы, на которых будет основан запрос. В данном случае – это Учетная карта  и  Ведомость. Выбор таблиц выполняют в диалоговом окне Добавление таблиц. В нем отображаются все таблицы, имеющиеся в базе. Выбранные таблицы заносятся в верхнюю половину бланка запроса по образцу щелчком на кнопке Добавить (рис. 26). Наличие в диалоговом окне вкладки Таблицы и Запросы говорит о том, что запрос можно основывать не только на таблицах, но уже и на имеющихся запросах

Рис. 26. Диалоговое окно Добавление таблицы. 

2. После выбора таблиц, приступаем к работе с Бланком запроса по образцу. Он имеет две панели (рис. 27). На верхней панели расположены списки полей тех таблиц, на которых основывается запрос. Строки нижней панели определяют структуру запроса, т.е. структуру результирующей таблицы, в которой будут содержаться данные, полученные по результатам запроса.

Рис. 27. Бланк запроса по образцу. В верхней части – выбранные таблицы. В нижней – выбранные (двойным щелчком или перетаскиванием) поля.
В строке Сортировка для поля ФИО показан раскрывающийся список для назначения типа сортировки по данному полю

3. Строку Поле можно заполнить двумя способами: или перетаскиванием названий полей из таблиц в верхней части бланка, или двойным щелчком на этом поле. Каждому полю будущей результирующей таблицы соответствует один столбец бланка запроса по образцу. Добавьте поля ФИО, Nst и Ngr из таблицы Учетная карта перетаскиванием мышью, а поля с названиями зачетов и экзаменов из таблицы Ведомость – двойным щелчком. Строка Имя таблицы заполняется автоматически при установке поля.

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

5. Закройте запрос, при закрытии дайте ему имя Общая ведомость.

6. Запустите запрос на выполнение, используя команду горизонтального меню Запрос>Запуск, или щелкнув на значке Представление запроса  панели инструментов Конструктор запроса. Посмотрите, какие данные он выводит.

7. Закройте запрос.

8. Следуя инструкциям 1 – 8, объединив данные из трех таблиц Группы, Преподаватели, Кафедры, создать запрос Кураторы, который дает список кураторов групп с их местом работы: название и месторасположение кафедры. Отсортируйте список кураторов по алфавиту (рис. 28). 

Рис. 28. Результаты выполнения запроса Кураторы, выполненного
по трем таблицам Группы, Преподаватели, Кафедры.  
Записи отсортированы по алфавиту кураторов

Построение запросов с условием отбора

 Условиями отбора называют ограничения, которые накладываются на запрос или фильтр для отбора конкретных записей. Например, требуются сведения не обо всех студентах, а только о тех, кто обучается на бюджетной основе. Для этого следует указать условия отбора, которые позволят включать в набор записей только те, у которых в поле Paym (Форма опл)  находится значение "Б" (рис. 29). 

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

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

Можно одновременно определить несколько условий отбора для одного поля или для разных полей. При вводе выражений в несколько ячеек Условие отбора Access объединяет их с помощью оператора And или Or. Если выражения находятся в разных ячейках одной и той же строки, используется оператор And. Если выражения находятся в разных строках бланка, применяется оператор Or.

Например, если требуется получить список студентов, не подлежащих призыву в армию, то в него войдут все девушки (поле pol=”ж”), студенты имеющие льготы (поле Lgoty= ДА), мужчины, дата рождения которых позднее, т.е. меньше 1 января 1985 года (на 2003 год). Тогда бланк запроса будет иметь вид, аналогичный показанному на рис. 30.

Рис. 30. Критерий запроса включает три условия для разных полей,
объединенных оператором OR. Результирующие записи должны
удовлетворять одному из трех условий

2.2. ЗАДАНИЕ 10. Построение запросов с условием отбора

1. Сформировать запрос Запрос1 на получение списка студентов-мужчин, обучающихся на коммерческой основе. В результирующей таблице выведите только список фамилий. Рекомендация: условия для требуемых полей записывать в одной строке.
2. Сформировать запрос Запрос2 на получение списка студентов-мужчин, дата рождения которых находится в интервале от 1.01.1983г. до 31.12.1983г. Рекомендация: условия для выбора можно задать с помощью операторов >=#1.01.83# And <= #1.01.83# или  Between #1.01.83# And #31.12.83# 

 Построение запросов с параметром

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

Для каждого поля, которое предполагается использовать как параметр, введите в ячейку строки Условие отбора текст приглашения, заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст подсказки должен отличаться от имени поля, но может включать его.

Например, сделаем запрос на отсрочку от армии (рис. 30) более универсальным: возраст призыва будет меняться в зависимости от времени призыва. Для этого выполните следующее задание.

2.3. ЗАДАНИЕ 11. Построение запросов с параметром

1. Используя буфер обмена, скопировать в базу данных запрос Отсрочка от армии на 2003год. Дать ему новое имя  Отсрочка от армии.
2. Открыть новый запрос в режиме Конструктора. В ячейке Условие ввода
   ввести: <[Ввести дату призыва дд.мм.гг.] (рис. 31 а).
3. Для предварительного просмотра запроса с параметрами до его сохранения нажмите кнопку  на панели инструментов и введите значение параметра. Для возвращения в режим конструктора запроса нажмите кнопку   на панели инструментов. При выполнении запроса программа Access открывает диалоговое окно с введенной вами в квадратных скобках подсказкой (рис. 31 б). В это окно необходимо ввести нужную величину.
4. Активизируйте запрос несколько раз, задавая разные значения параметра. Посмотрите, как меняются результаты выполнения запроса.
5. Скопируйте Запрос2 из ЗАДАНИЯ 10, дав ему имя Запрос3. Откройте его в режиме Конструктора.
6. Для того чтобы вывести приглашения "Введите начальную дату:" и "Введите конечную дату:" для определения диапазона отбираемых значений, введите Between [Введите начальную дату :] And [Введите конечную дату:] в ячейку строки Условие отбора в столбце поля Dtr (Дата рожд). Поработайте с этим запросом. 

а)                                                    б)

Рис. 31. а) Фрагмент бланка запроса, в котором параметром является

условие отбора для поля Dtr (Дата рожд);

б) Диалоговое окно для ввода параметра

Для того, чтобы находить в поле не конкретное значение, а только его фрагмент используют оператор Like. Например, если требуется отобрать студентов, фамилия которых начинаются на букву «А», в бланке запроса для поля ФИО можно записать условие Like "А*".

7. Для таблицы Ведомость создать запрос, который выводил результаты экзаменов за I семестр для любой группы.

8. Для этого создайте новый запрос в режиме конструктора на основе запроса Общая ведомость. Добавьте поля ФИО, Nst, 1_p, 1_e1,1_e2, Ngr. В последнем поле Ngr задайте условие отбора Like [Ввести номер группы]. Дайте ему название Ведомость группы за I семестр.

9. Проверьте, как работает данный запрос. Обратите внимание, что при вводе в поле параметра группы можно не соблюдать регистр букв и не надо набирать дефис. Например, чтобы получить ведомость группы А‑2, надо ввести а2.

 Вычисления в запросах

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

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

 Построение вычисляемых полей

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

2.4. ЗАДАНИЕ 12. Построение запросов с вычисляемыми полями

Построим простейший запрос, в котором выдается список группы и средний балл каждого студента (для II курса II семестра – это группы А-2 и Б-2).

1. Откройте окно запроса Ведомость группы за I семестр  в режиме конструктора.
2. Уберите флажки в ячейке Вывод на экран для полей Nst, Ngr, 1_e1, 1_e2. В ячейку Поле свободного столбца поставьте курсор, вызовите контекстное меню и выберите команду Область ввода: в этом окне удобнее работать с длинными выражениями.
3. В Области ввода набрать выражение:
   СреднийБалл: ([1_e2]+[1_e1])/2. Закрыть Область ввода. Поставить галочку в ячейке Вывод на экран вычисляемого поля: (рис. 32).
4. Запустите запрос (пункт 7 ЗАДАНИЯ 9), как работает данный запрос?
5. При проектировании таблиц мы оговаривали, что в таблице Группы количество студентов в группе и номер курса могут быть вычислены по уже имеющимся полям. Составьте самостоятельно запрос Список групп, в котором номер курса определяется в вычисляемом поле как разность между последней цифрой текущего года и последней цифрой номера группы. Например, для II полугодия 2004 года группа Б-1 будет отнесена к III курсу: (200)4-(Б-)1=3. Причем, если берется I полугодие, то формула расчета должна включать добавление единицы. Этот факт реализуется вводом параметра в выражение:

Kурс: Val(Right$(Date$();1))-Val(Right$([Nгр];1))+[Для I полугодия ввести 1, иначе 0]

Рис. 32. Запрос с вычисляемым полем

6. Постройте указанный запрос на основе таблицы Группы. Вычисляемое поле поставьте после поля Nгр, используя команду горизонтального меню Вставка>Столбец. Проверьте, как работает запрос для разных значений параметра.

7. Используя выражение для вычисления номера курса пункта 5, создайте запрос Список студентов по курсам на основе таблицы Учетная карта, включив в него все поля, кроме Phgr. При этом модифицируйте выражение для вычисления курса так, чтобы полугодие вычислялось автоматически (для этого используйте функции MID$, DATE$(), чтобы определить текущий месяц года.)

 Групповые операции и вычисления. Итоговые запросы

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

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

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

Список опций поля Групповая операция включает 9 итоговых функций и три элемента: Группировка, Выражение, Условие.

• Группировка. Этот элемент указывает на поле, по которому результаты выполнения запроса будут организованы в группы для дальнейших итоговых вычислений.
• Sum. Суммируются все значения, содержащиеся в поле запроса.
• Avg. Вычисляется среднее арифметическое значение для всех чисел, содержащихся в выбранном поле.
• Min/Max. Отображается минимальное/максимальное изо всех значений, содержащихся в поле запроса.
• Count. Вычисляется количество непустых значений в поле запроса.
• StDev. Вычисляется среднеквадратичное отклонение для значений в поле.
• Var. Вычисляется дисперсия распределения значений, содержащихся в указанном поле.
• First/Last. Отображается значение из первой/последней записи результирующего набора.
• Выражение. Этот элемент сообщает программе Access, что следует создать поле, значение которого будет вычисляться.
• Условие. Элемент, указывающий Access, что данное поле не участвует в группировке; условие отбора, вводимое в это поле, определяет какие записи будут участвовать в вычислениях.

2.5. ЗАДАНИЕ 13. Построение итоговых запросов

В ЗАДАНИИ 12 в пунктах 5 – 6 был создан запрос Список групп. Дополним этот запрос новыми вычисляемыми полями, в которых указано количество студентов в группе и средний балл по двум экзаменам.

1. Откройте запрос Список групп в режиме Конструктора.
2. Добавьте в верхнюю половину бланка запроса таблицу Учетная карта.
3. Поставьте курсор в поле Instructor(Куратор) и перетащите поле Ngr таблицы Учетная карта. Новое поле вставляется между имеющимися полями запроса.
4. Отобразите строку Групповая операция, выполнив команду горизонтального меню Вид>Групповые операции.
5. В ячейке нового поля строки Групповая операция щелкните на кнопке раскрывающегося списка справа от элемента Группировка и выберите опцию Count.
6. Сохраните запрос и посмотрите, как он работает. Обратите внимание, какое имя дает Access новому полю.
7. Перейдите в режим Конструктора и вызовите контекстное меню на вычисляемом поле. Перейдите к пункту Свойства и дайте новую Подпись Количество.
8. Теперь вычислите средний балл группы по каждому экзамену. Снова перейдите в режим Конструктора. Вставьте в верхнюю половину бланка запроса таблицу Ведомость. В свободные столбцы вставьте из новой таблицы поля с результатами экзаменов:1_е1, 1_е2, 2_е1, 2_е2.
9. Выберите среди групповых операций для этих полей функцию Avg. Посмотрите, как работает запрос.
10.  Дайте новые подписи каждому полю (например, СрБ_1э1) так, как это было сделано в пункте 7.
11.  Измените формат вывода среднего балла так, чтобы высвечивался только один знак после десятичной запятой. Для этого воспользуйтесь пунктами Формат (Фиксированный) и Число десятичных знаков (1) из вкладки Свойства поля, вызываемой из контекстного меню.
12.  Сохраните запрос и посмотрите результаты его работы. Они должны выглядеть так, как показано на рис. 33.
13. Самостоятельно постройте запрос по таблице Преподаватели, определяющий количество сотрудников по каждой должности. Используя ввод параметра, модифицируйте построенный запрос так, чтобы он определял количество сотрудников только одной категории.

    Рис. 33. Результаты итогового запроса Список групп

2.6. ЗАДАНИЕ 14. Построение итоговых запросов с использованием сложных условий отбора

Если возникает необходимость отфильтровать записи еще до выполнения вычислений, а условие отбора применяется не для поля группировки, в строке Групповая операция такого поля следует выбрать опцию Условие. Например, необходимо в каждой группе подсчитать количество студентов, сдавших экзамены за первый семестр без задолженностей и троек – количество «хорошистов». При этом вначале должны быть отобраны записи (студенты), удовлетворяющие указанному критерию, затем они должны быть сгруппированы по полю Nгр и по каждой группе уже будет подсчитано количество попавших в нее студентов. Бланк такого запроса и результаты его работы представлены на рис. 34.

1. Создайте запрос, бланк которого показан на рис. 34. Обратите внимание, что программа Access автоматически снимает флажок опции Вывод на экран для полей, к которым применено условие отбора. Дайте запросу имя Хорошисты групп
2. Модифицируйте запрос Хорошисты групп так, чтобы он выдавал данные только для одной группы, т.е. введите параметр для названия группы.
3. Создайте запрос, определяющий в каждой группе количество студентов, имеющих задолженности за I семестр.
4. Создайте запрос, определяющий количество студентов, претендующих на повышенную стипендию (сдавших зачет и имеющих оценки «5» по двум экзаменам, например, по предметам 1_з,2_э1,2_э2).
5. Создайте запрос, определяющий список студентов II курса  (группы А‑2 и Б–2), которые могут получать стипендию. Критерий для обора таких студентов следующий: это студенты, которые обучаются на бюджетной основе и не имеют задолженностей, оценки по двум экзаменам II семестра (2_э1 и 2_э2) «4» и «5» ИЛИ имеют льготы (в поле Lgoty – да), которые дают им возможность получать стипендию, если среди оценок на экзаменах есть тройки. Дайте запросу имя Список II курса на стипендию.

а)

б)

Рис. 34. а) Бланк запроса, в котором критерий отбора записей будет
применен до выполнения вычислений. б) Результаты работы запроса:
показано, сколько «хорошистов в каждой группе»

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

7.  Усложним запрос, включив в него возможность подсчитывать общую сумму стипендии, выделяемой на каждую группу. Размер стипендии ввести как параметр. Для того, чтобы в выражении для стипендии записать формулу, необходимо ввести имя поля Count_Ngr, в котором подсчитывается количество студентов в группе, получающих стипендию. Поскольку в бланке запроса это имя не видно (оно выдается только при просмотре результатов запроса, да и то в случае, если не дана другая подпись полю), то для записи выражения лучше воспользоваться Построителем выражений и взять требуемое поле из раскрывающегося списка полей запроса Стипендия II курса (рис. 35).

Как видно из этого рисунка, размер стипендии вводится параметром в само выражение, её значение будет запрашиваться при запуске запроса.
Для того, чтобы запрос подсчитывал стипендию согласно построенному выражению, необходимо в строке Групповая операция поля Стипендия выбрать опцию Выражение.

8.  Посмотрите, как работает созданный Вами запрос. 

Рис. 35. Использование Построителя выражений для создания

вычисляемого поля Стипендия  на основе поля группировки Count_Ngr.

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

2.7. ЗАДАНИЕ 15. Построение сложных запросов с условиями отбора для вычисляемых полей

В запросе Стипендия II курса размер стипендии задавался одинаковым для всех категорий студентов. Во многих ВУЗах практикуется дифференцированная стипендия для разных категорий студентов. Допустим, что размер базовой стипендии равен N руб. Для отличников стипендия увеличивается вдвое, а для тех, кто получает стипендию, имея тройки, уменьшается вдвое. Составим запрос, в котором выдается список всех студентов II курса (группы А-2, Б-2) за II семестр (2_з, 2_э1, 2_э2) с указанием начисленной стипендии.

Создайте такой запрос по следующей схеме.

1. Создайте новый запрос в режиме Конструктора. Добавьте в бланк запроса таблицы Учетная карта  и Ведомость.
2. Перенесите в нижнюю половину запроса поля ФИО, Nгр, Форма опл, Льготы из таблицы Учетная карта и поля 2_з, 2_э1, 2_э2 из таблицы Ведомость.
3. В ячейке Условие Отбора поля №гр укажите выбор групп II курса любым способом, например, используя операторы In ("А2";"Б2") или "А2" OR "Б2". (Можете предложить другой способ определения студентов одного курса?)
4. В новом столбце создайте вычисляемое поле логического типа K, определяющее что студент может получать базовую стипендию, а именно: он обучается на бюджетной основе, не имеет задолженностей за сессию и оценки за экзамены выше «3». Выражение, соответствующее данному критерию, можно записать так:

K: [Paym]="б" And [2_p]="з" And ([2_e1]=4 Or [2_e2]=4) And [2_e1]>3 And [2_e2]>3

1. По аналогии в новом столбце запишите выражение для вычисляемого поля логического типа L, определяющее что студент может получать повышенную стипендию: он обучается на бюджетной основе, не имеет задолженностей за сессию и все оценки за экзамены «5».
2. В следующем столбце запишите выражение для вычисляемого поля логического типа M, определяющее что студент может получать половину базовой стипендии: он обучается на бюджетной основе, имеет льготы, сессию сдал без задолженностей и без двоек, но имеет тройки.
3. В следующем новом столбце создайте вычисляемое поле денежного типа Стипендия, указав в Свойствах поля формат поля Денежный с числом знаков 2. Выражение для этого можно записать, используя функцию управления Iif. Это выражение будет достаточно громоздким, а именно:

Стип: IIf([K]=-1; [Стипендия]; IIf([L]=-1;[Стипендия]*2;

IIf ([M]=–1;[Стипендия]/2;0)))

1. Посмотрите результаты работы запроса (рис. 36). Знак «-1» в логических полях K,L,M свидетельствует о том, к какой категории относится данный студент.
2.  Сохраните запрос, присвоив ему имя Дифференцированная стипендия II курса.
3. Создайте с помощью Конструктора новую таблицу Активисты факультета (рис. 37). В этой таблице представлен список студентов, занимающихся общественной работой.

Категория этой работы оценивается как «1», если работа соответствует уровню факультета, и «0», если – уровню группы. Если категория общественной работы «1»,то к стипендии добавляется M % от стипендии, которую получает данный студент.

11. Свяжите новую таблицу с таблицей Учетная карта, не устанавливая целостность данных.

Рис. 36. Результаты работы запроса на вычисление дифференцированной стипендии. Значение «-1» в логических полях K,L,M является
показателем для начисления стипендии в зависимости
от категории студента

Рис. 37. Таблица Активисты факультета

12. Модифицируйте запрос Дифференцированная стипендия II курса так, чтобы в нем вычислялась дополнительная стипендия, если категория общественной работы оценивается «1», и выдавалась суммарная стипендия (Рис. 38).

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

Рис. 38. Результаты работы модифицированного запроса
Дифференцированная стипендия II курса для случая 10 % надбавки

к стипендии за общественную работу

3. ВЫВОДЫ

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

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

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

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

4. КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Для чего служат запросы на выборку?
2. Почему запросы не занимают в базе данных много места?
3. Перечислите все способы включения полей таблицы в бланк запроса.
4. Какие виды запросов были использованы при конструировании БД Факультет?
5. Как с помощью запроса провести сортировку  записей по какому-либо полю?
6. Для чего в запрос вставляют ссылки на таблицы?
7. Как включают в запрос условия отбора?
8. Для чего служит оператор Like?
9. Какие преимущества дает запрос с параметром?
10.  Что такое выражения и функции?
11.  Как строится выражение в Access?
12.  Какие действия допустимы с полями текстового типа? логического типа?
13.  Как работает функция управления IIf?
14.  Какие операции допустимы в вычисляемых полях? Сохраняются ли значения таких полей в файле базы данных?
15.  Для чего используется Построитель выражений?
16.  Для чего предназначена операция группировки? В чем суть итогового запроса?
17.  Какие итоговые функции вы знаете?

5. ОТВЕТЫ К ЗАДАНИЯМ

ЗАДАНИЕ 10. Построение запросов с условием отбора

Пункт 1.

Запрос Список студентов – мужчин, обучающихся на коммерческой основе.

ЗАДАНИЕ 12. Построение запросов с вычисляемыми полями

Запрос Список групп

Запрос Список студентов по курсам

Kurs: Val(Right$(Date$();1))-Val(Right$([Ngr];1))+-IIf(Val(Mid$(Date$();4;2))<=6;0;1)

ЗАДАНИЕ 13. Построение итоговых запросов

Пункт 13. Запрос Количество сотрудников по должностям

ЗАДАНИЕ 14.

Пункт  5. Запрос Список студентов II курса на стипендию

Пункт 6. Запрос Стипендия II курса

Cтипендия: [Размер стипендии]*[Count_Ngr]

ЗАДАНИЕ 15.

 Модифицированный запрос Дифференцированная стипендия II курса

K: [Paym]="б" And [2_p]="з" And ([2_e1]=4 Or [2_e2]=4) And [2_e1]>3 And [2_e2]>3 – критерий для получения базовой стипендии.

L: [Paym]="б" And [2_p]="з" And [2_e1]=5 And [2_e2]=5 – критерий для получения повышенной стипендии.

M: [Paym]="б" And [2_p]="з" And [Lgoty]=Истина And ([2_e1]=3 Or [2_e2]=3) And [2_e2]>2 And [2_e1]>2 – критерий получения стипендии по льготам с тройками.

Стип: IIf([K]=-1;[Стипендия];IIf([L]=-1;[Стипендия]*2;IIf([M]=-1; [Стипендия]/2; 0))) – вычисление величины стипендии.

Ds: IIf([категория]=1;[Процент]*[Стип]/100;0) – надбавка за общественную работу.

Сумма: [Стип]+[Ds] – значение стипендии с надбавками.