Создание запросов в Microsoft Access

Подробности: Категория: Материалы студентам; Опубликовано 09.03.2015 09:27; Автор: Шитов В.Н.; Просмотров: 15177

Требования к запросам

Если при редактировании схемы запросов существует требование, чтобы ни одна таблица не была открыта, то при работе с запросами открытые таблицы никак не влияют на запросы. Для создания запросов выполните команду Создание → Конструктор запросов. После этого открывается окно для создания запросов, а также окно с перечнем таблиц, запросов или таблиц и запросов одновременно. Если такое окно не открыто, например, оно было закрыто случайно, то открыть его можно следующим образом: щелкните правой клавишей мыши в свободном месте окна запросов и из открывшегося контекстного меню выполните команду Добавить таблицу. В окне с перечнем таблиц добавьте нужные таблицы и/или запросы (запросы можно использовать точно так же, как и обычные таблицы, потому что в них имеются точно такие же поля, как и в таблицах). Так как у нас пока нет никаких запросов, то пока мы можем работать только с таблицами.

В окне Добавление таблицы выделите таблицы (поочередно или сразу все с нажатой клавишей Shift для смежных или клавишей Ctrl для несмежных) и нажмите на кнопку Добавить.

Если таблица была добавлена в запрос случайно, то щелкните по этой таблице (в любом месте) правой клавишей мыши и из открывшегося контекстного меню выполните команду Удалить таблицу. Таблица удаляется всего лишь из запроса, а не из БД. Закройте окно Добавление таблицы.

При создании запросов используются не все таблицы. Для большинства типов запросов число лишних открытых таблиц не имеет значения, лишние таблицы просто не будут участвовать в запросе. Но в некоторых типах запросов лишние таблицы открывать нельзя, иначе будет выводиться сообщение об ошибке, причем, обычно, в чем состоит ошибка, не говорится. К таким типам запросов относятся Обновление, Удаление.

Расставьте таблицы так, чтобы они позволяли видеть связи между таблицами (Рис. 13).

Рис. 13. Открытые таблицы для запроса

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

1. В очередном пустом столбце Конструктора поместите курсор в строку, в которой находится надпись Поле. После этого в правой части поля появляется миниатюрная треугольная кнопочка. Нажмите на нее и выберите нужное поле. Каждое поле в таблице состоит из 2-х частей: имени таблицы, в которой это поле имеется, и имени поля в этой таблице. Все поля сортированы по именам таблиц. Если одно и то же поле создано в нескольких таблицах, то из какой именно таблицы брать это поле, совершенно все равно.

2. Подхватите поле из таблицы и перетащить его в столбец конструктора запросов (Рис. 14). Отпустите мышь.

Рис. 14. Перетаскивается поле Код_тура

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

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

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

Для примера создадим запрос Анализ продаж. Выберите следующие поля: Код_заказа, Дата, Наименование_тура, Цена, Количество, Название_фирмы, Город, Телефон, Скидка (Рис. 15).

Рис. 15. Конструктор запроса с выбранными полями

Для выполнения анализа и расчета нажмите на кнопку Конструктор → Выполнить. Укажите имя запроса. После этого результаты запроса будут выведены на экран (Рис. 16). Так как записей в таблице Запросы всего 100, то и в результатах запроса должно быть 100 записей.

Рис. 16. Результаты запроса Анализ продаж

В данном запросе мы использовали все 4 исходные таблицы. После создания запроса под каждой таблицей в списке таблиц теперь находится имя созданного запроса.

В следующем запросе создайте те же самые поля, что и в предыдущем (Код_заказа, Дата, Наименование_тура, Цена, Количество, Название_фирмы, Город, Телефон, Скидка). Прокрутите список столбцов в Конструкторе запросов вправо так, чтобы был виден следующий (справа) пустой столбец. В верхнем поле этого пустого столбца введите следующую формулу:

[Цена](1-[Скидка])[Количество]

В квадратных скобках указаны поля, участвующие в расчете формулы. В отличие от аналогичного приложения Microsoft Excel, в котором формула начинается с символа =, в приложении Microsoft Access символ = в формуле не используется.

Щелкните мышью в любом свободном поле, например, на 1 строчку ниже. Сразу после этого перед формулой появляется автоназвание этого поля: Выражение1. Формула и автоназвание разделены между собой символом двоеточия. Выделите мышью текст автоназвания (двоеточие или символы формулы ни в коем случае выделять не нужно). Введите вместо него заголовок поля К оплате. Выполните команду Конструктор → Выполнить (кнопка в виде восклицательного знака). Укажите имя запроса (Анализ продаж с оплатой).

