Файл: Механика и математическое моделирование самара издательство Самарского университета 2021.pdf

35

поле Дата поставки заполнено
Поле:
Дата поставки
Условие отбора:
Is Not Null

по незаполненным значениям в текстовом поле (поле Адрес не заполнено)
Поле:
Адрес
Условие отбора:
Is Null
В текстовое поле может быть введено пустое значение ””:

по пустым значениям в текстовом поле (в поле Телефон введено пустое значе- ние ““)
Поле:
Телефон
Условие отбора:
“”

по непустым значениям в текстовом поле
Поле:
Телефон
Условие отбора:
<>“”
Составные (сложные) условия отбора
Условие отбора типа ИЛИ
Для формирования условий отбора типа ИЛИ заполняют строку Условие отбора и строку
ИЛИ:

отбор товаров категорий обувь и продукты
Поле:
Категория
Условие отбора:
"обувь"
Или:
"продукты"
Условие отбора типа И:

по значению в одном поле (диапазон значений, отобраны товары с ценой от 30 до 100)
Поле:
Цена поставки
Условие отбора:
Between 30 And 100
Или:

для формирования условий отбора типа И в разных полях заполняют строку.
Условие отбора в этих полях, будет отобраны товары категории продукты с це- ной не более 31.
Поле:
Категория
Цена поставки
Условие отбора:
"продукты"
<=31
Или:

36
Запрос с вычисляемым полем
Для создания вычисляемого поля необходимо в пустой колонке бланка конструктора за- просов в строку «Поле» ввести имя нового поля (оно не должно совпадать с именами полей ис- точников запроса) и после двоеточия вычисляемое выражение. Оно может содержать константы, знаки операций, скобки, имена функций и полей. Примеры использования функций в вычисляе- мых полях приведены в Приложении Б. Имена полей нужно вводить в прямоугольных скобках
(Рисунок 14).
Рисунок 14 – Запрос с вычисляемым полем
Примеры вычисляемых полей:

вычисления с числовыми полями – стоимость товара
Стоимость: [Цена поставки]*[Количество]

вычисления с числовыми полями - стоимость товара со скидкой
Стоимость товаров: [Цена покупки]*[Покупки]![Количество]*(1-[Скидка %])

вычисления с текстовыми полями – полные фамилия, имя, отчество
ФИОП: [Фамилия] &” “&[Имя]&” “&[Отчество]

вычисления с текстовыми полями – фамилия с инициалами
ФИО: [Фамилия] &” “&Left([Имя];1)&”.“& Left([Отчество];1)&”.“

вычисления с полями дат
вычисление возраста
Возраст: Int((Date()-[Дата рождения])/365)
вычисление года по дате
Год:Year([Дата рождения])

37
вычисление месяца по дате
Год:Month([Дата рождения])

преобразование одних значений в другие
получение текстовой оценки из числовой (числовая оценка в поле [оценка]) с использо-
ванием функции Switch:
Текстовая оценка: Switch([Оценка]=5;”отлично”;[Оценка]=4;”хорошо”;
[Оценка]=3;”удовлетворительно”;[Оценка]=2;”неудовлетвори- тельно”;[Оценка]=0;”неявка”)
получение названия месяца по его номеру (номер месяца в поле [Месяц]) с использова-
нием функции Choose:
Название месяца:Choose([Месяц];”январь”;”февраль”;”март”; ”ап- рель”;”май”;”июнь”;”июль”; ”август”;”сентябрь”;”октябрь”;”ноябрь”;”декабрь”)
Запрос с параметром
Параметр – вводимое пользователем в режиме диалога значение в условии отбора или в вычисляемом поле. Позволяет пользователю изменять условие отбора или вычисляемое выра- жение не используя конструктор запросов.
Параметр в условии отбора
Параметр в условии отбора – это текст подсказки пользователю, который вводится в кон- структоре в строку условий отбора в квадратных скобках. Он не должно совпадать с именами полей.
Пример заполнения условия отбора с параметром и окна ввода параметра приведены ниже
(Рисунок 15,Рисунок 16).
Рисунок 15 – Запрос с параметром

38
Рисунок 16 – Окно ввода параметра
Примеры параметров в условии отбора приведены ниже:

параметр – выражение с операциями сравнения
Поле:
Цена
Условие отбора:
<=[Введите максимальную цену]

два параметра - диапазон числовых значений
Поле:
Цена
Условие отбора:
Between [Введите минимальную цену] And
[Введите максимальную цену]

параметр – подстрока
Поле:
Наименование
Условие отбора:
Like “*”&[Введите наименование то- вара]&”*”

