Запросы на выборку. Бланк запроса.

Запросы. Выборки данных.

При работе с таблицами имеются ограничения:

- нельзя использовать вычисляемые поля; Например, если в таблице есть поля «Цена» и «Количество», то нельзя вывести в таблицу расчетное значение «Стоимость»=Цена*Количество;

- при выводе данные таблиц отсортированы по полю первичного ключа или в соответствии с заданной сортировкой;

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

 

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

При этом данные запроса в отличие от таблиц физически не хранятся в БД.

Существуют следующие основные виды запросов:

- запрос на выборку. Данные извлекаются из одной или нескольких таблиц или других запросов и отображаются также в виде виртуальной таблицы;

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

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

- запросы на изменение (удаление) данных.

 

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

Перекрестные запросы

Этот вид запросов относится к числу аналитических запросов для получения сводных данных. Данные запроса отображаются в виде двумерной таблицы-матрицы. Фактически это частный случай запроса группировки.

1) Для получения такого запроса преобразуют обычный запрос в перекрестный, нажав кнопку -Перекрестный на вкладке Конструктор в группе Тип запроса.

В бланке появятся новые строки

- групповые операции

- перекрестная таблица

Свойство Групповая операция заполняется по аналогии с запросом группировки (см. предыдущий параграф)

Свойство Перекрестная таблица заполняется следующими значениями:

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

- заголовок столбцов – определяет колонки перекрестной таблицы (можно задать только для одного поля),

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

 

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

Создаем новый Запрос 1на выборку, на основе Главного запроса. Переносим в Запрос 1 поля для группировки - Назв_спец, - Форма_Об и поле для вычислений- Стоимость.

 

2) Преобразуем запрос в перекрестный, нажав кнопку -Перекрестный на вкладке Конструктор в группе Тип запроса.

 

В бланке появятся новые строки Групповая операция и Перекрестная таблица

 

Задаем значение для свойства Перекрестная таблица:

- Поле Назв_спец - заголовок столбцов,

- Поле Форма_Об - заголовок строк,

- Поле Стоимость - значение с групповой операцией суммирования Sum,

Результат выполнения запроса:

Запросы на изменение данных

- добавление записей в существующую таблицу

- удаление записей

- обновление записей

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

Технология создания таких запросов следующая:

1)создается запрос-выборка

2)запрос выполняется для визуального контроля результатов

3)запрос-выборка преобразуется в запрос-изменение

Запросы на удаление данных

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

Пример 6. Удалить записи в таблице «Работы», в которых значение в поле «Дата» меньше заданной.

1)Создаем запрос-выборку, в который включаем только те поля, которые используются в условии отбора

2)Задаем тип параметра

 

3) Выполняем запрос для проверки правильности отбора данных.

4) Запрос-выборка преобразуем в запрос-удаление. Для этого на вкладке Конструктор в группе Тип запроса: нажать кнопку Удаление

5) Выполняем запрос. Перед удалением будет выведено сообщение о количестве удаляемых записей

Запросы. Выборки данных.

При работе с таблицами имеются ограничения:

- нельзя использовать вычисляемые поля; Например, если в таблице есть поля «Цена» и «Количество», то нельзя вывести в таблицу расчетное значение «Стоимость»=Цена*Количество;

- при выводе данные таблиц отсортированы по полю первичного ключа или в соответствии с заданной сортировкой;

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

 

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

При этом данные запроса в отличие от таблиц физически не хранятся в БД.

Существуют следующие основные виды запросов:

- запрос на выборку. Данные извлекаются из одной или нескольких таблиц или других запросов и отображаются также в виде виртуальной таблицы;

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

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

- запросы на изменение (удаление) данных.

 

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

Запросы на выборку. Бланк запроса.

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

 

В Microsoft Access 2007 используются три основных способа создания нового запроса на выборку:

- создание запроса с помощью Конструктора запросов;

- создание запроса с использованием Мастера запросов;

- создание запроса в режиме SQL-редактора.

1.1. Создание запроса с помощью Конструкторазапросов

Создание запроса рассмотрим на примере БД учета заключения договоров за обучение.

База данных содержит информацию о студентах учебного заведения, обучающихся на договорной основе.

Информация о заключенных договорах фиксируется в таблице Договор.

 

 

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

1) На ленточной панели инструментов Создание нажать кнопку Конструктор запросов. Будет выбран режим создания запроса в ручном режиме

 

2) Открывается окно с бланком запроса и окно «Добавление таблицы».

 

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

 

3) В окне «Бланк запроса» формируем запрос

Бланк запроса

 

 

а)Добавляем поля в бланк запроса одним из следующих способов:

- выбор из списка полей таблиц-источников в колонке бланка запроса

- двойной клик на поле в таблице-источнике

- перетаскивание поле из таблицы-источника в бланк

б)Задаем другие свойства полей запроса:

- Сортировка – задает тип сортировки (по возрастанию/по убыванию/отсутствует)

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

- Условия отбора – задает критерии отбора данных;

Дополнительные свойства (маска ввода, формат вывода) можно задать через контекстное меню

 

4) Выполняем (тестируем) запрос, нажимая кнопку Выполнить

 

 

Выполнение запроса возвращает следующие данные

 

5)Нажимаем кнопку Сохранить и в окне Сохранение задаем имя запроса, под которым он будет сохранен

Задание условий отбора. Параметры

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

Условия, записанные друг под другом в одной колонке, соединяются операцией ИЛИ. Условия, записанные друг в разных колонках, соединяются операцией И.

В условии для текстовых полей можно использовать символы шаблона

* - несколько произвольных символов;

? – один произвольный символ

Примеры:

Л* - любые текстовые строки, начинающиеся с Л,

*Л - любые текстовые строки, заканчивающиеся на Л,

*авто* - любые текстовые строки, содержащие фрагмент «авто»,

1? - любые двухсимвольные строки, начинающиеся с «1».

 

Пример 2. Усложним запрос из примера 1, добавив условия: отобрать данные по студентам, имеющих фамилию, начинающиеся с букв Л или С, и стоимостью обучения более 25 000.

 

Результат выполнения запроса

 

 

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

 

Пример 3. Отобрать данные, относящиеся к периоду до заданной даты. Используем параметр [До даты включительно]

Примечание: в условии на равенство знак «равно» опускается.

 

 

При выполнении запроса появится окно с запросом параметра

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

Результат выполнения запроса при задании даты 07.10.2014

 

Вычисляемые поля

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

Формула вычисления задается выражением.

Например,

Стоимость работы: [ОбъемРаботы]*[Стоимость1часа].

здесь:

Стоимость работы – имя вычисляемого поля

ОбъемРаботы и Стоимость1часа - - имена полей, используемые в формуле

Значения вычисляемых полей изменять нельзя.

Полученное значение можно отформатировать:

 

задать для вычисляемого поля свойство «Формат поля», для этого на поле надо щелкнуть правой кнопкой мыши и выбрать пункт Свойства

Добавление в запрос вычисляемого поля Стоимость

Результат