Реализация базы данных в среде СУБД Access 2 страница

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

Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
	ЗАО "Альбатрос"	05.06.04	Подключение модема		Нет

Список заказов, выполненных за определенный день,
значение которого является параметром запроса

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

Название запроса: Список заказов за день.

Макет бланка QBE:

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

Примечание к бланку QBE. В запросе с параметром в строку Условие отбора напротив имени поля, по которому задается условие, записывается не конкретное значение, а указание его ввода. Оно представляет собой произвольный комментарий, записанный в квадратных скобках.

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

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

Номер заказа	Дата	Наименование клиента	Наименование услуги	Стоимость
	27.05.04	ЗАО "Альбатрос"	Подключение телефона
	27.05.04	ЗАО "Альбатрос"	Установка антенны
	27.05.04	ПКП "Монстр"	Подключение компьютера

Список заказов, выполненных за определенный период,
границы которого являются параметрами запроса

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

Название запроса: Список заказов за период.

Макет бланка QBE:

Поле:	Номер заказа	Дата	Наименование клиента	Наименование услуги	Стоимость
Имя таблицы:	Заказы	Заказы	Клиенты	Услуги	Услуги
Вывод на экран:	√	√	√	√	√
Условие отбора:		>=[Начальная дата] And <= [Конечная дата]

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

Результат выполнения запроса (для периода с 01.06.04 по 03.06.04):

Номер заказа	Дата	Наименование клиента	Наименование услуги	Стоимость
	01.06.04	ПКП "Сож"	Диагностика компьютера
	01.06.04	ПКФ "Марс"	Ремонт телевизора
	02.06.04	АПК "Верас"	Подключение компьютера
	03.06.04	ПКЦ "Геркулес"	Монтаж ЛВС
	03.06.04	Магазин "Южный"	Подключение телефона
	03.06.04	ПКЦ "Геркулес"	Вставка ДМ–блока

Список оплаченных заказов, оказанных определенному клиенту,
наименование которого является параметром запроса

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

Название запроса: Список оплаченных заказов клиенту.

Макет бланка QBE:

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

Примечание к бланку QBE. В этом запросе условия также связаны отношением и, причем одно из них задается в строке Условие отбора, а второе является параметром запроса.

Результат выполнения запроса (для клиента АПК «Верас»):

Номер заказа	Наименование клиента	Дата	Наименование услуги	Стоимость	Оплачено
	АПК "Верас"	25.05.04	Установка антенны		Да
	АПК "Верас"	26.05.04	Вставка ДМ–блока		Да
	АПК "Верас"	29.05.04	Подключение модема		Да

Список заказов, которые еще не выполнены

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

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

Макет бланка QBE:

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

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

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

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

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

· нажать кнопку Построитьна панели инструментов;

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

· закончить ввод выражения нажатием кнопки ОК.

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

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

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

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

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

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

Результат выполнения запроса (для текущей даты 5.06.04):

Номер заказа	Наименование клиента	Наименование услуги	Дата	Количество дней	Дата выполнения
	ПКП "Сож"	Диагностика компьютера	01.06.04		06.06.04
	ПКФ "Марс"	Ремонт телевизора	01.06.04		05.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	03.06.04		06.06.04
	Магазин "Южный"	Подключение телефона	03.06.04		05.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	04.06.04		07.06.04
	ЗАО "Альбатрос"	Подключение модема	05.06.04		06.06.04

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

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

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

Макет бланка QBE:

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

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

Результат выполнения запроса (для текущей даты 5.06.04):

Номер заказа	Наименование клиента	Наименование услуги	Дата
	ПКП "Сож"	Диагностика компьютера	01.06.04
	ПКФ "Марс"	Ремонт телевизора	01.06.04
	АПК "Верас"	Подключение компьютера	02.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	03.06.04
	Магазин "Южный"	Подключение телефона	03.06.04
	ПКЦ "Геркулес"	Вставка ДМ–блока	03.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	04.06.04
	ЗАО "Альбатрос"	Подключение модема	05.06.04

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

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

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

Макет бланка QBE:

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

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

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

Номер заказа	Наименование клиента	Наименование услуги	Дата
	ПКП "Сож"	Ремонт видеомагнитофона	25.05.04
	ПКП "Монстр"	Установка антенны	25.05.04
	АПК "Верас"	Установка антенны	25.05.04
	Магазин "Южный"	Ремонт телевизора	25.05.04
	ПКП "Сож"	Диагностика компьютера	01.06.04
	ПКФ "Марс"	Ремонт телевизора	01.06.04
	АПК "Верас"	Подключение компьютера	02.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	03.06.04
	Магазин "Южный"	Подключение телефона	03.06.04
	ПКЦ "Геркулес"	Вставка ДМ–блока	03.06.04
	ПКЦ "Геркулес"	Монтаж ЛВС	04.06.04
	ЗАО "Альбатрос"	Подключение модема	05.06.04

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

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

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

Макет бланка QBE:

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

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

[Стоимость]*(100+[на сколько % увеличить])/100

Результат выполнения запроса (при значении параметра, равном 20%):

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

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

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

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

Макет бланка QBE:

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

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

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

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

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

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

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

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

Макет бланка QBE:

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

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

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

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

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

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

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

Макет бланка QBE:

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

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

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

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

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

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

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

Макет бланка QBE:

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

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

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

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

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

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

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

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

Макет бланка QBE:

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

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

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

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

Создание активных запросов

Дата добавления: 2015-05-09