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

Перекрестные запросы позволяют группировать данные и представлять их для анализа в наглядном и компактном виде.

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

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

Наименование	Альвеола	Бобруйскмебель	……..
Диван "Диана 3"
Диван "Сорренто"
………

· Выберите режим создания запроса с помощью конструктора;

· В диалоговом окне Добавление таблицы добавьте таблицы Товары,Поставщик_товар и Поставщик.

· Переместите из таблицы Поставщик поля Название из таблицы Товары поля Наименование, а из таблицы Поставщик_товар поле Количество;

· Выполните команду Запрос/Перекрестный либо нажмите кнопку

на панели инструментов, выберите тип запроса

. Обратите внимание, что в бланке запроса появились строки Групповая операция и Перекрестная таблица;

· В бланке запроса необходимо произведите следующие действия:

Для поля Наименование в строке Групповая операция оставьте значение Группировка, а в строке Перекрестная таблица выберите Заголовки строк;

Для поля Название в строке Групповая операция оставьте значение Группировка, а в строке Перекрестная таблица выберите Заголовки столбцов;

Для поля Количество в строке Групповая операция выберите Sum, а в строке Перекрестная таблица выберите Значение.

· Выполните и сохраните запрос с именем Поставки перекрестный.

1 Создайте в БД три новые таблицы Должности (рисунок 15), Удержания (рисунок 16), Начисления (рисунок 17).

2 Далее необходимо проанализировать связи созданных таблиц с существующей структурой БД. Связь между отношениями Сотрудники и Должности М:1, сотрудник может занимать одну должность, но на предприятии могут быть несколько сотрудников имеющих одинаковую должность. Связь между Сотрудники и Удержания М:М, с одного сотрудника могут производиться различные виды удержаний, а один и тот же вид удержаний при расчете заработной платы вычитается для многих сотрудников. Связь между Сотрудники и Начисления М:М. Для реализации связи М:М в Microsoft Access необходимо добавить дополнительные таблицы (рисунок 18, 19).

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

4 Добавить новые таблицы в схему данных и связать их в соответствии со схемой, приведенной на рисунке 20.

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

6 Создать различные виды запросов к полученной базе данных.

Рисунок 18 – Структура таблицы Связь Начислений

Рисунок 19 – Структура таблицы Связь Удержаний

Рисунок 20 – Добавления новых таблиц в существующую схему данных

Запрос 1 Вывести из БД сведения о Названии, Адрес и Телефон для всех поставщиков.

Запрос 2 Вывести информацию о покупателях фамилии, которых начинаются на "П" и оператор "Life".

Запрос 3 ывести информацию о товарах, купленных в ноябре.

Запрос 4 Вывести информацию о покупателях фамилии которых начинаются с "А-П", заканчиваются на "я".

Запрос 5 Вывести информацию о поставщиках не зарегистрированных в г. Минске.

Запрос 6 Вывести информацию о поставщиках, которые не поставили ни одного вида товара.

Запрос 7 Вывести информацию о поставщиках в названии которых присутствует буква "а".

Запрос 8 Вывести информацию о поставках за зиму.

Запрос 9 Вывести информацию о товарах стоимость которых находится в пределах от 250000 до 6500000 рублей.

Запрос 10 Вывести информацию о сотрудниках имеющих должность кассир.

Запрос 11 Вывести информацию о сотрудниках следующих должностей: директор и бухгалтер.

Запрос 12 Вывести информацию о сотрудниках, а также размер их оклада.

Запрос 13 Вывести информацию о товарах наименование которых начинается на Т, С, Д а стоимость в пределах от 500000 до 2500000.

Запрос 14 Создайте запрос выводящий информацию о товарах, предварительно запрашивая наименования.

Запрос 15 Создайте запрос выводящий информацию о сотрудниках, предварительно запрашивая должность.

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

Запрос 17 Вывести информацию о поставках товаров, рассчитать стоимость поставки.

Запрос 18 Рассчитать общее количество поставщиков.

Запрос 19 Общее количество поставок по каждому поставщику.

Запрос 20 Общее количество поставок для каждой даты.

Запрос 21 Максимальную и минимальную стоимость товаров.

Запрос 22 Общее количество купленного товара, а также общую стоимость покупки для каждого покупателя.

Запрос 23 Общее количество купленного товара.

Запрос 24 Общее количество товара по каждому наименованию.

Запрос 25 Общее количество сотрудников по каждой должности.

Запрос 27 Разработать запрос на увеличение (на 20%) окладов для всех сотрудников.

Запрос 28 Разработать запрос на увеличение размера подоходного налога на 1%.

Запрос 29 Удалить информацию о сотруднице Смирновой, предварительно поместив информацию в новую таблицу Архив.

Запрос 30 Создайте перекрестный запрос, отражающий суммарное количество поставок для каждой даты.

Запрос 31 Создайте перекрестный запрос, отражающий общую стоимость покупок для каждой даты.

Запрос 32 Создайте запрос подсчитывающий количество кассиров на предприятии.

1 Что такое запрос, и какие типы запросов поддерживает MS Access?