параметр – начальные символы
Поле:
Фамилия
Условие отбора:
Like [Введите первые буквы фами- лии]&”*”
Параметр в вычисляемом поле
Для изменения значений в вычисляемом выражении используют параметр. Название па- раметра вводится в выражение в вычисляемом поле в квадратных скобках. Оно не должно сов- падать с именами полей.
Поле:
Цена по- купки
Цена со скидкой: [Цена покупки]-[Цена покупки]*[Введите скидку в %]/100
Таблица Покупки

39
При выполнении запроса сначала необходимо ввести значение параметра, затем с ним бу- дет подсчитано значение поля в каждой записи. Например, вычисление цены со скидкой, вели- чина скидки вводится как параметр (Рисунок 17).
Рисунок 17 – Использование параметра в вычисляемом поле
Запрос с итогами
Запрос с итогами позволяет сгруппировать данные по полю, в котором есть повторяющи- еся значения, а по другим полям подвести итоги. Для числовых полей итогами является вычис- ление максимума, минимума, среднего, суммы. Для текстовых полей итогами может быть пер- вое или последнее значение в группе записей. Запрос с итогами можно создать с помощью ма- стера простых запросов или в конструкторе запросов, включив строку Группировка командой
Итоги (Рисунок 18).
В запросах с итогами полей группировки и полей для подведения итогов может быть не- сколько. Должно быть, как минимум, одно поле группировки и одно поле итогов. Если группи- ровка происходит по нескольким полям, то сначала группируются одинаковые значения в самом левом поле, затем среди одинаковых значений в этом поле происходит группировка по значе- ниям в следующем поле и т.д. Группировка по ключевым полям не имеет смысла – в них значе- ния не повторяются и группу для подведения итогов образовать невозможно (Рисунок 18).
Рисунок 18 – Команда Итоги в конструкторе запросов

40
Заполнение бланка запроса с итогами (Рисунок 19).
Рисунок 19 – Заполнение бланка запроса с итогами
Результат выполнения запроса с итогами представлен ниже (Рисунок 20).
Рисунок 20 – Результат выполнения запроса с итогами
Варианты заполнения строки Групповая операция:
1. Группировка – для поля группировки.
2. Функция – для поля подведения итогов.
3. Выражение – для вычисляемых полей.
4. Условие – для полей, по которым заполняется условие отбора.

41
Перекрестный запрос
Перекрестный запрос предполагает группировку по двум полям и подведение итогов по одному полю. Результат выполнения перекрестного запроса похож на сводную таблицу в Excel.
Создать новый перекрестный запрос можно с помощью мастера «Перекрестный запрос» или в конструкторе. В конструкторе создают запрос на выборку, определяют источники запроса и за- тем в группе команд «Тип запроса» кнопкой выбирают Перекрестный. В бланк добавляются строки Групповая операция и Перекрестная таблица. Пример заполнения бланка перекрест- ного запроса показан ниже (Рисунок 21).
Рисунок 21 – Заполнение бланка перекрестного запроса
Результат выполнения перекрестного запроса представлен ниже (Рисунок 22).
Рисунок 22 – Результат выполнения перекрестного запроса

42
Варианты заполнения строки Групповая операция:
1. Группировка – для поля группировки.
2. Название функции – для поля подведения итогов.
3. Выражение – для вычисляемых полей.
4. Условие – для полей, в которых заполнено условие отбора.
Варианты заполнения строки Перекрестная таблица:
1. Заголовки строк – для поля группировки по строкам.
2. Заголовки столбцов – для поля группировки по столбцам.
3. Значение – для подведения итогов.
РЕКОМЕНДАЦИИ ПО СОЗДАНИЮ ЗАПРОСОВ
При создании запросов необходимо руководствоваться следующими рекомендациями:

не включать в качестве источников таблицы, поля которых не используются в запросе;

включать в запрос только необходимые поля источников;

выводить поля в нужном порядке;

кодовые поля (счетчики) обычно не включают в запрос;

обязательно выполнять сортировку в запросах, сортировка по нескольким по- лям должна быть оправданной;

сложную обработку данных лучше выполнять в нескольких последовательных запросах;

давать запросам осмысленные имена.
ИСПОЛЬЗОВАНИЕ СВОЙСТВ ЗАПРОСА
Свойства запроса
Свойства запроса можно изменить, открыв в конструкторе запросов окно свойств из кон- текстного меню (правая кнопка мыши) или командой Страница свойств (Рисунок 23):
Вкладка Конструктор  Группа команд Показать или скрыть Команда Страница свойств.