В списке таблиц (в левой части рабочего окна Access) выделите последний запрос, щелкните по нему правой клавишей мыши и из открывшегося контекстного меню выполните команду Конструктор. Выделите столбец с формулой и удалите эту формулу. Обратите внимание, что в предыдущем примере мы создавали формулу с участием полей. Эти поля мы указывали в формуле вручную. Это очень опасный способ, так как ошибка в одной букве названия поля или символе приведет к неработоспособности всей формулы, а вместе с формулой и всего запроса. Поэтому формулы обычно создают с помощью Построителя выражений. Для этого в первом поле столбца щелкните правой клавишей мыши и из открывшегося контекстного меню выполните команду Построить. После этого открывается окно Построитель выражений (Рис. 17).

Рис. 17. Создание формулы с помощью построителя

В левом списке находятся все объекты БД. Откройте группу Таблицы. Откройте таблицу Туры: список полей таблицы выводятся в средней части построителя. Двойным щелчком включите поле Цена в формулу. Квадратные скобки и имя таблицы включаются в формулу автоматически. Одиночным щелчком вставьте символ умножения (*). Вставьте открывающуюся круглую скобку. Введите с клавиатуры цифру 1. Вставьте символ вычитания (-). В списке таблиц щелкните по таблице Клиенты. Двойным щелчком включите поле Скидка в формулу. Вставьте закрывающуюся круглую скобку. Вставьте символ умножения (*). В списке таблиц щелкните по таблице Заказы. Двойным щелчком включите поле Количество в формулу. Нажмите на кнопку ОК. В созданной формуле с клавиатуры потребовалось ввести только цифру 1 — все остальные объекты (поля, операторы и даже скобки) выбирались готовыми: чем меньше потребуется ручного ввода, тем меньше ошибок будет в формуле.

После возвращения в Конструктор запросов название поля измените точно так же, как в и предыдущем примере (щелкните по соседнему полю и автоназвание Выражение1 измените на К оплате).

Как видно из этого примера работа с построителем намного проще, чем ручное создание формулы. Если при ручном вводе формулы нам потребовалось все символы вводить вручную, что рано или поздно приведет к ошибкам, то при использовании построителя нам пришлось с клавиатуры ввести всего один символ: цифру 1.

Для создания следующего запроса откройте конструктор запросов (команда Создание → Конструктор запросов). В предыдущих примерах мы в качестве исходных данных использовали вкладку Таблицы в окне Добавление таблицы. Запрос Анализ продаж является компиляцией из всех 4-х таблиц. Поэтому использовать таблицы в следующем запросе не обязательно. В окне Добавление таблицы перейдите на вкладку Запросы и добавьте запрос Анализ продаж в исходные данные создаваемого запроса. Включите в новый запрос следующие поля: Код_заказа, Дата, Наименование_тура, Название_фирмы, Город, Телефон. В столбце с полем Город найдите поле Условие отбора. Введите в это поле Волгоград. Щелкните мышью в любом соседнем поле (лучше пустом): название города будет автоматически заключено в парные кавычки. Данная операция не является обязательной: просто здесь объясняется, откуда берутся кавычки — они устанавливаются автоматически. Поэтому нет никакого смысла вводить их вручную. В столбце с полем Наименование_тура в поле Условие отбора введите Стамбул (Рис. 18). Выполните команду Конструктор → Выполнить. Сохраните запрос под именем Анализ по регионам (Рис. 19).

Рис. 18. Параметры запроса Анализ по регионам

Рис. 19. Результаты запроса Анализ по регионам

Откройте запрос Анализ по регионам в режиме Конструктора. Измените Волгоград на Ижевск. Наименование_тура измените из Стамбул на Мальорка. Выполните запрос, убедитесь в правильности работы запроса. Самостоятельно выберите Город и Наименование_тура и выполните запрос.

В предыдущем запросе мы указывали условия отбора записей в теле Конструктора. Кроме этого имеется возможность выбора условий непосредственно при работе с запросом. Такие запросы называются параметрическими. Условия поиска указываются не в кавычках, как мы это делали в предыдущем запросе, а в квадратных скобках, причем указывать нужно не конкретное значение, а вопрос (Рис. 20). Например, в предыдущем примере мы в качестве параметра поиска указывали город Волгоград. В параметрическом запросе нужно ввести между квадратными скобками: Введите город. Выполните запрос. Укажите имя запроса Анализ по регионам-П. Введите параметры в окно Введите значение параметра (Рис. 21). Нажмите на кнопку ОК. Укажите следующий параметр (так как в нашем запросе имеются 2 условия в квадратных скобках).

