Создание запросов 2 страница

¨ Примечание к бланку QBE (табл. 2.32). В БД отсутствует поле с датой выполнения заказа. Однако его значение можно получить по значениям других полей, имеющихся в БД. Данный запрос называется запросом с вычисляемым полем, так как в бланк QBE кроме полей, выбранных из таблиц, добавляется еще одно поле (Дата выполнения), которое получается путем вычислений по следующей формуле:

[Дата] + [Количество дней].

Таблица 2.32. Макет бланка QBE

Поле	Номер заказа	Наименование клиента	Наименование услуги
Имя таблицы	Заказы	Клиенты	Услуги
Сортировка	по возрастанию
Вывод на экран	Ö	Ö	Ö
Условие отбора

Продолжение табл. 2.32

Поле	Дата	Количество дней	Дата выполнения: [Дата]+[Количество дней]
Имя таблицы	Заказы	Услуги
Вывод на экран	Ö	Ö	Ö
Условие отбора			>Now()

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

1. Установить курсор в первый свободный столбец строки Поле бланка QBE.

2. Нажать кнопку Построить на панели инструментов.

Рис. 2.20. Создание вычисляемого поля
в диалоговом окне Построитель выражений

3. В открывшемся окне Построителя выражений записать приведенное выше выражение для расчета стоимости. Для того, чтобы автоматически создать данное выражение, следует раскрыть двойным щелчком папку Таблицы, активизировать таблицу Заказы, выбрать из списка поле Дата и нажать кнопку Вставить. Если поле Дата было внесено в бланк QBE заранее, то достаточно его активизировать и нажать кнопку Вставить. Знак + добавляется путем нажатия на одноименную кнопку в окне Построителя. Аналогично добавляется ссылка на поле Количество дней из таблицы Услуги. После этого нужно установить курсор в начало выражения, присвоить ему имя Дата выполнения и отделить двоеточием от самого выражения. Результат набора выражения приведен на рис. 2.20.

4. Заканчивается ввод выражения нажатием кнопки ОК.

Чтобы обеспечить решение поставленной задачи, необходимо установить ограничение на созданное поле с помощью встроенной в Access функции Now(), возвращающей текущую дату. Для ее записи в строку Условие отбора можно использовать непосредственный ввод с клавиатуры либо снова воспользоваться кнопкой Построить на панели инструментов. В окне Построителя выражений необходимо последовательно открыть папки Функции, затем Встроенные функции, в списке функций активизировать функцию Now, входящую в категорию Дата/время, и нажать кнопки Вставить, а потом ОК.

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

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

