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

Тема: Работа с данными при помощи запроса на выборку

Цель: Научиться конструировать запросы

1. Выбор данных из одной таблицы

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

После выполнения запроса на выборку Microsoft Access создаёт набор записей, содержащий отобранные данные, с которыми можно работать также как и с таблицей.

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

Рис. 1 Список кнопки Новый объект

Рис. 2 Новый запрос

Окно конструктора запросов (рис.3) разделено на две части. В верхней находятся списки полей таблиц или запросов, на основе которых создаётся новый запрос. В нижнем располагается бланк QBE (запрос по образцу), в котором выполняется работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе.

Рис.3 Запрос в режиме конструктора

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

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

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

Ввод условий отбора. Если нужно отобрать записи с конкретным значением поля, его нужно ввести в ячейку Условия отбора этого поля. Текстовое значение в качестве условия заключается в кавычки. При задание условия отбора можно пользоваться знаками отношений <, >, >=, <=, =, <> и логическими операторами or, and.

Кроме этого Access предоставляет специальные операторы для отбора данных, выводимых в запросе:

between – определяет диапазон значений. Between 10 and 20 означает то же самое, что и выражение >=10 and <=20;

in – задаёт используемый для сравнения список значений. Выражение in( “wa”, “ca”, “id”) означает то же самое, что и выражение “wa” or “ca” or “id”;

like – этот оператор позволяет при отборе текстовых полей использовать символы: ?, *, #. Символ # указывает, что в данной позиции должна стоять цифра, символы ? и * имеют то же назначение, что и в именах файлов OC MS DOS. Например, like “B*” – означает, что нужно выбрать поля, начинающиеся с буквы В. Команда like [Введите фамилию] выводит диалоговое окно.

Условия отбора для дат и времени Access обрабатывает в любом формате. При вводе дату или время необходимо заключить в символы #. Например, #15 Апрель 1998#, #15/04/98# определяют одну и ту же дату.

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

day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31. Если нужно отобрать записи с определёнными днями месяца – задаётся вычисляемое поле, например, day([Дата_заказа]) и вводится условие отбора, например, >10. В этом случае выбираются все записи поля, вычисляемое поле которых >10;

month(дата) – возвращает значение месяца года в диапазоне от 1 до 12;

year(дата) – возвращает значение года в диапазоне от 100 до 9999;

weekday(дата) – возвращает целое число от 1(Воскресенье) до 7(Суббота), соответствующее дню недели;

date() – возвращает текущую системную дату.

Вычисляемые поля. Можно выполнить вычисления с любыми полями таблицы и сделать вычисляемое выражение новым полем в наборе записей. При этом можно использовать любые функции, встроенные в Access и выполнять над полями таблицы арифметические операции с помощью операторов: +, -, *, /, \, ^, mod, &. Например, пусть имеется имя поля с именем “Количество”, где записано количество единиц товара и поле “Цена”, где записана стоимость единицы товара. Тогда для подсчёта стоимости товара в пустое поле бланка запроса нужно ввести выражение Количество* Цена и значение этих полей будет перемножено.

Задание имён вычисляемых полей. При создании любого выражения в бланке запроса Access помещают стандартное имя поля “Выражение 1”. Можно изменить или назначить имена полей, что является важным, если их нужно использовать в отчёте или других запросах. Это делается с помощью окна свойств. Для этого нужно щёлкнуть по любой ячейке соответствующего столбца, нажать кнопку Свойства на панели инструментов и выбрать Подпись или ввести название непосредственно в заголовок поля.

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

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

Access представляет несколько функций для обеспечения групповых операций. Основные из них:

sum – вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых и денежных полей;

avg – вычисляет среднее арифметическое значение всех значений данного поля в каждой группе;

min, max – вычисляет наименьшее (наибольшее) значение поля внутри группы;

count – вычисляет число записей, в которых значение данного поля отличны от Null.

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