Цель работы: научиться разрабатывать различные виды запросов в MS Access для поиска, изменения и анализа данных.
Краткие теоретические сведения
Запросы представляют собой средства для извлечения информации из базы данных, отвечающей некоторым критериям. В результатах запроса приводятся не все записи из таблицы, а только те, которые удовлетворяют запросу. С помощью запроса можно определить условие, описывающее искомое подмножество записей; при выполнении запроса Ассеss отображает (в режиме таблицы) только записи, удовлетворяющие этому условию. Запросы могут быть простыми - например, можно вывести все записи, в которых должность сотрудника стоит «Бухгалтер», - или сложными, в которых производится ряд последовательных сравнений содержимого полей и учитываются альтернативные условия. Запрос можно построить с помощью конструктора, в строках и столбцах которого вводятся соответственно просматриваемые поля и условия (см. рис. 1).
Рисунок 1 - Запрос в режиме конструктора
Каждое условие состоит из трех элементов:
· поле, которое используется для сравнения;
· оператор, описывающий тип выполняемого сравнения;
· величина, с которой должно сравниваться значение поля.
Результаты запроса всегда отображаются в режиме таблицы.
Установка критериев отбора записей. Условие отбора записей, задаются в строке Условие отбора. Условием отбора является выражение, которое состоит из операторов сравнения и операндов. В качестве операндов могут быть литералы, константы, идентификаторы.
Константамиявляются такие значения, как «TRUE», «НЕТ», «NULL»" и т.д.
Идентификатор-это имена полей, заключенные в квадратные скобки.
Посредством назначения критериев отбора задается, какие записи нужно выбирать в таблицах и отображать в таблице результатов выполнения запроса. Критерии отбора указываются для одного или нескольких полей.
Пример. Поступление заказа за последние 10 дней
Поле Дата заказа >DATE() - 10
Поиск значений в заданном диапазоне. Для ограничения списка с двух сторон необходимо задать запрос с ограничением. Например, между 1 января 2002 г. и 1 января 2003 г. Чтобы задать подобный запрос, необходимо использовать оператор логического умножения AND(и). Введите два условия в одной строке и разделите их словом AND.
(>=# 01.01.02# AND <# 01.01.03#)
Можно создать эти инструкции «между» для любых типов данных. Можно комбинировать множество критериев в различных столбцах, ограничивая группу записей дополнительными условиями. Если критерий указать в отдельных строках, то между такими критериями будет выполняться оператор OR(или).
Пример. Отобрать студентов, родившихся ранее задаваемого года
YEAR([студент]![дата рождения] )<1985
Для создания запросов предусмотрено несколько режимов (см. рис. 2).
Рисунок 2 - Режимы создания запросов
В окне диалога можно выбрать тип запроса и способ его создания:
· Значение Конструктор предназначено для создания стандартных запросов. Его следует выбрать, если вы хотите создать свой запрос «с нуля».
· Другой, простейший способ создания стандартных запросов заключается в выборе значения Простой запрос, при котором Access поможет вам в разработке запроса.
· Значение Перекрестный запрос позволяет создать достаточно сложные запросы (см. ниже).
· При выборе значений Повторяющиеся записи и Записи без подчиненных запускаются специализированные мастера, помогающие сохранить целостность данных.
Кроме запроса на выборку существуют запросы других типов:
- запросы действия (запрос на добавление, удаление, обновление, создание таблицы);
- параметрические запросы;
- перекрестные запросы.
Запрос на добавление. С помощью запроса на добавление записи из одной таблицы, можно поместить в конец другой таблицы. Таблицы могут находиться как в одной, так и в разных базах данных. Если необходимо добавить записи к таблице из другой базы данных, сначала следует присоединить таблицу источник к базе содержащей целевую таблицу, с помощью команды Связь с таблицамииз подменю Внешние данные,меню Файл.Для отбора добавляемых записей нужно составить запрос на выборку. Проверьте составленный запрос, выполнив его. Затем необходимо в режиме конструктора активизировать команду Добавлениеиз меню Запрос.В диалоговом окне Добавлениев поле Имя таблицызадать имя таблицы, к которой необходимо присоединить данные из результирующего набора записей созданного запроса. Access добавляет в бланк запроса строку Добавленияи в эту строку автоматически (или вручную) вставляются имена полей целевой таблицы, которые совпадают с именами полей запроса. Далее кнопка Запускдля выполнения запроса.
Запрос на удаление. Запросы этого типа служат для удаления из таблицы группы записей, соответствующих некоторому критерию отбора. Записи, удаленные посредством запроса восстановить нельзя.
Запрос на обновление. Используя запрос на обновление, пользователь может изменить группу записей, отобранную на основе определенных критериев. При разработке запроса на обновление прежде всего создается и проверяется запрос на выборку. После этого в режиме конструктора активизируется команда Обновлениеиз меню Запроси добавляется в бланк запроса строка Обновление,которая предназначена для указания новыхзначений полей таблицы. В качестве таковых могут выступать и вычисляемые выражения. Нажать кнопку Запуск.
Запрос на создание таблицы. На основе результирующего набора записей запроса можно построить новую таблицу. С помощью запроса на создание таблицы. Такие запросы обычно применяют для архивирования старых записей или для сохранения резервных копий таблицы. Сначала необходимо подготовить запрос на выборку и получить динамический набор данных. Из режима конструктора и в меню Запрос выбираете команду Создание таблицыи в диалоговом окне ввести имя новой таблицы. Затем выполним Запрос.
Параметрические запроси. Запросы, которые представляют собой варианты базового запроса и незначительно отличаются друг от друга, называются параметрическими.
Пример: запрос на выборку фамилий лиц, проживающих в указанном городе и по определенному адресу. В запросе на выборку включим указанные поля из таблицы. Поля строки Условие отборазаполните не конкретными критериями отбора, а обращением к пользователю. Обращение необходимо заключить в квадратные скобки, например [Назовите город],[Укажите адрес]. В режиме таблицы в окне Введите значение параметра введите нужный город и улицу.
Итоговые запросы.Запрос можно использовать для вычисления расчетов и подведения итогов. Для этих целей в Access предусмотрены статистические функции SOL. Статистическую функцию задают в строке Групповая операция,которая появляется при нажатии правой кнопки или через меню Вид.Для получения итога нужно заменить групповую операцию на конкретную итоговую функцию: Sum, Avg, Min, Max, Count, StDev, Var, First, Last.
Если требуется сделать вычисления с использованием итоговых функций, надо установить в поле Групповая операция значение Выражение, а в строке поле – выражение из итоговых функций. Например, Max([Age])-Min([Age]). Для отбора записей в пределах группы нужно задать в строке Групповая операция значение Условие, а конкретно условие задать в строке Условие.
Функции для отбора дат и времени Day(), Month(), Year(), Weekday(), Hour(), DatePart(), Date().
Вычисляемые поля. Вычисляемое поле, включённое в запрос, позволяет получить новое поле с результатами вычисления. При создании базы данных, создаются только те поля, которые необходимы и не вводятся значения, которые можно вычислить на основе уже имеющей информации. Для этого используется Вычисляемое поле. Чтобы создать Вычисляемое поле, щелкните в строке Поле свободного столбца, и вводите с клавиатуры то, что должна выполнить Access. Название поля заключается в квадратные скобки.
Например: [цена]´1,3
По умолчанию вычисляемому полю присваивается имя Выражение + № выражения + двоеточие (:). Имя поля можно заменить на более информативное.
Кроме изменения имени поля, можно изменить и его формат. Можно проводить вычисления, используя несколько полей.
Например: ([Новая цена] - [цена1])/[цена2] - процентное отношение между новой и старой ценой.
В вычисление можно включать не только числа, но и слова.
Например, объединить фамилию и имя в одно поле: [имя]&" "&[фамилия], где знак & обозначает конкатенацию строк, а между двумя кавычками находится пробел, который отделяет имя и фамилию.
Перекрёстные запросы. MS Access поддерживает особый тип итогового запроса, который называется перекрестным запросом. Они позволяют увидеть вычисляемые значения в виде перекрестной таблицы, напоминающей электронную. В перекрёстном запросе мастер формирует таблицу, в которой левый столбец образует заголовки строк из значений одного поля, верхняя строка образует заголовки столбцов из значений другого поля, а на пересечении строк и столбцов размещаются итоговые значения, вычисленные по значениям третьего поля. Для построения перекрестного запроса выделите имя нужной таблицы в окне базы данных и выберите Запрос – Новый объект на панели инструментов. В окне диалога Новый запрос выберите Конструктор – Запрос -Перекрестный.
Бланк перекрестного запроса содержит строки Групповая операция и Перекрестные таблицы.