Рис. 20. Параметрический запрос

Рис. 21. Ввод параметра

Сравните 2 запроса, созданные по-разному, но выдающие один и тот же результат.

Выполните этот же запрос с другими значениями параметров.

Запрос: список фирм из города Пенза

Откройте Конструктор запросов. В качестве исходных данных используйте таблицу Клиенты. Выберите поля Название_фирмы, Город, Индекс, Адрес и Телефон. Для поля Город выберите условие: Пенза (Рис. 22). Выполните запрос. Сохраните запрос под именем Фирмы Пензы.

Рис. 22. Параметры для запроса по фирмам Пензы

Запрос: список агентов, стаж работы которых не превышает 5 лет

Откройте Конструктор запросов. Откройте таблицу Сотрудники. Выберите следующие поля: ФИО и Стаж. Для поля Стаж укажите условие отбора (Рис. 23):

<=5

Выполните запрос. Сохраните запрос под именем Стаж до 5 лет.

Рис. 23. Выборка по стажу

Запрос: список сотрудников старше 25 лет

Откройте Конструктор запросов. Откройте таблицу Сотрудники. Выберите следующие поля: ФИО, Дата_рождения. Дополнительно в следующем поле введите формулу с помощью построителя:

DateDiff("d";[Сотрудники]![Дата_рождения];Now())

Функция DateDiff выбирается в группе Функции → Встроенные функции → Дата/время. Эта функция позволяет определить разницу между двумя датами. Синтаксис этой функции следующий (обязательные аргументы):

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

где:

interval — единица измерения расчета. Значение d, например, измеряет разницу в днях. Значение заключается в парные кавычки.

date1, date2 — даты, участвующие в расчете. Из date2 вычитается date1.

Остальные аргументы необязательные.

Аргумент date1 это дата рождения сотрудника. Аргумент date2 это текущая дата. Функция текущей даты и времени называется Now(). У функции нет аргументов, так как текущая дата берется из системной даты. Назовите это поле Возраст.

Возраст измеряется в днях. Поэтому у 25-летнего сотрудника возраст будет 25*365=9125 дней (Рис. 24). Сохраните запрос под именем Сотрудники старше 25 лет (Рис. 25).

Рис. 24. Выборка по возрасту

Рис. 25. Результат запроса

Сразу после этого обычно делаются следующие замечания: более привычно считать в годах, а не в днях, и как округлить возраст до некоторого приемлемого значения.

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

DateDiff("d";[Дата_рождения];Now())/365

Условие отбора измените на 25. Выполните запрос: теперь в поле Возраст находится много символов решетки: увеличьте ширину этого поля, чтобы увидеть результат. Теперь результат действительно выводится в годах, но с множеством знаков после запятой. Знаков может быть так много, что они могут даже не поместиться по ширине монитора. Поэтому это число нужно как то ограничить. Снова войдите в Конструктор и Построитель. Функция округления называется ROUND. В качестве обязательного аргумента нужно указать саму формулу, которую нужно округлить. Если число знаков после запятой не указано, то число округляется до целого. Поэтому укажем, что округлять нужно до 1 знака после запятой:

Round((DateDiff("d";[Дата_рождения];Now())/365);1)

Закройте построитель, выполните запрос. Возможен запрос с отключенным расчетным полем Возраст.

Запрос: список клиентов, не получивших скидку

Откройте Конструктор запросов. Откройте таблицу Клиенты. Выберите следующие поля: Название_фирмы, Код_агента, Скидка. Для поля Скидка укажите условие отбора: 0. Выполните запрос. Сохраните запрос под именем Клиенты без скидки.

Более сложный запрос: вывести клиентов, получивших скидку. В этом случае нужно указать условие отбора:

<>0

То есть здесь указывается: не равно 0. Еще один способ: выделите пустую строку с условием отбора. Вызовите Построитель. Нажмите на кнопку Not, то есть Нет (или Не). Введите с клавиатуры 0. Нажмите на кнопку ОК. В условии отбора будет выведено:

Not 0

То есть в формуле указано: не 0. Выполните запрос.

Запрос: заказы за последнюю неделю текущего месяца