2 Каковы режимы создания запросов в СУБД MS Access?

4 Как задать параметры при создании запроса выборки с параметрами?

5 Для чего используются запросы с параметрами?

6 Что такое запросы действия, и какие бывают запросы действия?

7 Что такое перекрестный запрос и как он конструируется?

8 Что представляют собой запрос на создание таблицы, как он работает, какую таблицу можно создать?

9 Что такое запросы на удаление, как они конструируются? Для чего могут быть использованы запросы на удаление?

11 Какие групповые функции используются в расчетах?

1 Керри Н. Праг, Майкл Р. Ирвин. Microsoft Access. Библия пользователя: пер. с англ.:Уч. пос.–М.:Издательский дом «Вильямс», 2000.-1040с.

2 Ю.Бекаревич, Н.Пушкина. Самоучитель Access 2010. –М:BHV, 2010.-432с.

4 В.Кошелев. Access 2003. Практическое руководство.– М:Бином, 2010 -464с.

5 Кен Кук. Access 2010 для «чайников». – М: Издательский дом «Вильямс», 2011.-384с.

Лабораторная работа №1. Создание базы данных. Работа с таблицами 4

Основные понятия реляционных БД: нормализация, связи и ключи 5

Создание таблицы ПОКУПАТЕЛЬ в режиме конструктора. 17

Создание таблицы ТОВАРЫ с помощью мастера. 18

Создание таблицы ПОСТАВЩИК путём ввода данных. 20

Создание таблицы ПОСТАВЩИК_ТОВАР в режиме конструктора 21

Создание таблиц ПОКУПАТЕЛЬ_ТОВАР и СОТРУДНИКИ.. 22

Лабораторная работа №2. Создание запросов. 30

Задания и методические указания к формированию запросов. 36

№кли ента	ФИО	Пол	Адрес	Телефон	Оператор
	Петров Сергей Степанович	м	Белинского 12-54		Life
	Иванов Олег Ефремович	м	Волгоградская 10-12		MTS
	Степанова Ольга Ивановна	ж	Никифорова 14-12		Белтелеком
	Александрова Ирина Олеговна	ж	Рафиева 10-22		Velcom
	Петрова Оксана Александровна	ж	Никифорова 5-56		MTS
	Аринич Михаил Сергеевич	м	Гашкевича 2-55		Белтелеком
	Александров Андрей Иванович	м	Семашко 8-22		MTS
	Бондарчик Елена Витальевна	ж	Сердича 5-99		Белтелеком
	Комаров Василий Степанович	м	Русиянова 10-58		Life
	Кулаковская Татьяна Сергеевна	ж	Нестерова 12-69		MTS
	Каменская Татьяна Марковна	ж	Ангарская 5-52		Velcom
	Кабушко Сергей Анатольевич	м	Маяковского 55-25		Life
	Дубаневич Ирина Олеговна	ж	Ванеева 10-7		Белтелеком
	Понаморева Екатерина Семеновна	ж	Жудро 2-54		Velcom
	Сенько Ирина Павловна	ж	Плеханова 12-87		Белтелеком

№товара	Наименование	Стоимость(ед)
	Стул A1	120 000,00р.
	Стол M2	260 000,00р.
	Кресло	570 000,00р.
	Сейф 01/06/05	2 050 000,00р.
	Сейф 02/06/05	3 004 500,00р.
	Компьютерный стол М03	1 050 000,00р.
	Компьютерный стол М04	950 000,00р.
	Шкаф 1М	1 500 000,00р.
	Шкаф 2М	2 030 000,00р.
	Компьютерный стол М06	1 500 000,00р.
	Стул А2	500 000,00р.
	Стул А3	125 600,00р.
	Сейф 03/06/05	3 500 000,00р.
	Тумба "Давиль"	623 000,00р.
	Диван "Сорренто"	6 256 000,00р.
	Диван "Диана 3"	12 000 000,00р.
	Спальня "Александрина"	10 010 000,00р.
	Спальня "Валерия"	12 500 000,00р.
	Спальня "Венера"	15 000 200,00р.
	Шкаф с витриной ММ1	4 000 500,00р.
	Шкад для книг	2 030 000,00р.
	Диван угловой "Капри"	6 000 500,00р.
	Диван угловой "Стинг"	6 250 000,00р.
	Диван угловой "Принцесса"	8 960 000,00р.
	Корпусная мебель "Полонез"	11 000 000,00р.
	Корпусная мебель"Влада"	12 500 000,00р.
	Диван кровать "Фаворит"	5 680 000,00р.
	Диван кровать "Консул"	4 526 000,00р.
	Диван кровать "Корсика"	5 628 000,00р.
	Тумба "Оскар"	2 550 000,00р.
	Шкаф с витриной "Оскар"	4 580 000,00р.
	Угол "Атланта"	9 680 000,00р.
	Угол "Грация"	12 265 000,00р.
	Угол "Лондон"	10 562 000,00р.
	Стол обеденный	1 250 000,00р.

