Групповые вычисления в запросе
Занятие 5
MS Access 2010
Отбор данных из базы данных с помощью запросов (продолжение)
По окончании данного занятия вы сможете:
ü Создавать вычисляемое поле в запросе.
ü Выполнять групповые вычисления в запросе.
ü Создавать таблицу с помощью запроса.
ü Добавлять записи в таблицы с помощью запроса.
ü Редактировать данные в таблице с помощью запроса.
ü Удалять записи из таблиц с помощью запроса.
Создание вычисляемого поля в запросе
В запрос можно включать не только поля из базовых таблиц и запросов, но и создавать новые вычисляемые поля. Например, если одно и то же вычисляемое поле используется в формах или отчетах, то можно создать запрос, который создаст это поле и использовать этот запрос в качестве источника данных для форм и отчетов. Вычисляемые поля создаются с помощью выражений, согласно правилам, которые были рассмотрены ранее.
Результаты вычислений, выводящиеся в вычисляемом поле, не запоминаются в базовой таблице. Вместо этого, вычисления снова производятся всякий раз, когда выполняется запрос. Обновить вычисленные результаты вручную невозможно. Однако при внесении изменений в любое из полей, на которое ссылается вычисляемое поле, эти изменения будут отражены в вычисляемом поле.
Для определения вычисляемого поля можно использовать встроенные функции Microsoft Access или выражения, создаваемые пользователем. Каждое вычисляемое поле может содержать одно выражение. Для вычисляемого поля можно задать условие отбора и указать направление сортировки данных.
|
|
|
Для того чтобы создать вычисляемое поле в запросе, нужно:
· Открыть запрос в режиме конструктора.
· Ввести в пустую ячейку строки Поле имя для нового поля, затем символ : (двоеточие) и само выражение для вычисления.
Выражение может быть введено вручную или создано с помощью построителя выражений.
Если выражение содержит имена полей, на которые ссылается при расчетах, то их необходимо заключить в квадратные скобки.
Имя для нового поля можно не вводить. В этом случае ему будет автоматически присвоено имя «ВыражениеN», где N является целым числом, возрастающим на единицу для каждого нового вычисляемого поля в запросе.
«Выражением» всегда можно изменить на более содержательное имя, например, «Новая Цена».
· Ввести условие отбора результатов вычислений, если это необходимо.
· Задать направление сортировки результатов вычисления, если это необходимо.
· Указать нужно или нет выводить данные вычисляемого поля на экран.
Для того чтобы просмотреть для вычисляемого поля все выражение, не прибегая к прокрутке, нужно, находясь в ячейке с выражением, нажать клавиши SHIFT+F2.
|
|
|
: Задание 1.
Откройте базу данных База.Ваша фамилия, созданную ранее.
1. Откройте таблицу Договоры в режиме конструктора.
2. Добавьте в конец новое поле – Сумма договора, тип данных Денежный, 2 знака после запятой.
3. Перейдите в режим таблицы (сохранив изменения) и заполните добавленное поле следующими данными:
4. Сохраните и закройте таблицу Договоры.
5. Создайте запрос, в котором вычисляется продолжительность каждого договора в днях.
Выполнение задания.
1. В окне базы данных выберите группу Запросы.
2. Выберите в окне базы вкладку Создание.
3. В группе Запросы выберите Конструктор запросов.
4. В окне Добавление таблицы откройте вкладку Таблицы и дважды щелкните мышью на имени таблицы Договоры.
5. Закройте окно Добавление таблицы, щелкнув по кнопке Закрыть.
6. Перенесите из списка полей таблицы Договоры в бланк запроса имя поля Номер договора.
7. Введите в пустую соседнюю ячейку строки Поле выражение:
Продолжительность договора (в днях): [Дата окончания]-[Дата заключения]
8. Переключитесь в режим таблицы и посмотрите, какой отбор выполнил созданный вами запрос:
9. 
|
|
|
10. Сохраните запрос под именем Продолжительность договоров и закройте запрос.
Групповые вычисления в запросе
В запросе можно выполнять вычисления над группами записей. В следующей таблице перечислены типы вычислений, которые можно использовать для групповых операций:
| Avg | Среднее значений в поле |
| Min | Наименьшее значение в поле |
| Max | Наибольшее значение в поле |
| Sum | Сумма значений в поле |
| Count | Число непустых значений в поле |
| StDev | Стандартное отклонение значений в поле |
| Var | Дисперсия значений в поле |
| First | Значение поля из первой записи |
| Last | Значение поля из последней записи |
Для выполнения групповых вычислений в бланке запроса указываются поля, которые будут группироваться и поля, по которым будут выполняться групповые вычисления. Каждое поле, включенное в такой запрос, должно быть группирующим или вычисляемым.
Для выполнения групповых вычислений нужно:
· Открыть запрос в режиме конструктора.
· Указать таблицы, данные из которых будут обработаны создаваемым запросом.
· Поместить в бланк запроса поля, которые должны участвовать в запросе.
· Выбрать в группе Показать или скрыть команду Итоги. В бланке запроса появится строка Групповая операция. По умолчанию в ячейках строки Групповая операция всех полей содержится значение Группировка.
|
|
|
· Выбрать в раскрывающихся списках ячеек Групповая операция тип вычислений для полей, в которых необходимо выполнять групповые вычисления. Остальные поля должны остаться группирующими.
· Переключиться в режим таблицы для просмотра полученных результатов.
Можно выполнить вычисления над всеми записями таблицы. Например, можно создать запрос на поиск самого дорогого товара в указанной таблице. В этом случае бланк запроса может выглядеть так:
Бывает необходимо получить не одно итоговое значение для всех записей, а результаты по группам записей. Например, посчитать среднюю заработную плату каждой категории сотрудников. В этом случае бланк запроса может выглядеть так:
Группировать записи можно по нескольким полям. В таком случае записи группируются по порядку включения полей в бланк запроса. Например, нужно посчитать среднюю заработную плату каждого разряда каждой категории сотрудников. В этом случае бланк запроса может выглядеть так:
Всегда можно наложить условие отбора записей на группирующее поле. Если нужно выполнить вычисления для определенной группы записей, необходимо в бланке запроса задать условия отбора для группирующего поля. Например, необходимо подсчитать среднюю заработную плату инженерного состава предприятия. В этом случае бланк запроса может выглядеть так:
Условия отбора записей можно определять и для итоговых полей, т.е. полей, в которых выполняются групповые вычисления. Для того чтобы выполнить групповые вычисления, а затем отобрать записи, которые следует включить в результирующий набор записей, следует ввести соответствующее выражение в ячейку Условие отбора для итогового поля. Например, для каждой организации нужно подсчитать стоимость всех заказов и показать только те организации, у которых итоговое значение превысит 1000000. В этом случае бланк запроса может выглядеть так:
Можно задать условия отбора записей для поля, которое не является группирующим или итоговым. Для этого нужно включить это поле в бланк запроса, ввести условие отбора в строку Условие отбора и выбрать из раскрывающегося списка ячейки Групповая операция параметр Условие. В этом случае сначала выполняется отбор записей, а затем для них выполняется вычисление. Автоматически для того поля снимается флажок Вывод на экран, и поле не выводится на экран при выполнении запроса. Например, для каждой организации нужно подсчитать стоимость всех заказов, выполненных в 1999 году, не выводя календарную дату в результирующем наборе. В этом случае бланк запроса может выглядеть так:
Для выполнения вычислений можно использовать сложные выражения, например, включающие несколько функций или полей. Для этого нужно создать вычисляемое поле с выражением для вычисления и выбрать из раскрывающегося списка ячейки Групповая операция параметр Выражение. Например, для каждой группы товара подсчитать разницу закупочных и продажных цен. В этом случае бланк запроса может выглядеть так:
: Задание 2.
Определите, какое количество договоров заключил каждый сотрудник.
Выполнение задания.
1. В окне базы данных выберите группу Запросы.
2. Выберите в окне базы вкладку Создание.
3. В группе Запросы выберите Конструктор запросов.
4. В окне Добавление таблицы откройте вкладку Таблицы и двойными щелчками мыши выберите таблицы Сотрудники, Договоры.
5. Закройте окно Добавление таблицы.
6. Перенесите в бланк запроса из списка полей таблицы Сотрудники имя поля Сотрудник, из списка полей таблицы Договоры имя поля Номер договора.
7. Выберите в группе Показать или скрыть команду Итоги.
8. Выберите в раскрывающемся списке ячейки Групповая операция поля Номер договора функцию Count. В остальных ячейках строки Групповая операция должно остаться значение Группировка.
9. Переключитесь в режим таблицы и просмотрите полученный динамический набор:
10. Сохраните запрос под именем Количество договоров по сотрудникам и закройте запрос.
: Задание 3.
1. Создайте таблицу Выплаты со следующей структурой:
2. Введите в таблицу Выплаты следующие данные:
|
| ||
| Код договора | Дата выплаты | |
| 1 | 12.12.2016 | 7 000,00 ₽ |
| 1 | 26.07.2018 | 8 000,00 ₽ |
| 2 | 20.12.2016 | 7 000,00 ₽ |
| 3 | 17.01.2017 | 2 000,00 ₽ |
| 3 | 04.04.2017 | 4 000,00 ₽ |
| 3 | 16.01.2018 | 4 000,00 ₽ |
| 4 | 01.02.2017 | 7 000,00 ₽ |
| 4 | 01.02.2018 | 2 000,00 ₽ |
| 5 | 20.02.2017 | 5 000,00 ₽ |
| 5 | 20.02.2018 | 5 000,00 ₽ |
| 6 | 03.04.2017 | 3 000,00 ₽ |
| 6 | 05.05.2018 | 5 000,00 ₽ |
| 7 | 12.06.2017 | 3 000,00 ₽ |
| 7 | 12.06.2018 | 3 000,00 ₽ |
| 8 | 16.05.2017 | 2 000,00 ₽ |
| 8 | 15.12.2017 | 2 000,00 ₽ |
| 9 | 01.06.2017 | 3 000,00 ₽ |
| 9 | 01.09.2017 | 3 000,00 ₽ |
| 9 | 01.02.2018 | 3 000,00 ₽ |
| 9 | 01.08.2018 | 3 000,00 ₽ |
| 10 | 01.06.2017 | 17 000,00 ₽ |
| 11 | 21.08.2017 | 5 000,00 ₽ |
| 11 | 21.08.2018 | 5 000,00 ₽ |
| 12 | 22.01.2018 | 5 000,00 ₽ |
| 13 | 18.02.2018 | 7 000,00 ₽ |
| 13 | 18.05.2018 | 5 000,00 ₽ |
| 14 | 17.01.2018 | 4 000,00 ₽ |
| 15 | 02.02.2018 | 3 000,00 ₽ |
3. Добавьте таблицу Выплаты в схему данных:
4. Самостоятельно постройте запрос, который отображает сумму выплат по каждому договору. Результат запроса должен быть следующим:
5. Сохраните запрос под именем Суммы выплат.
6. Закройте запрос.
Запросы на изменение
Запрос на изменение - это запрос, который за одну операцию вносит изменения в несколько записей. Существует четыре типа запросов на изменение: на удаление, на обновление и добавление записей, а также на создание таблицы.
Запрос на создание таблицы
Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Созданная таблица может содержать записи, удовлетворяющие заданным условиям или отдельные поля из указанных таблиц. Такая таблица может использоваться как архивная таблица. Например, можно создать таблицу, сохраняющую все старые заказы, прежде чем удалить эти записи из текущей таблицы.
Для того чтобы создать новую таблицу с помощью запроса на создание таблицы, нужно:
· Задать создание нового запроса в режиме конструктора.
· Добавить в запрос таблицы, содержащие данные, которые следует поместить в новую таблицу.
· Щелкнуть в группе Тип запроса кнопку Создание таблицы. На экране появится диалоговое окно Создание таблицы.
· 
В поле Имя таблицы ввести имя для создаваемой таблицы.
· Выбрать параметр В текущей базе данных, чтобы поместить таблицу в открытую в данный момент базу данных, или выбрать параметр В другой базе данных и ввести в строку Имя файла имя базы данных, в которую требуется поместить новую таблицу. В случае необходимости указать путь нахождения другой базы данных.
· Выбрать ОК.
· Переместить поля, которые должна содержать новая таблица, из списка полей в бланк запроса.
· Ввести условия отбора записей в строке Условие отбора.
· Для просмотра новой таблицы до ее создания на панели инструментов нажать кнопку Вид. Чтобы вернуться в режим конструктора запроса и внести необходимые изменения или запустить запрос, на панели инструментов нажать кнопку Вид.
· Для создания новой таблицы нажать кнопку Запуск ! на панели инструментов.
Чтобы остановить выполнение запроса, нажать клавиши CTRL+BREAK.
· Выбрать Да для подтверждения создания таблицы.
· Закрыть запрос.
На экране появится окно с предупреждением.
· Если этот запрос больше использоваться не будет, выбрать Нет.
Установленные в исходных таблицах свойства полей и ключевые поля не переходят к данным в новой таблице.
: Задание 4.
Создайте с помощью запроса архивную таблицу, которая должна содержать записи о договорах, завершенных в первом полугодии 2018 года. Каждая запись должна содержать Код договора, Дату заключения и Дату окончания.
Выполнение задания.
1. В окне базы данных выберите группу Запросы.
2. Выберите в окне базы вкладку Создание.
3. В группе Запросы выберите Конструктор запросов.
4. В окне Добавление таблицы откройте вкладку Таблицы и двойным щелчком мыши выберите таблицу Договоры.
5. Закройте окно Добавление таблицы.
6. Перенесите в бланк запроса из списка полей таблицы поля Код договора, Дата заключения и Дата окончания.
7. Выполните команду Создание таблицы. На экране появится диалоговое окно Создание таблицы.
8. В поле Имя таблицы введите имя для создаваемой таблицы - Архивная таблица.
9. Выберите параметр В текущей базе данных и щелкните по кнопке ОК.
10. Введите в строку Условие отбора столбца Дата окончания условия отбора записей:
11. Для просмотра новой таблицы до ее создания переключитесь в режим таблицы.
12. Вернитесь в режим конструктора.
13. Для создания новой таблицы нажмите кнопку Выполнить.
14. Выберите Да для подтверждения создания таблицы.
15. Закройте запрос без сохранения.
16. Перейдите в группу Таблицы и убедитесь, что там появилась Архивная таблица:
Запрос на добавление
Запрос на добавление отбирает группу записей из одной таблицы или нескольких связанных таблиц и добавляет их в конец другой таблицы. Например, перед удалением старых записей из основной таблицы их нужно сохранить в уже существующей архивной таблице. Такие записи можно добавить в архивную таблицу с помощью запроса на добавление, а не вводить их вручную.
Таблица, в которую добавляются записи, должна содержать соответствующие поля. Имена полей основной таблицы и таблицы, в которую добавляются записи, могут отличаться друг от друга. Обе таблицы могут содержать разное количество полей, то есть иметь поля не только с добавляемыми данными.
Для того чтобы добавить записи из одной таблицы в другую с помощью запроса на добавление записей, нужно:
· Задать создание нового запроса в режиме конструктора.
· Добавить таблицы, записи из которой необходимо добавить в другую таблицу.
· Щелкнуть в группе Тип запроса кнпку Добавление. На экране появится диалоговое окно Добавление.
· В поле Имя таблицы ввести имя таблицы, в которую необходимо добавить записи. Можно выбрать имя таблицы из раскрывающегося списка.
· Выбрать параметр В текущей базе данных, чтобы поместить записи в таблицу открытой в данный момент базы данных, или выбрать параметр В другой базе данных и ввести имя базы данных, в которую требуется поместить записи. В случае необходимости указать путь для другой базы данных.
· Выбрать ОК.
· Из списка полей в бланк запроса переместить поля, которые необходимо добавить, а также поля, которые будут использованы при определении условия отбора.
· Если все поля в обеих таблицах имеют одинаковые имена, то можно просто переместить с помощью мыши символ звездочка (*) в бланк запроса.
· Если в обеих таблицах выделенные поля имеют одинаковые имена, соответствующие имена автоматически вводятся в строку Добавление. Если имена полей двух таблиц отличны друг от друга, в строку Добавление надо ввести имена полей, добавляемых в таблицу.
· Ввести условие отбора записей для добавления в ячейку Условие отбора бланка запроса.
· Для предварительного просмотра добавляемых записей нажать кнопку Вид на панели инструментов. Для возврата в режим конструктора снова нажать кнопку Вид на панели инструментов. Любые изменения следует вносить в режиме конструктора.
· Для добавления записей нажать кнопку Запуск на панели инструментов.
Чтобы остановить выполнение запроса, нажать клавиши CTRL+BREAK.
· Выбрать Да для подтверждения добавления записей.
· Закрыть запрос.
· Выбрать Нет, чтобы не сохранять запрос.
: Задание 5.
Добавьте с помощью запроса в архивную таблицу записи о договорах, завершившихся в 2017 году.
Выполнение задания.
1. В окне базы данных выберите группу Запросы.
2. Выберите в окне базы вкладку Создание.
3. В группе Запросы выберите Конструктор запросов.
4. В окне Добавление таблицы откройте вкладку Таблицы и двойными щелчками мыши выберите таблицы Договоры.
5. Закройте окно Добавление таблицы.
6. Перенесите в бланк запроса из списка полей таблицы поля Код договора, Дата заключения и Дата окончания.
7. Выполните команду Тип запроса, Добавление. На экране появится диалоговое окно Добавление.
8. Выберите параметр В текущей базе данных.
9. Выберите в раскрывающимся списке поля Имя таблицы имя Архивная таблица и щелкните по кнопке ОК.
10. Введите в строку Условие отбора столбца Дата окончания условие отбора записей:
11. Для просмотра добавляемых записей переключитесь в режим таблицы.
12. Вернитесь в режим конструктора.
13. Для добавления записей в архивную таблицу нажмите кнопку Выполнить.
14. Выберите Да для подтверждения добавления записей.
15. Закройте запрос без сохранения.
16. Перейдите в группу Таблицы и откройте Архивную таблицу.
17. Убедитесь, что в ней теперь 3 записи.
Запрос на обновление записей
Запрос на обновление записей вносит указанные изменения в группу записей одной таблицы или нескольких связанных таблиц. Например, на все молочные продукты цены увеличиваются на 10 процентов. Для того, чтобы увеличить цены в таблице нужно создать запрос на обновление записей, где выражение укажет как изменить цены [Цена]*1,1. Выражения составляются согласно рассмотренным ранее правилам.
Примеры выражений в запросах на обновление записей
| Выражение | Результат |
| "Продавец" | Изменяет значение на «Продавец» |
| #10.08.96# | Изменяет значение даты на 10-авг-96 |
| Да | Изменяет в логическом поле значения на «Да» |
| "РN"&[код] | Присоединяет строку «PN» в начало данных поля «Код» |
| [Цена]*[Количество] | Вычисляет произведение полей «Цена» и «Количество» |
| [Фрахт]* 1.5 | Увеличивает значения поля «Фрахт» на 50% |
| Right([Индекс],3) | Обрезает левые символы, оставляя три правых символов данных поля «Индекс» |
Для того чтобы изменить данные группы записей с помощью запроса на обновление, нужно:
· Задать создание нового запроса в режиме конструктора.
· Добавить таблицы, данные которых необходимо изменить.
· Щелкнуть в группе Тип запроса кнопку Обновление.
· Переместить с помощью мыши из списка полей в бланк запроса поля, которые необходимо обновить или поля, для которых нужно определить условие отбора.
· В случае необходимости в ячейки Условие отбора ввести условия отбора записей для редактирования.
· Для полей, которые необходимо обновить, в ячейку Обновление ввести выражение или значение, которое должно быть использовано для изменения полей.
· Чтобы просмотреть обновляемые записи нажать кнопку Вид на панели инструментов. Выводимый список не будет содержать новых значений. Для возврата в режим конструктора запроса снова нажать кнопку Вид на панели инструментов.
· Для внесения изменений нажать кнопку Запуск на панели инструментов.
Чтобы остановить выполнение запроса, нажать клавиши CTRL+BREAK.
· Выбрать Да для подтверждения изменения записей.
· Закрыть запрос.
· Выбрать Нет, чтобы не сохранять запрос.
Запрос на удаление
Для удаления нескольких записей, удовлетворяющих определенным условиям, эффективнее использовать запрос на удаление, чем удалять все записи по очереди. Запрос на удаление удаляет группу записей из одной таблицы или из нескольких связанных таблиц. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее.
Запрос на удаление позволяет удалить записи из нескольких таблиц, которые связаны отношением «один-к-одному» или отношением «один-ко-многим», если для них поддерживается целостность данных с каскадным удалением связанных записей.
В большинстве случаев записи удаляются из одной таблицы. Если у таблицы, используемой в запросе на удаление, есть подчиненные таблицы, то связанные записи из подчиненных таблиц будут также удалены, даже если подчиненные таблицы не были указаны в запросе.
Для того чтобы удалить записи с помощью запроса на удаление, нужно:
· Задать создание нового запроса в режиме конструктора.
· Добавить таблицу, из которой необходимо удалить записи и для полей которой будут установлены условия отбора записей. Если в условиях отбора записей должны участвовать поля из всех таблиц связанных отношением «один-к-одному», то добавить эти таблицы в запрос.
· Щелкнуть в группе Тип запроса кнопку Удаление.
· В бланке запроса появится строка Удаление.
· Переместить с помощью мыши символ «звездочка» (*) из списков полей в бланк запроса
В ячейках Удаление в этих столбцах появляется значение Из.
· Переместить с помощью мыши поля, для которых необходимо определить условие отбора записей.
В ячейках Удаление для этих полей появляется значение Условие.
· Ввести условия отбора записей в ячейки Условие отбора для полей, перемещенных в бланк запроса.
· Для предварительного просмотра удаляемых записей нажать кнопку Вид на панели инструментов. Для возврата в режим конструктора снова нажать кнопку Вид на панели инструментов.
· Внести изменения, если это необходимо, в режиме конструктора.
· Для удаления записей из таблиц нажать кнопку Запуск на панели инструментов.
Чтобы остановить выполнение запроса, нажать клавиши CTRL+BREAK.
На экране появится окно с предупреждением.
· Выбрать Да для удаления записей.
После удаления записей из таблицы, указанной в запросе, автоматически удаляются соответствующие записи из связанных таблиц, даже если эти таблицы не были указаны в запросе.
· Закрыть запрос.
Бывает необходимо из таблиц связанных отношением «один ко многим» удалить записи, для которых в условиях удаления должны участвовать поля подчиненной таблицы со сторона «много». В этом случае для удаления с помощью запроса на удаление нужно:
· Задать создание нового запроса в режиме конструктора.
· Добавить таблицы, из которых необходимо удалить записи и для полей которых будут установлены условия отбора записей.
· Щелкнуть в группу Тип запроса кнопку Удаление.
В бланке запроса появится строка Удаление.
· Переместить с помощью мыши символ «звездочка» (*) из списка полей подчиненной таблицы со стороны «много» в бланк запроса.
В ячейке Удаление в этом столбце появляется значение Из.
· Переместить с помощью мыши поля из главной и подчиненной таблицы, для которых необходимо определить условия отбора записей.
В ячейках Удаление для этих полей появляется значение Условие.
· Ввести условия отбора записей в ячейки Условие отбора для полей, перемещенных в бланк запроса.
· Для предварительного просмотра удаляемых записей нажать кнопку Вид на панели инструментов. Для возврата в режим конструктора снова нажать кнопку Вид на панели инструментов.
· Внести изменения, если это необходимо, в режиме конструктора.
· Для удаления записей из подчиненной таблицы нажать кнопку Запуск на панели инструментов.
Чтобы остановить выполнение запроса, нажать клавиши CTRL+BREAK.
На экране появится окно с предупреждением.
· Выбрать Да для удаления записей.
Удаление записей из подчиненной таблицы, не влечет за собой удаление связанных записей из главной таблицы.
· Удалить из запроса список полей подчиненной таблицы. Для этого выделить список полей, который необходимо удалить из запроса, и нажать клавишу DELETE.
Из бланка запроса автоматически будут удалены все поля подчиненной таблицы.
· Для главной таблицы, оставшейся в запросе, и полей, для которых установлены условия отбора в бланке запроса, снова нажать кнопку Запуск.
На экране появится окно с предупреждением.
· Выбрать Да для удаления записей.
При этом будут удалены указанные записи из главной таблицы на стороне «один».
: Задание 6.
1. Покажите результаты работы преподавателю.
2. Завершите работу в СУБД Access.
Дата добавления: 2018-11-24; просмотров: 480; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!