Так как в учебном курсе невозможно угадать срок выполнения расчетов студентами, то указанные сроки в нашем примере придется скорректировать вручную. Для этого откройте таблицу Заказы и в поле Дата измените даты заказа (примерно в 10-15 записях) так, чтобы они были датами за предыдущую неделю на момент создания данного запроса (то есть датами, отстоящими от текущей даты не более, чем 7 дней).

Откройте Конструктор запросов. Откройте таблицу Заказы. Выберите следующие поля: Месяц, Дата, Количество, Код_тура.

Сначала определим, что такое «последняя неделя». Это дни, отстоящие от текущей даты до 7 дней. Значит, чтобы найти записи за этот период нужно от текущей даты вычесть 7 дней: все записи в диапазоне от текущей даты и максимум 7 дней будут считаться записями за последнюю неделю. В строке условия отбора откройте Построитель. Нам потребуется оператор Between… And… (Между… и…), который находится в группе Операторы → Сравнения. Вставьте этот оператор в формулу:

Between Выражение And Выражение

Вместо слова Выражение мы должны подставить функции текущей даты. Текущую дату можно подставить с помощью функции Date(). Выделите первое «Выражение». Перейдите в группу Функции → Встроенные функции → Дата/время. На место выделенного вставьте функцию Date(). Выделите второе «Выражение». Введите там следующую конструкцию:

(Date()-7)

Функция Date() вводится также из списка функций даты и времени: вручную вводится только цифра 7, так как минус также вставляется из списка операторов. В какой последовательности указывать искомый диапазон не имеет никакого значения. То есть можно указать так:

Between (Date()-7) And Date()

а можно так:

Between Date() And (Date()-7)

Выполните запрос. Сохраните его под именем «Заказы за текущую неделю». Помните, что за время, прошедшее с момента создания запроса и представлением готовых расчетов преподавателю, текущая неделя может измениться и тогда снова придется вручную изменять даты оформления заказов: поэтому готовую работу нужно предоставить как можно быстрее.

Запрос: список телефонов клиентов, оформивших заказ вчера

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

Откройте Конструктор запросов. Откройте таблицы Заказы и Клиенты. Из таблицы Клиенты выберите следующие поля: Название_фирмы, Телефон. Из таблицы Заказы выберите поле Дата. В условиях отбора записей в поле Дата с помощью Построителя создайте выражение:

Date()-1

Выполните запрос. Сохраните его под именем «Вчерашние заказы». Помните, что за время, прошедшее с момента создания запроса и представлением готовых расчетов преподавателю, вчерашнее число может измениться и тогда снова придется вручную изменять даты оформления заказов.

Запрос: анализ популярности туров по России в апреле в г. Пенза

Откройте Конструктор запросов. Откройте таблицы Заказы, Клиенты и Туры. Из таблицы Заказы выберите поля Месяц и Количество. Из таблицы Туры выберите поле Тип_тура. Из таблицы Клиенты выберите поле Город.

Введите условия отбора: в Месяц — Апрель; Тип_тура — Россия; Город — Пенза. В одном и том же месяце может быть несколько записей, удовлетворяющим условиям запроса. Например, в августе месяце из Пензы тысячи людей уезжают на отдых в Сочи. Если сейчас запустить созданный запрос, то может оказаться, что по одному туристическому направлению может оказаться несколько записей. Суммировать общее значение придется вручную. Чтобы указать программе, что найденные одинаковые количества путевок нужно суммировать, а результат выводить одной строкой, необходимо выполнить следующие действия: Щелкните правой клавишей мыши в любой свободной ячейке любого столбца, даже пустого. Выполните команду Итоги. Между строками Имя таблицы и Сортировка появится еще одна строка, которая называется Групповая операция. В каждом непустом столбце появляется значение Группировка. В поле Количество щелкните по значению Группировка. После этого в правой части ячейки появляется миниатюрная треугольная кнопочка. Нажмите на нее и в открывшемся списке выберите значение Sum, то есть суммирование строк с одинаковыми значениями.

Выполните запрос. Сохраните его под именем «Туры по России в апреле в Пензе».

Запрос: объем продаж агента «Павлухина Мария Сергеевна» за апрель