№поставщика	№товара	Количество	Дата
			25.03.2011
			26.02.2011
			15.11.2011
			17.11.2011
			15.10.2011
			15.11.2011
			15.11.2011
			16.11.2011
			16.11.2011
			17.11.2011
			16.11.2011
			17.10.2011
			17.11.2011
			25.11.2011
			20.11.2011
			21.11.2011
			20.11.2011
			10.10.2011
			12.11.2011
			11.11.2011
			02.12.2011
			05.12.2011
			06.12.2011
			02.12.2011
			25.11.2011
			25.11.2011
			01.11.2011
			05.12.2011
			07.12.2011
			05.12.2011
			06.10.2011
			17.11.2011
			05.12.2011
			07.12.2011
			12.11.2011

Таблица 12 – Записи таблицы ПОКУПАТЕЛЬ_ТОВАР

№клиента	№товара	Коли чество	Дата	Доставка	№сотруд ника
			21.11.2011	ЛОЖЬ
			25.11.2011	ИСТИНА
			25.12.2011	ИСТИНА
			01.12.2011	ИСТИНА
			25.12.2011	ЛОЖЬ
			01.12.2011	ЛОЖЬ
			02.12.2011	ИСТИНА
			08.12.2011	ИСТИНА
			12.11.2011	ИСТИНА
			06.12.2011	ИСТИНА
			12.12.2011	ЛОЖЬ
			05.12.2011	ИСТИНА
			06.12.2011	ИСТИНА
			01.12.2011	ИСТИНА
			08.12.2011	ИСТИНА
			05.12.2011	ЛОЖЬ
			07.12.2011	ЛОЖЬ
			06.12.2011	ИСТИНА
			07.12.2011	ИСТИНА
			07.12.2011	ЛОЖЬ
			07.12.2011	ЛОЖЬ

№сотруд ника	Фамилия	Имя	Отчество	Пол	Должность	Рабочий телефон
	Иванов	Иван	Иванович	м	директор
	Медведева	Наталья	Петровна	ж	бухгалтер
	Исаев	Петр	Сергеевич	м	консультант
	Смирнова	Кристина	Олеговна	ж	кассир
	Гладкова	Ирина	Валерьевна	ж	кассир
	Волков	Семен	Петрович	м	водитель

Таблица 14 - Операторы, используемые при формировании условия

Операторы	Описание
Like	Для отбора данных в текстовых полях по шаблону, заключенному в кавычки. Шаблоном может быть слово, по которому будет производиться поиск и отбор записей. Шаблоном может быть набор символов:
? * # [] [!]	любой одиночный символ в данной позиции любое количество символов в данной позиции любая цифра в данной позиции заключает допустимый диапазон символов заключает недопустимый диапазон символов
"Минск"- выбирает все записи из заданного поля со значением Минск
Like " ?#[5-8][!1-3]A'' - выбирает все записи из заданного поля со значением:* в первой позиции - произвольный символ во второй позиции - произвольная цифра в третьей позиции любое число от 5 до 8 включительно в четвертой позиции любое число кроме цифр от 1 до 3 включительно в пятой позиции буква А, после произвольные символы в любом количестве
=, >, <, >=,<=, <>	равно, больше, меньше, больше или равно, меньше или равно, неравно
And	Логическое И, задает интервал отбора из выражений, операнд:
>10and<=20 (в числовом поле выбирает записи из интервала (10-20]
Or	Логическое ИЛИ, задает альтернативы отбора из выражений, операнд:
10 or 20 or 30 (в числовом поле выбирает записи равные 10 или 20 или 30)
Like "M" or Like "A" в текстовом поле выбирает записи начинающиеся с букв М или К
Not	Логическое НЕ (отрицание) Not Like "белый" (в текстовом поле выбирает все записи кроме белый)
Between X1 And X2	Позволяет задать интервал для числового значения от Х1 до Х2 включительно. Записи:
	Between 10 And 20	(в числовом поле выбирает записи из интервала от 10 до 20 включительно)
>=10 And <=20
	In ("Mинск"; "Омск"; "Орша")	In(10;20;30)
"Минск"ог"Омск"ог"Орша"	10 or 20 or 30
Минск или Омск или Орша	10 или 20 или 30
Or	Логическое ИЛИ, задает альтернативы отбора из выражений, операнд:
10 or 20 or 30 (в числовом поле выбирает записи равные 10 или 20 или 30)
Like"M" or Like "A" в текстовом поле выбирает записи начинающиеся с букв М или К)
Not	Логическое НЕ (отрицание) Not Like "белый" (в текстовом поле выбирает все записи кроме белый)
Between X1 And X2	Позволяет задать интервал для числового значения от Х1 до Х2 включительно. Записи:
Between 10 And 20	(в числовом поле выбирает записи из интервала от 10 до 20 включительно)
>=10 And <=20
In	Позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Выбирает записи из полей со значениями
In ("Mинск"; "Омск"; "Орша")	In(10;20;30)
"Минск"ог"Омск"ог"Орша"	10 or 20 or 30
Минск или Омск или Орша	10 или 20 или 30

Таблица 15 – Операнды, используемые при формировании условий