¨ в строке Условие отбора созданного столбца Месяц ввести выражение Month(#5.06.01#), т. е. везде вместо Now использовать значение даты #5.06.01#;

¨ ввести в БД несколько новых квитанций с датами, близкими к текущей системной дате компьютера.

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

Таблица 2.33. Результат выполнения запроса для текущей даты 05.06.01

Номер заказа	Наименование клиента	Наименование услуги	Дата	Количество дней	Дата выполнения
	ПКП «Сож»	Диагностика компьютера	01.06.01		06.06.01
	ПКФ «Марс»	Ремонт телевизора	01.06.01		05.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	03.06.01		06.06.01
	Магазин «Южный»	Подключение телефона	03.06.01		05.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	04.06.01		07.06.01
	ЗАО «Альбатрос»	Подключение модема	05.06.01		06.06.01

Список заказов, выполненных за текущий месяц:

¨ Источник данных: таблицы Заказы, Услуги, Клиенты.

¨ Название запроса: Список заказов за текущий месяц.

¨ Примечание к бланку QBE (табл. 2.34). В данном запросе встречается распространенная в практических задачах ситуация, когда требуется создать вычисляемое поле и наложить на него условие отбора. Номер месяца определяется с помощью функции Month, параметром которой является какая-либо дата. Так как в БД могут находиться записи прошлых лет, то необходимо аналогично организовать сравнение по году. Результат выполнения запроса представлен в табл. 2.35.

Таблица 2.34. Макет бланка QBE

Поле	Номер заказа	Наименование клиента	Наименование услуги
Имя таблицы	Заказы	Клиенты	Услуги
Сортировка
Вывод на экран	Ö	Ö	Ö
Условие отбора

Продолжение табл. 2.34

Поле	Дата	Месяц: Month([Дата])	Год: Year([Дата])
Имя таблицы	Заказы
Сортировка	по возрастанию
Вывод на экран	Ö	Ö	Ö
Условие отбора		Month(Now())	Year(Now())

Таблица 2.35. Результат выполнения запроса для текущей даты 05.06.01

Номер заказа	Наименование клиента	Наименование услуги	Дата
	ПКП «Сож»	Диагностика компьютера	01.06.01
	ПКФ «Марс»	Ремонт телевизора	01.06.01
	АПК «Верас»	Подключение компьютера	02.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	03.06.01
	Магазин «Южный»	Подключение телефона	03.06.01
	ПКЦ «Геркулес»	Вставка ДМ-блока	03.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	04.06.01
	ЗАО «Альбатрос»	Подключение модема	05.06.01

Список заказов с номерами от 520 до 523 или за текущий месяц:

¨ Источник данных: таблицы Заказы, Услуги, Клиенты.

¨ Название запроса: Список заказов с номерами от 520 до 523 или за текущий месяц.

¨ Примечание к бланку QBE (табл. 2.36). Условие в запросе с вычисляемым полем может быть составным. В данном случае отдельные условия по различным полям объединяются операцией «или» и поэтому располагаются в разных строках бланка QBE. Результат выполнения запроса представлен в табл. 2.37.

Таблица 2.36. Макет бланка QBE

Поле	Номер заказа	Месяц: Month([Дата])	Год: Year([Дата])
Имя таблицы	Заказы
Сортировка	по возрастанию
Вывод на экран	√
Условие отбора	>=520 and <=523
или		Month(Now())	Year(Now())

Продолжение табл. 2.36

Поле	Дата	Наименование услуги	Наименование клиента
Имя таблицы	Заказы	Услуги	Клиенты
Сортировка
Вывод на экран	Ö	Ö	Ö
Условие отбора
или

Таблица 2.37. Результат выполнения запроса

Номер заказа	Наименование клиента	Наименование услуги	Дата
	ПКП «Сож»	Ремонт видеомагнитофона	25.05.01
	ПКП «Монстр»	Установка антенны	25.05.01
	АПК «Верас»	Установка антенны	25.05.01
	Магазин «Южный»	Ремонт телевизора	25.05.01
	ПКП «Сож»	Диагностика компьютера	01.06.01
	ПКФ «Марс»	Ремонт телевизора	01.06.01
	АПК «Верас»	Подключение компьютера	02.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	03.06.01
	Магазин «Южный»	Подключение телефона	03.06.01
	ПКЦ «Геркулес»	Вставка ДМ-блока	03.06.01
	ПКЦ «Геркулес»	Монтаж ЛВС	04.06.01
	ЗАО «Альбатрос»	Подключение модема	05.06.01

Список услуг со стоимостью, увеличенной на несколько процентов (параметр запроса) относительно исходной:

¨ Источник данных: таблица Услуги.

¨ Название запроса: Список переоцененных услуг.

¨ Примечание к бланку QBE (табл. 2.38). Это комбинация запроса с параметром, названным на сколько процентов увеличить, и вычисляемым полем Новая стоимость, которое определяется выражением

[Стоимость] × (100 + [на сколько процентов увеличить]): 100.

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

Таблица 2.38. Макет бланка QBE

Поле	Наименование услуги	Стоимость
Имя таблицы	Услуги	Услуги
Сортировка
Вывод на экран	Ö	Ö

Продолжение табл. 2.38

Поле	Новая стоимость: [Стоимость]* (100+[на сколько % увеличить])/100
Имя таблицы
Сортировка	по убыванию
Вывод на экран	Ö

Таблица 2.39. Результат выполнения запроса
при значении параметра, равном 20 %

Наименование услуги	Стоимость	Новая стоимость
Ремонт видеомагнитофона
Монтаж ЛВС
Ремонт телевизора
Диагностика компьютера
Подключение компьютера
Установка антенны
Подключение телефона
Подключение модема
Вставка ДМ-блока

Общая стоимость услуг, оказанных каждому клиенту:

¨ Источник данных: таблицы Услуги, Клиенты, Заказы.

¨ Название запроса: Стоимость услуг клиентам.

¨ Примечание к бланку QBE (табл. 2.40). Данный вид запроса называется запросом с группировкой, так как в нем записи группируются (собираются) по условию группировки (по клиенту), а в пределах группы рассчитывается, например, общая стоимость всех оказанных ему услуг. Полученное значение попадает в результат выполнения запроса одной строкой (например, наименование клиента — общая стоимость оказанных ему услуг). Создание запроса с группировкой обеспечивается строкой Групповая операция в бланке QBE. Если она отсутствует, то необходимо нажать на одноименную кнопку на панели инструментов.

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

Таблица 2.40. Макет бланка QBE

Поле	Наименование клиента	Стоимость
Имя таблицы	Клиенты	Услуги
Групповая операция	Группировка	Sum
Сортировка	по возрастанию
Вывод на экран	Ö	Ö

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

Таблица 2.41. Результат выполнения запроса

Наименование клиента	Sum_Стоимость
АПК «Верас»
ЗАО «Альбатрос»
Магазин «Южный»
ПКП «Монстр»
ПКП «Сож»
ПКФ «Марс»
ПКЦ «Геркулес»

Общая стоимость оплаченных заказов по услуге каждого вида:

¨ Источник данных: таблица Услуги, Заказы.

¨ Название запроса: Стоимость оплаченных услуг по видам.

¨ Примечание к бланку QBE (табл. 2.42). В результат выполнения запроса с группировкой могут попадать не все записи. Ограничение обеспечивается условием по необходимым полям. Для них в строке Групповая операция должно быть указано значение Группировка. В данном случае следует отобрать записи по значению Истина в поле Оплачено. Результат выполнения запроса представлен в табл. 2.43.

Таблица 2.42. Макет бланка QBE

Поле	Наименование услуги	Стоимость	Оплачено
Имя таблицы	Услуги	Услуги	Заказы
Групповая операция	Группировка	Sum	Группировка
Сортировка	по возрастанию
Вывод на экран	Ö	Ö
Условие отбора			Да

Таблица 2.43. Результат выполнения запроса

Наименование услуги	Sum_Стоимость
Диагностика компьютера
Вставка ДМ-блока
Ремонт видеомагнитофона
Ремонт телевизора
Подключение компьютера
Подключение модема
Подключение телефона
Монтаж ЛВС
Установка антенны

Общая стоимость оплаченных услуг, оказанных за каждый месяц:

¨ Источник данных: таблицы Заказы, Услуги.

¨ Название запроса: Стоимость услуг за месяц по видам.

¨ Примечание к бланку QBE (табл. 2.44). Запрос с группировкой может быть создан на основе вычисляемого поля. Номер месяца возвращается функцией Month от поля Дата. Общий итог рассчитывается по полю Стоимость. Как и в предыдущем случае, необходимо еще ввести ограничение по оплате. Результат выполнения запроса представлен в табл. 2.45.

Таблица 2.44. Макет бланка QBE

Поле	Месяц:Month([Дата])	Стоимость	Оплачено
Имя таблицы		Услуги	Заказы
Групповая операция	Группировка	Sum	Группировка
Сортировка	по возрастанию
Вывод на экран	Ö	Ö
Условие отбора			Да

Таблица 2.45. Результат выполнения запроса

Месяц	Sum_Стоимость

Общая стоимость заказов, выполненных предприятием для каждого клиента по каждой услуге:

¨ Источник данных: таблицы Заказы, Услуги, Клиенты.

¨ Название запроса: Стоимость заказов по клиентам и услугам.

¨ Примечание к бланку QBE (табл. 2.46). В данном запросе необходимо сгруппировать данные по двум измерениям: строкам и столбцам. Задача решается с помощью запроса, называемого перекрестным. На пересечении каждой строки и каждого столбца рассчитывается итоговое значение, соответствующее заголовкам строки и столбца. После выбора необходимых таблиц в режиме Конструктора необходимо выполнить команду меню Запрос/Перекрестный. В результате ее выполнения в бланк запроса будут добавлены две новые строки: Групповая операция и Перекрестная таблица и убрана строка Вывод на экран.

В строку Полебланка QBE нужно поместить те поля, содержимое которых будет использоваться в качестве заголовков строк перекрестной таблицы (в данном примере — это Наименование услуги), заголовков столбцов перекрестной таблицы (Наименование клиента) и расчетных значений (Стоимость). Напротив каждого из таких полей в строке Перекрестная таблица бланка QBE из раскрывающегося списка выбираются соответствующие значения: Заголовки строк, Заголовки столбцов, Значение. В данном запросе записи группируются по полям Наименование услуги и Наименование клиента, поэтому в строке Групповая операция напротив этих полей записывается слово Группировка. Общий итог рассчитывается по полю Стоимость. Следовательно, напротив этого поля в строке Групповая операция выбирается значение Sum, соответствующее операции суммирования. Результат выполнения запроса представлен в табл. 2.47.

Таблица 2.46. Макет бланка QBE

Поле	Наименование услуги	Наименование клиента	Стоимость
Имя таблицы:	Услуги	Клиенты	Услуги
Групповая операция	Группировка	Группировка	Sum
Перекрестная таблица	Заголовки строк	Заголовки столбцов	Значение
Сортировка	по возрастанию

Таблица 2.47. Результат выполнения запроса

Наименование услуги	АПК «Верас»	ЗАО «Альбатрос»	Магазин «Южный»	ПКП «Монстр»	ПКП «Сож»	ПКЦ «Геркулес»	ПКФ «Марс»
Вставка ДМ-блока
Диагностика компьютера
Монтаж ЛВС
Подключение компьютера
Подключение модема
Подключение телефона
Ремонт видеомагнитофона
Ремонт телевизора
Установка антенны

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

¨ Источник данных: таблицы Заказы, Услуги, Клиенты.

¨ Название запроса: Стоимость оплаченных заказов по услугам и месяцам.

¨ Примечание к бланку QBE (табл. 2.48). Поскольку необходима группировка по двум полям, то задача решается с помощью перекрестного запроса. Аналогично запросу с группировкой в перекрестных запросах можно использовать вычисляемые поля и условия на значения, что ниже и демонстрируется. Результат выполнения запроса представлен в табл. 2.49.

Таблица 2.48. Макет бланка QBE

Поле	Наименование услуги	Месяц:Month(Дата)
Имя таблицы	Услуги
Групповая операция	Группировка	Группировка
Перекрестная таблица	Заголовки строк	Заголовки столбцов
Сортировка	по возрастанию
Условие отбора

Продолжение табл. 2.48

Поле	Стоимость	Оплачено
Имя таблицы	Услуги	Заказы
Групповая операция	Sum	Группировка
Перекрестная таблица	Значение
Сортировка
Условие отбора		Да

Таблица 2.49. Результат выполнения запроса

Наименование услуги
Диагностика компьютера
Вставка ДМ-блока
Ремонт видеомагнитофона
Ремонт телевизора
Подключение компьютера
Подключение модема
Подключение телефона
Монтаж ЛВС
Установка антенны

2.3.3.2. Активные запросы

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

Порядок создания активных запросов почти ничем не отличается от создания запросов на выборку. Отличие состоит в том, что при создании такого запроса требуется дополнительно указать вид запроса (создание таблицы, добавление, удаление, обновление) с помощью команды меню Запросили кнопки Тип запроса в окне Конструктора запросов.

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

Создание новой таблицы, содержащей все заказы:

¨ Источник данных: запрос Список всех заказов.

¨ Название запроса: Создание таблицы со всеми заказами.

¨ Примечание к бланку QBE (табл. 2.50). После выполнения команды Запрос/Создание таблицы появляется диалоговое окно, в котором необходимо указать имя создаваемой таблицы (например, Все заказы). Затем, как в запросе на выборку, в строке Полебланка QBE поместить те поля запроса, значения которых будут храниться в создаваемой таблице. Если в выходной таблице необходимы все поля, то достаточно разместить в строке Поле бланка QBE символ *, находящийся в списке полей запроса. Можно также добавить поле для сортировки (Номер заказа), но не отображать его, так как оно в противном случае повторится в выходном наборе.

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

Дата добавления: 2014-11-16