Несмотря на кажущуюся простоту это сложный запрос. Откройте Конструктор запросов. Откройте таблицы Заказы, Сотрудники и Туры. Из таблицы Сотрудники выберите поле ФИО. Из таблицы Заказы выберите поле Месяц. Из таблицы Туры ничего выбирать не нужно: в этом то вся сложность и необычность запроса. Дело в том, что мы собираемся рассчитать объем продаж, а для этого нужно знать 2 аргумента: Количество и Цена. Аргумент Количество можно взять из таблицы Заказы. А вот аргумент Цена находится в таблице Туры, поэтому эта таблица должна быть открыта. Если в предыдущих запросах мы использовали поля в явном виде, то здесь мы впервые используем поле в неявном виде, в данном случае — в формуле. Если таблица Туры не будет открыта, то программа не сможет прочитать цены туров и попытается это выяснить в окне, похожем на параметрический.

В следующем пустом поле (третье поле слева) в строке Поле поместите курсор и вызовите Построитель. Откройте группу Таблицы и откройте список полей таблицы Заказы. Двойным щелчком вставьте поле Количество. Вставьте символ умножения (*). Откройте список полей таблицы Туры. Двойным щелчком вставьте поле Цена. Данная формула рассчитывает стоимость тура, оформленным одним клиентом. Но нам нужна сумма всех стоимостей. Для этого откройте группу Функции → Встроенные функции → Статистические. Поместите курсор в начало формулы. Двойным щелчком вставьте функцию Sum. Удалите символы «expr»). Установите курсор в конце формулы. Вставьте закрывающуюся круглую скобку. Полная формула должна быть такой:

Sum([Заказы]![Количество]*[Туры]![Цена])

Нажмите на кнопку ОК. Выделите автоназвание поля Выражение1 и вместо него введите название Продажи.

Щелкните правой клавишей мыши в любой свободной ячейке любого столбца, даже пустого. Выполните команду Итоги. Между строками Имя таблицы и Сортировка появится еще одна строка, которая называется Групповая операция. В каждом непустом столбце появляется значение Группировка. В поле Продажи щелкните по значению Группировка. После этого в правой части ячейки появляется миниатюрная треугольная кнопочка. Нажмите на нее и в открывшемся списке выберите значение Выражение. Выполните запрос. Сохраните его под именем «Продажи Павлухиной за апрель».

Удаление записей из БД

В таблицу Клиенты введите новую запись:

Код_клиента	Название_фирмы	Код_агента	Индекс	Город	Адрес	Телефон	Скидка
21	Белый заяц	Иванов Иван Иванович	111111	Москва	Алтынная, 1	( - )111-11-11	2,00%

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

Откройте Конструктор запросов. Откройте таблицу Клиенты. Выберите поля: Код_клиента, Название_фирмы, Город, Телефон. В условие отбора для поля Название_фирмы укажите параметрический запрос:

[Введите название фирмы]

В условие отбора для поля Город укажите параметрический запрос:

[Введите город]

На риббоне Конструктор на панели Тип запроса нажмите кнопку Удаление. В столбцах полей исчезнут строки Сортировка и Вывод на экран, но будет добавлена строка Удаление. Выполните запрос. Укажите название фирмы: Белый заяц. Укажите город: Москва. После этого программа сообщает, сколько записей найдено: 1 и предупреждает об опасности удаления записей из БД, так как восстановить их потом будет невозможно, так как команда Назад после создания запроса будет недоступна.

Самостоятельно введите в таблицу Клиенты новую фирму. В таблицу Заказы введите новые заказы с участием этой фирмы. Затем с помощью запроса удалите эту фирму: удалится не только запись об этой фирме из таблицы Клиенты, но и все упоминания об этой фирме в таблице Заказы.

Создание запроса на обновление

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

Откройте Конструктор запросов. Откройте таблицу Туры. Выберите поля: Наименование_тура, Цена. В условиях отбора поля Наименование_тура создайте параметрическое условие:

[Введите тур]

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

[Туры]![Цена]*(1+[проценты])

Разбор формулы мы начнем с выражения (1+[проценты]), так как именно в этом месте абсолютное большинство студентов делают ошибки. Многие просто умножают цену на процент изменения цены. В результате создается не цена, а изменение цены и это изменение записывается на место цены. Например, цена была 100 рублей. Процент повышения 10%. Если формула будет: 100*10%, то получится всего 10 рублей, хотя должно было получиться 110 рублей (100*(1+10%)).

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

При выполнении запроса сначала запрашивается процент изменения цены и только после этого запрашивается название тура. Процент представляет собой 1/100 от числа (символ процента вводить нельзя, так как это будет ошибкой из-за несоответствия типов данных). Процент изменения вводится с дробными частями. Например, нужно вводить не 2, а 0,02. В качестве десятичного знака используется запятая.

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