Выполнение запросаГотовый запрос выполняется после щелчка по кнопке
Запросы к нескольким таблицам Запросы можно создавать для отбора данных как из одной, так и из нескольких таблиц. Запросы к нескольким таблицам производятся аналогично запросам к однотабличным БД с той лишь разницей, что в окно конструктора запроса добавляются все таблицы, данные которых нужны в запросе. При этом следует учитывать наличие связей между таблицами (см. лаб. раб. по созданию многотабличной БД). На рис. 3 представлены две таблицы Студенты и Студенты и занятия, где показана связь один-ко-многим.
С помощью запроса можно создавать расчетные поля. Это временные поля, в которые заносятся результаты вычислений над значениями других полей. Для создания вычисляемого поля необходимо:
Пример. Найти потребляемую мощность машины. Пример показан на рис. 4.7.
Рис. 4.7. Окно Построителя выражений При вводе формулы нежелательно печатать названия полей вручную, достаточно выщелкивать их двойным щелчком, высвечивая в среднем списке. Рассмотрим пример вычисляемого поля. Пусть в таблице имеется информация о мощности, потребляемой каждой машиной в час, их количестве и времени работы. Для расчета «Потребляемой мощности» всех машин за все время необходимо умножить мощность каждой машины на их количество и время работы в часах. Для этого в Построитель выражений заносится следующая запись: Потребляемая мощность: [Машины]! [Мощность] *[Машины]! Обратите внимание, поля таблицы появляются в квадратных скобках в сопровождении названия таблицы и отделяются от имен таблиц восклицательным знаком. Иногда требуется объединить текстовые поля. Например, в Вашей таблице отдельно содержатся поля «Город», «Улица», «Дом» и «Квартира». Требуется создать поле «Адрес». Создаем запрос, в который выносим поле «Фамилия» и вызываем Построитель выражений. На рис. 4.8 представлена исходная таблица, на основании которой создается запрос.
Рис. 4.8. Исходная таблица Таблица, содержащая требуемые поля, называется «Экзамены». Поля требуется приписать друг к другу. В качестве соединителя полей использован знак конкатенации &. Мы не дали новому полю название и автоматически появилась надпись «Выражение1» (рис. 4.9).
Рис. 4.9. Создание поля «Адрес» пока под именем «Выражение1» Результат выполнения запроса представлен на рис. 4.10.
Рис. 4.10. Результат выполнения запроса Внешний вид, полученный в результате выполнения такого запроса, весьма далек от идеала. Модифицируем выражение, добавив в него название поля и знаки разделителя между составляющими адреса. Окно «Построителя» будет выглядеть следующим образом (рис. 4.11):
Рис. 4.11. Окно Построителя выражения с модифицированным запросом В верхнем окне Построителя записана следующая формула: АДРЕС: [ЭКЗАМЕНЫ]![Город] & ", " & [ЭКЗАМЕНЫ]![Улица] & ", " & [ЭКЗАМЕНЫ]![Дом] & ", " & [ЭКЗАМЕНЫ]![КВ] Знак & соединяет отдельные элементы, в кавычках - разделители. Результат выполнения запроса, представленный на рис. 4.12, отвечает правилам орфографии. Вы можете с помощью знака конкатенации соединять существующие поля и произвольный текст, который вводится в двойных кавычках ("). В приводимом примере – это запятая и несколько пробелов, для отделения названий друг от друга.
Рис. 4.12. Окончательный результат выполнения запроса На практике иногда возникает необходимость выполнить запрос для групп данных, или получить итоговые значения по группам строк. Такие запросы называются «Итоговыми». Например, необходимо подсчитать число травмированных рабочих по каждому цеху за текущий год. В этом случае используются итоговые запросы. Для создания группового запроса необходимо выполнить команду «Вид»/ «Групповые операции» или нажать на панели инструментов кнопку Таблица 4.1. Основные групповые функции
Если необходимо произвести расчеты не по всем записям, а только по определенным, в строке «Условия отбора» задаются соответствующие условия. При необходимости указывается и порядок сортировки (рис. 4.13).
Рис. 4.13. Режим конструктора при создании итогового запроса Если в запрос добавляется вычисляемое поле, использующее одну или несколько статистических функций для расчетов итоговых значений, необходимо выбрать в ячейке «Групповая операция» этого поля значение «Выражение».
Использование функций даты/времени Допустим, нами была создана база данных, содержащая поля, указанные на рис. 4.14, причем поля «Дата экзамена» и «Дата рождения» имеют «Краткий формат даты», а поля «Время начала экзамена» и «Время окончания» - «Краткий формат времени».
Рис. 4.14. Создание таблицы базы данных При составлении запросов с полями указанных типов при поиске конкретной даты, ее вводят в «Условие отбора», при этом при запуске запроса появятся ограничивающие значки #, например, при вводе условия = #1.2.06# появятся все экзамены, приходящиеся на эту дату. Однако, такой вариант работы имеет ограниченные возможности. Если требуется найти всех, кто, допустим, родился в мае, вряд ли это удастся сделать с помощью одного условия. Для создания сложных запросов, связанных с полями указанных типов, используются встроенные функции. Наиболее популярные функции: DATE() - возвращает текущую системную дату компьютера в виде 1.7.2006; DAY([дата]) - возвращает день месяца. Обязательный аргумент [дата] может быть любым значением типа «ДАТА», например, именем поля соответствующего типа или просто произвольной датой. MONTH([дата]) - возвращает номер месяца; YEAR([дата])- возвращает год; WEEKDAY([дата]) - возвращает номер дня недели, причем нумерация дней начинается с воскресенья (№ 1), понедельник - №2, вторник - №3 и т.д. HOUR([время])- используется для определения часа, соответствующего указанному времени; MINUTE ([время]) -используется для определения минут, соответствующего указанному времени; SECOND([время]) -используется для определения секунд, соответствующих указанному времени; TIME() - возвращает текущее системное время; NOW() - возвращает текущую дату и время. Приведем несколько примеров использования функции типа Дата/время. Пример 1. Для определения количества дней, прошедших со времени экзамена, используется выражение, записанное с помощью Построителя выражений (рис. 4.15).
Рис. 4.15. Вычисляем дни, прошедшие со дня экзамена Пример 2. Для определения фамилий тех, кто сдавал экзамен в пятницу WEEKDAY[Дата экзамена]=6 и 13-ого числа DAY[Дата экзамена]=13 используем запрос, конструктор которого приведен на рис. 4.16.
Рис. 4.16. Пятница, 13-ое число Попробуйте по аналогии найти всех, кто родился в выходные дни. Пример 3. Для определения студентов, родившихся в високосные года, создается запрос, приведенный на рис. 4.17.
Рис. 4.17. Поиск високосных лет Если потребуется найти всех, кто родился в года, кратные пяти, что изменится в формуле? Пример 4. Для определения фамилий всех, сдавших экзамены в 2006 году, используют выражение: Year([Экз]![Дата экзамена])=2006, которое вводят в строку «Условие отбора».Для выделения месяца и года из поля применяют выражения: Месяц: Month([Экз]![Дата экзамена]);» Год: Year([Экз]![Дата экзамена]) Если необходимо узнать, сколько дней прошло между двумя датами, определяют их разность. Но иногда требуется определить количество месяцев, прошедших между двумя датами, возраст человека в годах, сроки выполнения работы в кварталах. Aссеss предоставляет для этого специальные функции. Определение разницы между датами производят с помощью функции DATEDIFF(интервал; начальная дата; конечная дата). На первом месте задается интервал (в чем измеряется разница). Если это год, то используют yyyy, q – кварталы; m- месяцы; d- в днях; ww – в неделях; h – в часах; n – в минутах; s -всекундах. ~ Возраст1: DateDiff ("yyyy"; [Экз]![Дата рождения]; [Экз]![Дата экзамена]) - определяет возраст студента на момент сдачи экзамена в годах; ~ Возраст2: DateDiff("m"; [Экз]![Дата рождения]; [Экз]![Дата экзамена]) - определяет возраст студента на момент сдачи экзамена в месяцах. Составьте выражение, позволяющее определить стаж работы, если известны дата поступления на работу и дата ухода на пенсию. Функция DATEADD(интервал, число, дата) предназначена для добавления или вычитания указанного временного интервала из значения даты. Например, с помощью этой функции можно вычислить дату, на 30 дней более позднюю, чем текущая, или время, на 45 минут более позднее, чем настоящее. Для добавления дней к аргументу дата можно задавать временной интервал как день года ("y"), день месяца ("d") или день недели ("w"). Пример 1. Для определения даты, которая была через полтора года после дня рождения в Построителе выражений следует ввести следующую запись: Дата: DateAdd("m";18;[Экз]![Дата рождения]) Пример 2. Поле «Новая дата» определяет дату через 10 лет после дня рождения, для чего в Построителе выражений вводится следующая запись: Новая дата: DateAdd("yyyy";10;[Экз]![Дата рождения]). Пример 3. Для определения всех, пришедших на экзамен до 11 часов и ушедших не позднее 14, необходимо создать запрос, конструктор и результат выполнения которого приведен на рис. 4.18.
Рис. 4.18. Конструктор запроса и результат выполнения Функции «Управления» используются для проверки условий и выполнения набора некоторых действий в зависимости от результатов проверки. Для их применения используют «Построитель» (рис. 4.19).
Рис. 4.19. Список функций управления Принципы действий операторов изучим на примере таблицы «ВОЯЖ» (рис. 4.20). Допустим, требуется найти новую стоимость билетов, если их изменения произошли по следующему принципу: для поездок, приходящихся на период с мая по октябрь, стоимость билета следует увеличить на 25%; на остальные месяцы уменьшить на 10%.
Рис. 4.20. Таблица исходных данных Для выполнения поставленной задачи используем функцию IIF (рис. 4.21). Синтаксис: IIf (условие; действие по «ДА»; действие по «Нет») Назначение: проверить условие и в зависимости от результатов его проверки, выполнить то ли действие по «ДА», то ли действие по «Нет».
Рис. 4.21. Построитель выражения при использовании функции управления Новая стоимость: IIf (Month([ВОЯЖ]![Дата отъезда])>4 And Month([ВОЯЖ]![Дата отъезда])<11; [ВОЯЖ]![Стоимость билета]*1,25;[ВОЯЖ]![Стоимость билета]*0,9). Комментарии к формуле мы привели ниже.
Функция IIF может быть использована в укороченном варианте: IIf(условие; действие по «ДА»). Например, требуется повысить стоимость проживания только во Франции. Новый запрос выполняется на базе уже имеющегося - это делается следующим образом: Новая стоимость: IIf ([ВОЯЖ]![Страна]= Франция"; [ВОЯЖ]![Стоимость проживания]*2). Для того, чтобы можно было использовать поля базового запроса, его требуется предварительно сохранить. Можно использовать вложенные функции IIF, которые проверяют несколько условий. Например: ПОЖЕЛАНИЯ: IIf ([ДНИ]<10;"Рано уезжаете!"; IIf([ДНИ]<20; "Приезжайте еще!"; IIf ([ДНИ]<30;"Может увидимся!";".....!!!!!"))) В запросе расписаны возможные варианты невысказанных мнений хозяев, в зависимости от срока проживания гостей. Если количество проверок больше двух или трех, функцией IIF становится неудобно пользоваться и вместо нее применяют SWITCH. Синтаксис: SWITCH (условие 1; действие 1; условие 2; действие 2; условие 3; действие 3…) Назначение: содержит пары выражений и значений. Выражения вычисляются в том порядке, в котором они включены в список (слева направо). Возвращается значение, соответствующее первому истинному выражению в списке. Пример: Страна = SWITCH ([CityName] = "Лондон", "Англия", [CityName] = "Рим", "Италия", [CityName] = "Москва", "Россия") - если в поле [CityName] указан Лондон, то Вы находитесь в Англии... В следующем примере функция SWITCH используется для определения сезона по известным датам. Напоминаем, что Month () используется для выделения месяца из даты. Обратите внимание на слово ИСТИНА в конце приводимого выражения. Оно используется в том случае, когда требуется выполнить какое-либо действие, если все предыдущие условия оказались невыполнимыми. Так, если анализируемый месяц находится вне рассматриваемых пределов, то этот сезон называется «ЗИМА». Слово ИСТИНА вводится без кавычек. Сезон: SWITCH (Month([Экскурсия]![Дата])<6 And Month([Экскурсия]![Дата])>2; "весна";Month([Экскурсия]![Дата])<9 And Month([Экскурсия]![Дата])>5;"лето"; Month([Экскурсия]![Дата])<12 And Month([Экскурсия]![Дата])>8;"осень";Истина;"зима"). Попробуйте составить выражение, в котором цена стоимости билетов увеличивается на 35% в летний сезон, падает на 15% в осеннее – весенний и увеличивается на 5% в зимний. Следующей функцией управления является функция CHOOSE. Синтаксис: CHOOSE (указатель; действие 1; действие 2; действие 3…) Назначение: возвращает действие из списка, выбранное на основании значения аргумента - указателя. Указатель всегда считается с 1. Если указатель равняется 1, возвращается первый элемент списка, если указатель равняется 2, возвращается второй элемент списка и т.п. Чаще всего применяют для замены числовых выражений текстовыми эквивалентами. Пример: Допустим, что при вводе данных в таблицу «Экскурсия», мы не стали записывать название транспорта, а ввели кодировку. Если вводилась 1, то это соответствовало морскому транспорту, 2 - поезду, 3 - автобусу. В запросе поле транспорт определяли как: Транспорт(текст): Choose ([Экскурсия]![Транспорт]; "море"; "поезд"; "автобус") Запросы, которые изучались нами ранее, называются запросами-выборками. При их сохранении в памяти машины сохраняются только заданные условия поиска. Каждый раз при запуске запроса на выполнение возникает так называемый динамический набор данных. Он не существует реально. Запрос на изменение - это запрос, который за одну операцию вносит изменения в несколько записей. Существует четыре типа запросов на изменение: на удаление, на обновление, добавление записей, на создание таблицы. Запрос на удаление используется для автоматического удаления группы записей, удовлетворяющих определенным условиям из одной или нескольких таблиц. Причем можно удалять только всю запись, а не отдельные поля внутри ее. Операцию удаления записей отменить невозможно, поэтому рекомендуется перед выполнением создать резервную копию таблицы. Последовательность действий: 1. В режиме конструктора запросов войти в меню «Запрос» и выбрать команду «Удаление». Появится дополнительная строка «Удаление». 2. В строке «Условие отбора» указать, какие записи должны удаляться. 3. Сначала рекомендуется выполнить команду «Запрос»/ «Выполнить», чтобы просмотреть удаляемые записи, а затем «Запрос»/ «Удалить». Ни рис 4.22 показано окно конструктора, в котором из состава студентов, получающих стипендию, будут исключены те, кто сдал экзамены на два. Обратите внимание, в той версии, которая реализована у нас, стипендии лишаются студенты, получившие двойки по трем экзаменам, если бы условия сдачи на два по английскому языку мы перенесли на одну строку ниже (строка «или»), а двойка по математике была бы записана еще ниже, то это означало бы поиск студентов, получивших двойку хотя бы по одному экзамену. Запрос на обновление записей. Вносит общие изменения в группу записей, которые отбираются с помощью условий, в одну или несколько таблиц. Запрос этого типа позволяет изменять данные в существующих таблицах. Допустим, первоначально студентам была назначена стипендия в размере 34 р. (рис. 4.23). Затем стипендию решили увеличить на 50р. Это можно сделать с помощью запроса на обновление. Чтобы создать запрос на обновление, в режиме Конструктора запросов следует выбрать команду меню «Запрос»/ «Обновление». В нижней части бланка появится новая строка «Обновление», в которую следует ввести выражение для расчета (рис. 4.24).
Рис. 4.22. Окно конструктора запроса на удаление
Рис. 4.23. Исходная таблица данных
Рис. 4.24. Окно конструктора запроса на обновление При запуске запроса на выполнение появится сообщение, которое следует подтвердить. После того, как Вы подтвердите обновление записей, внешний вид экрана не изменится, все изменения происходят только с таблицей. Чтобы просмотреть их, откройте таблицу. Значения величин в поле «Стипендия» должны увеличиться. Запросы на обновление позволяют внести изменения не только во все записи таблицы, но и в отдельные записи, которые находят по определенным условиям. В этом случае в строке «Условие отбора» вводят условия, определяющие какие записи выбрать для изменения, а в строке «Обновление» указывают, как их следует изменить. После сдачи экзамена в деканате постановили лишить стипендии всех студентов, которые получили хотя бы одну двойку в сессии. Был составлен запрос и…. Следует обратить внимание на то, что напечатанный 0 в строке «Обновление» означает, что стипендия будет равна 0, но не во всех записях, а в тех, которые удовлетворяют приведенным в соседнем столбце условиям (рис. 4.25).
Рис. 4.25. Окно конструктора запроса на обновление Условия объединяются словом ИЛИ (или ВТ=2, или Математика=2, или английский язык=2). Добавим в таблицу новое поле «Общежитие», однако не будем его заполнять вручную, оставив пустым. Определим, кому требуется общежитие, считая, что, если студент проживает в Донецке, общежитие не требуется, а при проживании в других городах – требуется (рис. 4.26). Запустим запрос на выполнение, щелкнув мышкой по кнопке, Как видите, запрос на обновление очень удобен, позволяя автоматически изменять устаревшую информацию на новую, менять содержимое таблиц по определенным условиям.
С помощью запроса на добавление таблицы имеется возможность добавить записи из одной таблицы в конец указанной Вами таблицы или нескольких таблиц. Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Допустим, требуется создать две таблицы на основании уже существующей, но в первую таблицу перенести информацию о студентах, которым требуется общежитие, а во вторую - проживающих в Донецке. Такая операция выполняется с помощью запроса на создание таблицы (рис. 4.28). Для этого: ~ перенести в запрос те поля, которые должны быть в таблице; ~ указать условия отбора; ~ войти в меню «Запрос» / «Создание таблицы», в появившемся окне ввести имя создаваемой таблицы, в нашем случае она называется «Иногородние»; ~ Запустить запрос на выполнение. Дать согласие на создание новой таблицы; Таблица появится в списке существующих таблиц.
Рис. 4.28. Окно конструктора запроса на создание таблицы
|
панели инструментов в режиме Конструктора запросов или при активизации команды Запуск из меню Запрос. В результате будет получена таблица с ответом на заданные условия. Например, на рис 8 показан результат запроса, построенного на рис. 3.
Рис.8. Результат выполненного запроса
Построить, открыв окно построителя выражений (рис. 4.7);
, в результате чего в окне Конструктора запросов появится новая строка «Групповая операция». Выбрав поле, по которому будет выполняться групповая операция, следует заказать одну из статистических функций, список которых приводится в табл. 4.1.
а затем откроем таблицу. В целях наглядности скрыты все столбцы, кроме столбца «Фамилия» и «Общежитие» (рис. 4.27).
Рис. 4.26. Окно конструктора запроса на обновление
