Перекрестные запросы (кросс-таблицы)
Если необходимо объединить данные в таблицу, в которой заголовками столбцов являются значения некоторых полей таблицы базы данных, то используются перекрестные запросы. Перекрестные запросы позволяют более компактно, чем обычные запросы, отображать данные, объединяя однотипную информацию. Представим в виде перекрестного запроса количество потребляемых магазинами комплектующих изделий для компьютеров. Вся нужная информация содержится в одной таблице ПОТРЕБ, которую и выбираем в спецификации запроса в окне конструктора запросов. Из этой таблицы берем все три имеющиеся поля. Вводим команду меню Запрос / Перекрестный. В спецификации запроса появляется строка Перекрестная таблица и строка Групповая операция. В строке Перекрестная таблица в колонке н_маг щелкаем мышью и в раскрывшемся списке указываем пункт Заголовки столбцов, это означает, что названия комплектующих изделий будут записаны в заголовках столбцов. В строке Перекрестная таблица в колонке н_маг щелкаем мышью и в раскрывшемся списке указываем пункт Заголовки строк, это означает, что названия магазинов будут записаны в заголовках строк. В строке Перекрестная таблица щелкаем мышью в колонке ПРОИЗВ и в раскрывшемся списке вводим пункт Значение, что означает, что на пересечении названия магазинов и названия комплектующих изделий будут записаны соответствующие числа из колонки таблицы ПОТРЕБ базы данных. В строке Групповая операция в колонках н_маг, н_изд и ПОТРЕБ вводим соответственно: Группировка, Группировка, Sum . Вид окна конструктора запроса приведен на рис. 19.
|
|
|
Рис. 19
После выполнения запроса получим:
Рис. 20
Вычисления в запросах
Групповые операции
Пример: пусть имеется некоторая база данных, состоящая из одной таблицы с именем Торговые операции (рис. 21):
рис. 21
В эту таблицу в течение рабочего дня заносится каждая торговая операция одна за другой. Требуется рассчитать суммы сделок каждого продавца, приходящиеся на каждого клиента.
Р е ш е н и е
• Создадим новый запрос, построенный по таблице ТОРГОВЫЕ ОПЕРАЦИИ.
• В строку Поле спецификации запроса перетащим мышью все поля таблицы ТОРГОВЫЕ ОПЕРАЦИИ.
• Нажмем кнопку Групповые операции (с изображением знака S) в пиктографическом меню. В спецификации запроса появится новая строка с именем Групповая операция.
• Щелкнем мышью в поле Объем операции строки Групповая операция спецификации запроса. В указанной клетке спецификации появится изображение кнопки выпадающего списка.
• Откроем список и выберем операцию суммирования Sum .
• Выполним запрос, нажав кнопку с восклицательным знаком в пиктографическом меню. Спецификация запроса будет иметь следующий вид (рис. 22).
|
|
|
Рис. 22
Результат выполнения запроса имеет следующий вид:
Рис. 23
В рассматриваемом примере с помощью функции Группировки осуществлена проверка содержимого полей Фамилия продавца и Фамилия покупателя во всех записях таблицы базы данных ТОРГОВЫЕ ОПЕРАЦИИ на их совпадение и сгруппированы совпадающие записи в один блок. Отдельно для каждого блока применена функция суммирования в поле Объем операций. Результат суммирования помещен в столбце запроса Sит Объем сделки.
Действительно, совпадение содержимого полей Фамилия продавца и Фамилия покупателя произошло дважды:
• Шунтов – Иванов в первой и второй строках таблицы ТОРГОВЫЕ ОПЕРАЦИИ. В результате две этих строки заменены одной строкой, в которой в поле Sит Объем сделки помещена сумма: 7980,22+910=8890,22. Карамзин – Иванов в четвертой и в седьмой строках таблицы ТОРГОВЫЕ ОПЕРАЦИИ. В результате две строки заменены одной строкой, в которой в поле Sит Объем сделки помещена сумма: 1439,78+1235,53=2675,31. Такое упорядочение является результатом выполнения функции группировки для содержимого полей Фамилия продавца и Фамилия покупателя. Функция группировки применяется по умолчанию. Она служит для объединения идентичных записей и исключения дублирования. Если в таблице используется поле счетчика и оно включено в группировку, то все записи будут различными, так как каждая из этих записей содержит свой собственный оригинальный номер.
|
|
|
Путем комбинации критериев и функций вычислений внутри одного запроса можно получить ответы, на интересующие вас вопросы.
Пример. 1. Каков объем торговых сделок продавца Шунтова? Вид спецификации этого запроса имеет следующий вид (рис. 24).
Рис. 24
Использование функции Count (количество) в поле Фамилия покупателя позволяет получить количество записей, включенных в группу.
2. Какова величина покупок каждого клиента (рис. 25)?
Рис. 25
Использование функции Count (количество) в поле Фамилия продавца позволяет объем покупок каждого клиента.
3. Какова сумма выручки (товарооборота) за день (рис.26)?
Рис. 26
Поскольку функция группировки не встречается ни в одном поле спецификации вся таблица базы данных ТОРГОВЫЕ ОПЕРАЦИИ представляет собой один блок, в котором применены групповые операции Sum и Count . Поэтому будет выведено общее количество торговых операций, которое покажет сумму полного оборота товаров за день.
|
|
|
Таким образом, показано, что применение групповых операций позволяет проводить вычисления внутри любого поля (внутри столбца) таблицы базы данных. Эти вычисления осуществляются в соответствии с заданной функцией, обрабатывающей содержимое поля для всех записей группы. Эта функция выбирается из выпадающего списка.
Вычисляемые поля
В результате выполнения запроса MS Access позволяет не только выбирать из таблицы содержащуюся в ней информацию, но также производить вычисления и отображать результат вычисления в результирующей таблице. таким образом можно получить данные, отсутствующие в исходной таблице.
Для создания вычисляемого поля следует щелкнуть мышью в пустом заголовке поля спецификации запроса (там, где находится имя поля). В выбранной таким образом клетке спецификации запроса появляется текстовый курсор. С клавиатуры вводится выражение, операндами которого являются имена полей объединенной таблицы. С вычисляемыми полями можно проводитьвсе перечисленные выше операции по фильтрации и групповые операции. Вычисляемые поля являются мощным средством расчетов в среде Access. Рассмотрим пример: Пусть в базе данных СКЛАД нужно определить, на какую сумму поставила продукции каждая ФИРМА?
Для разработки такого запроса достаточно использовать всего одну таблицу КОМПИЗД с полями н_фир, ЦЕНА, ПРОИЗВ. Выведем в область спецификации запрос поле н_фир. Для создания вычисляемого поля щелкаем мышью на следующем пустом заголовке поля и вводим с клавиатуры следующее выражение: ЦЕНА*ПРОИЗВ и нажимаем на клавишу [Enter]. Выражение в заголовке вычисляемого поля примет следующий вид.
Выражение1: [ЦЕНА]*[ПРОИЗВ], где Выражение 1 - имя вычисляемого поля, присвоенное по умолчанию.
Так как имеются ФИРМЫ, выпускающие не один, а несколько видов комплектующий изделий, а нужно найти сумму всех выпускаемых изделий, вводим групповые операции с функцией группировки в поле н_фир и с функцией суммирования в вычисляемом поле. На экран выводим оба поля н_фир и выражение1. Вид конструктора запроса приведен на рис. 27. Запрос выполните самостоятельно.
Рис. 27
Зачетные задания
1. Вывести на экран и скопировать в свой отчет по лабораторной работе всю связанную информацию отдельно для фирм и комплектующих изделий, для комплектующих изделий и магазинов, а также для фирм, комплектующих изделий и магазинов.
2. Какие комплектующие поставила каждая фирма, и на какую сумму?
3. Сколько видов комплектующих изделий выпускает каждая фирма?
4. На какую сумму выпускает каждая фирма всей продукции?
5. На какую сумму поставили комплектующих изделий все фирмы?
6. Какова стоимость поставленных винчестеров? (и любых других комплектующих изделий, указанных преподавателем).
7. Какая фирма имеет максимальную сумму реализации всей продукции и чему равна эта сумма?
8. Какое изделие имеет максимальную сумму реализации и каким фирмам оно поставляется?
9. Какие комплектующие изделия закупил каждый магазин, и на какую сумму?
10. Сколько видов комплектующих изделий закупает каждый магазин?
11. На какую сумму закупает каждый магазин всех комплектующих изделий?
12. На какую сумму закупили комплектующих изделий все магазины?
13. Какова стоимость закупленных магазинами системных плат? (и любых других комплектующих изделий, указанных преподавателем).
14. Какой магазин имеет максимальную сумму закупки комплектующих изделий и чему равна эта сумма?
15. Какое комплектующее изделие имеет максимальную цену закупки и каким магазином он закупается?
16. Какие фирмы (название, адрес и номер телефона) являются поставщиками комплектующих для каждого магазина?
17. В какой магазин поступает изделие, производимое фирмой, указанное параметрически?
18. Какая фирма поставляет продукцию, потребляемую магазином, указанным параметрически?
19. Какова разница стоимостей закупаемой у фирм и продаваемой магазинами всей продукции?
20. Какие комплектующие изделия приносят складу максимальную прибыль?
21. Какая продукция приносит складу минимальную прибыль или убытки?
22. Найти адрес фирмы, выпускающей изделия стоимостью более указанного числа «X». Число «Х» задавать в виде параметра.
23. Найти адрес магазина, потребляющего комплектующие изделия стоимостью более указанного числа «Х». Число «Х» задавать в виде параметра.
24. На какую сумму каждая фирма выпустила комплектующих изделий каждого вида? Запрос оформить в перекрестной форме.
«Торговое дело» профиль «Коммерция»
| 1. | Агарков Владимир Андреевич | |
| 2. | Блудилина Екатерина Сергеевна | |
| 3. | Елина Ольга Вячеславовна | |
| 4. | Иламанов Руслан Гурбангелдиевич | |
| 5. | Качурин Сергей Анатольевич | |
| 6. | Киселёв Андрей Сергеевич | |
| 7. | Курдюмов Владимир Владимирович | |
| 8. | Лунис Татьяна Александровна | |
| 9. | Михеев Руслан Сергеевич | |
| 10. | Савин Михаил Васильевич | |
| 11. | Страхов Роман Васильевич | |
| 12. | Шилин Родион Владимирович | |
| 13. | Ярославцева Полина Александровна |
Дата добавления: 2019-07-17; просмотров: 551; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!