43
Рисунок 23 – Команда Страница свойств
Свойства запроса представлены ниже (Рисунок 24).
Рисунок 24 – Свойства запроса
Свойства поля в запросе
Для изменения свойств поля в конструкторе запросов необходимо выделить нужное поле и открыть окно свойств из контекстного меню (правая кнопка мыши) или командой Страница
свойств. Обычно свойства поля изменяют в вычисляемых полях. Например, в вычисляемом поле
Стоимость выбран формат поля Денежный и число десятичных знаков 2 (Рисунок 25).
Рисунок 25 – Свойства поля Стоимость

44
КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Назовите универсальный язык программирования запросов.
2. Назовите источники запроса.
3. Перечислите запросы на изменение.
4. Что подсчитает выражение [Цена]*[Скидка в %] в вычисляемом поле запроса?

45
5. ЗАПРОСЫ НА ИЗМЕНЕНИЕ
ОСОБЕННОСТИ ЗАПРОСОВ НА ИЗМЕНЕНИЕ
Эти запросы изменяют данные (добавляют, удаляют, обновляют, создают новые таблицы).
Неправильное применение запросов на изменение может привести к потере данных, поэтому такие запросы требуют особого внимания при выполнении. Перед выполнением таких запросов необходимо создавать копии таблиц-источников запроса или всей базы данных.
ЗАДАЧИ, РЕШАЕМЫЕ ЗАПРОСАМИ НА ИЗМЕНЕНИЕ
С помощью запросов на изменение можно решать следующие задачи (Таблица 9).
Таблица 9 – Типы запросов
Тип запроса
Решаемая задача
Запрос на создание таб- лицы
Создание копий таблиц (архивов)
Отбор записей в новую таблицу
Создание таблиц из нескольких таблиц-источников
Добавление новых полей в создаваемую таблицу
Создание пустых таблиц
Запрос на удаление
Удаление всех данных в таблице (очистка таблицы)
Удаление определенных записей в таблице
Запрос на обновление
Обновление данных в полях таблицы
Запрос на добавление
Добавление новых данных из других источников
(баз данных, электронных таблиц)
В качестве примера рассмотрим базу данных 200>

46
База данных содержит сведения о студентах, обучавшихся в 2019/20 году. Необходимо в конце 2019/20 учебного года подготовить таблицы базы данных для нового 2020/21 учебного года. Последовательность операций, которые необходимо выполнить при преобразовании базы данных, представлена ниже (Таблица 10).
Таблица 10 – Последовательность выполняемых запросов
№
п/п
Операция
Тип запроса
на изменение
Периодичность
выполнения
1
Создание таблицы Студенты 2020 из таблицы
Студенты
Запрос на созда- ние таблицы один раз в конце учебного года
2
Удаление данных о закончивших в 2020 году обучение в таблице Студенты с условием от- бора по номеру курса (5 курс)
Запрос на удале- ние
3
Перевод студентов на следующий курс в
2014/15 учебном году с увеличением номера курса на единицу.
Запрос на об- новление
4
Добавление данных о 1 курсе из таблицы Ex- cel в таблицу Студенты
Запрос на добав- ление
СОЗДАНИЕ ЗАПРОСОВ НА ИЗМЕНЕНИЕ
Запросы на изменение создаются в конструкторе. Для этого выполняют команду создания запроса на выборку с помощью конструктора, определяют источники запроса, затем на вкладке
Конструктор в группе команд Тип запроса выбирают нужный тип запроса на изменение (Ри- сунок 27). В бланк добавляются дополнительные строки, соответствующие типу запроса.
Рисунок 27 – Команды выбора типа запроса
ЗАПРОС НА СОЗДАНИЕ ТАБЛИЦЫ
В запросе на создание таблицы участвуют таблицы-источники (одна или несколько) и таб- лица-получатель. Таблиц-источников может быть несколько, таблица-получатель всегда одна.
Запрос на создание копии таблицы
Сначала создаем обычный запрос на выборку, в качестве источника берем таблицу Сту-
денты (Рисунок 28).

47
Рисунок 28 – Базовый запрос для создания полной копии таблицы
Изменяем тип запроса – запрос на создание таблицы и указываем имя создаваемой таб- лицы-получателя (Рисунок 29).
Рисунок 29 – Заполнение бланка запроса на создание полной копии таблицы

48
Имя таблицы-получателя можно увидеть и изменить в свойствах запроса (Рисунок 30).
Рисунок 30 – Свойства запроса на создание копии
На каждом шаге выполнения запроса на создание таблицы (и других запросов на измене- ние) требуется подтверждение или отмена его выполнения (Рисунок 31).
Рисунок 31 – Предупреждения при выполнении запроса на изменение данных
Запрос на создание таблицы с отбором записей
Создадим таблицу результатов сдачи сессии одной из групп – Рисунок 32.