Групповые операции в access как включить

Desktop / For_exams / БазыДанных(3-И) / Лекции / Лекция 7

С целью получения итоговых данных на группах записей из таблиц предусмотрена возможность выполнения групповых операций. Для включения групповых операций в запрос необходимо вызвать команду Итоги (символ ∑) на вкладке «Конструктор» или ту же команду из контекстного меню в бланке запроса. В результате в бланке запроса появляется новая строка  Групповая операция.

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

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

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

Вариант 1: вначале отфильтровываются записи, удовлетворяющие заданным условиям отбора, и затем над ними выполняются групповые операции. В этом случае для полей, по которым задаются условия отбора, в строке Групповая операция вводится значение Условие (путем его выбора из раскрывающегося списка), а условия отбора обычным образом помещаются в соответствующей строке.

Вариант 2: вначале выполняются групповые операции по отношению ко всем записям; фильтрация на основе заданных условий отбора производится над записями, полученными в результате выполнения групповых операций. В этом случае условия отбора указываются в соответствующей строке для тех полей, над которыми предварительно выполняются групповые операции; поэтому в строке Групповая операция указано Группировка или одна из статистических функций, перечисленных в таблице.

Статистические функции Access

Вычисляет среднее арифметическое набора числовых значений поля запроса по группе записей

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

Возвращает значение указанного поля из первой записи группы

Возвращает значение указанного поля из последней записи группы

Возвращает максимальное значение указанного поля из группы записей

Возвращает минимальное значение указанного поля из группы записей

Вычисляет сумму значений указанного поля по группе записей

Вычисляет среднеквадратичное отклонение по набору значений указанного поля в группе записей

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

2. Перекрестные запросы

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

Ограничения при использовании Мастера перекрестных запросов:

мастер может создать перекрестный запрос на основе только одной таблицы (или одного запроса);

невозможность задания условий отбора.

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

3. Модифицирующие запросы

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

запросы на создание таблиц;

запросы на добавление данных;

запросы на удаление данных;

запросы на обновление данных.

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

Запросы на создание таблиц

Запрос на создание таблицы позволяет создать таблицу на основе данных, содержащихся в результирующем множестве запроса. Эти запросы чаще всего используются для экспорта информации в другую БД или другое приложение.

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

Запросы на добавление данных

Запрос на добавление позволяет добавить в таблицу, имеющуюся в БД, записи, создаваемые запросом. При этом запрос может включать меньше полей, чем таблица, в которую добавляются записи. В этом случае поля таблицы, которых нет в запросе на добавление, в добавляемых записях остаются пустыми.

Запросы на удаление данных

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

Запросы на обновление данных

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

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

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

Аксесс группировка в запросах

Сегодня поговорим на тему «Групповые операции в запросах Access». Групповые операции в запросах Access позволяют выделить группы записей с одинаковыми значениями в указанных полях и вычислить итоговые данные для каждой из групп по другим полям, используя одну из статистических функций. Статистические функции применимы, прежде всего, к полям с типом данных Числовой, Денежный, Дата/время.
В Access предусматривается девять статистических функций:

• Sum — сумма значений некоторого поля для группы;
• Avg — среднее от всех значений поля в группе;
• Max, Min — максимальное, минимальное значение поля в группе;
• Count — число значений поля в группе без учета пустых значений;
• StDev — среднеквадратичное отклонение от среднего значения поля в группе;
• Var — дисперсия значений поля в группе;
• First и Last — значение поля из первой или последней записи в группе.

Результат запроса с использованием групповых операций содержит по одной записи для каждой группы. В запрос, прежде всего, включаются поля, по которым производится группировка, и поля, для которых выполняются статистические функции. Кроме этих полей в запрос могут включаться поля, по которым задаются условия отбора.
Рассмотрим конструирование однотабличного запроса с групповой операцией на примере таблицы ПОСТАВКА_ПЛАН.

Запрос с функцией Sum

Задача. Определите, какое суммарное количество каждого из товаров должно быть поставлено покупателям по договорам. Все данные о запланированном к по-ставке количестве товара указаны в таблице ПОСТАВКА_ПЛАН.

1. Создайте в режиме конструктора запрос на выборку из таблицы ПОСТАВКА_ПЛАН.
2. Из списка таблицы перетащите в бланк запроса поле КОД_ТОВ ― код товара. По этому полю будет производиться группировка записей таблицы.
3. Перетащите в бланк запроса поле КОЛ_ПОСТ, по которому будет подсчитываться суммарное количество каждого из товаров, заказанных во всех договорах.
4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). В бланке запроса появится новая строка Групповая операция (Total) со значением Группировка (Group By) в обоих полях запроса.
5. В столбце КОЛ_ПОСТ замените слово Группировка (Group By) на функцию Sum. Для этого вызовите список и выберите эту функцию. Бланк запроса примет вид, показанный на рис. 4.11.
6. Для отображения результата запроса (рис. 4.12) щелкните на кнопке Выполнить (Run) в группе Результаты (Results).
7. Замените подпись поля Sum-КОЛ_ПОСТ на Заказано товаров. Для этого перейдите в режим конструктора, в бланке запроса установите курсор мыши на поле КОЛ_ПОСТ и нажмите правую кнопку. В контекстном меню выберите Свойства (Properties). В окне Свойства поля (Field Properties) введите в строке Подпись (Caption) — Заказано товаров. Для открытия окна свойств может быть выполнена команда Страница свойств (Property Sheet) в группе Показать или скрыть (Show/Hide).

1. Сохраните запрос под именем Заказано товаров.
2. Чтобы подсчитать количество товаров, заказанных в каждом месяце, выполните группировку по двум полям: КОД_ТОВ и СРОК_ПОСТ, в котором хранится месяц поставки (рис. 4.13).
3. Чтобы подсчитать количество товаров, заказанных в заданном месяце, предыдущий запрос дополните вводом параметра запроса в условие отбора (рис. 4.14).

Запрос с функцией Count

Задача. Определите, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется в таблице НАКЛАДНАЯ.

1. Создайте запрос на выборку на основе таблицы НАКЛАДНАЯ.
2. Из списка полей таблицы НАКЛАДНАЯ перетащите в бланк запроса поле НОМ_ДОГ. По этому полю должна производиться группировка.
3. По сути, смысл задачи сводится к подсчету в таблице числа строк с одинаковым номером договора, поэтому неважно по какому полю будет вычисляться функция Count. Перетащите в бланк запроса любое поле, например опять НОМ_ДОГ.
4. Выполните команду Итоги (Totals) из группы Показать или скрыть (Show/Hide). Замените слово Группировка (Group By) в одном из столбцов с именем НОМ_ДОГ на функцию Count. Бланк запроса примет вид, показанный на рис. 4.15.

1. Сохраните запрос под именем Число отгрузок по договорам. Выполните запрос. Результат запроса показан на рис. 4.16.

Для закрепления смотрим видеоурок:

Лекция 6 Тема: Запросы. Вычисления и групповые операциию

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

6.4. Итоговые запросы.

6.5. Перекрестные запросы.

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

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

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

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например

Стоимость: Цена*[Количество товара]

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

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

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

Рис.6.1. Диалоговое окно Построитель выражений

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Рис.6.3. Диалоговое окно Построителя выражений
со списком встроенных функций

Все встроенные функции в Построителе выражений сгруппированы по функциональному назначению.

При выборе функции в правом окне построителя, в нижней области окна Построителя выражений отображается синтаксис этой функции. Для более детальной справки необходимо щелкнуть по клавише Справка.

Обзор всех встроенных функций выходит за рамки лекции, поэтому рассмотрим наиболее используемые.

Таблица 6.1

Функции категории Дата/время

Возвращает значение дня месяца от 1 до 31

Возвращает значение месяца от 1 до 12

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Возвращает значение года от 100 до 9999

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Возвращает целое число от 0 од 23, представляющее значение часа

Возвращает числовое значение в зависимости от значения аргумента интервал:

«q» – квартал (от 1 до 4);

«m» – месяц (от 1 до 12);

«yyyy» – год (от 100 до 9999);

«ww» – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Возвращает текущую системную дату

Функции категории Проверка

Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

Функции категории Управление

IIf(условие; выр1; выр2)

Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

Продолжение таблицы 6.1

Функции категории Текстовые

Возвращает n левых символов аргумента текст

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Возвращает количество символов (длину строки) в аргументе текст

Возвращает строковое значение аргумента текст без начальных пробелов

Возвращает строковое значение аргумента текст без заключительных пробелов

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Возвращает строковое значение аргумента число

Возвращает значение аргумента переменная в формате заданным аргументом формат

6.3. Итоговые запросы

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

— количество сделок с Партнерами за определенный промежуток времени;

— средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

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

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Функции категории Статистические

Возвращает сумму набора значений

Возвращает среднее арифметическое набора значений

Возвращает наименьшее значение из набора значений

Возвращает наибольшее значение из набора значений

Возвращает количество записей в наборе значений отличных от Null

Возвращает первое значение поля в группе

Возвращает последнее значение поля в группе

Возвращает среднеквадратичное отклонение набора значений

Возвращает дисперсию набора значений

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

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

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

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

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

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

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

Рис.6.4. Макет перекрестной таблицы

Рис.6.5. Сформированный перекрестный запрос в QBE

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

Аксесс группировка в запросах

Упражнение 4. Итоговый запрос

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

В окне базы данных щелкните на кнопке Запросы.

Дважды щелкните на значке Создание запроса в режиме конструктора (Create Query In Design View).

В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить.

6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора.

Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора.

В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

Рис. 17.7. Запрос с групповыми операциями

В раскрывающемся списке ячейки Групповая операция (Total) третьего столб ца бланка запроса выберите пункт Min.

В той же ячейке четвертого столбца выберите пункт Мах.

ТАБЛИЦА 17.1 . Групповые операции

Название	Функция
Условие(Where)	Режим задания условия отбора для поля, но которому не выполняется группировка. Access автоматически делает такое поле скрытым
Выражение (Expression)	Вычисляемое поле, значение которого рассчитывается по сложной формуле
Group By	Поле, определяющее группу записей, по которой вычисляются статистические параметры. К одной группе относятся все записи, для которых значения поля с режимом Group By (Группировка) одинаковы
Last	Последнее значение в группе
First	Первое значение в группе
Var	Вариация значений поля
StDev	Стандартное отклонение величин ноля от среднего
Count	Количество записей, соответствующее ноле которых не содержит величины Null
Мах	Максимальное значение
Min	Минимальное значение
Avg	Среднее значение поля
Sum	Сумма значений поля по всем записям

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

Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

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

Снова щелкните на кнопке Вид.

Для сохранения изменений структуры щелкните на кнопке Да.

Аксесс группировка в запросах

Упражнение 4. Итоговый запрос

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

В окне базы данных щелкните на кнопке Запросы.

Дважды щелкните на значке Создание запроса в режиме конструктора (Create Query In Design View).

В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить.

6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора.

Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора.

В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

Рис. 17.7. Запрос с групповыми операциями

В раскрывающемся списке ячейки Групповая операция (Total) третьего столб ца бланка запроса выберите пункт Min.

В той же ячейке четвертого столбца выберите пункт Мах.

ТАБЛИЦА 17.1 . Групповые операции

Название	Функция
Условие(Where)	Режим задания условия отбора для поля, но которому не выполняется группировка. Access автоматически делает такое поле скрытым
Выражение (Expression)	Вычисляемое поле, значение которого рассчитывается по сложной формуле
Group By	Поле, определяющее группу записей, по которой вычисляются статистические параметры. К одной группе относятся все записи, для которых значения поля с режимом Group By (Группировка) одинаковы
Last	Последнее значение в группе
First	Первое значение в группе
Var	Вариация значений поля
StDev	Стандартное отклонение величин ноля от среднего
Count	Количество записей, соответствующее ноле которых не содержит величины Null
Мах	Максимальное значение
Min	Минимальное значение
Avg	Среднее значение поля
Sum	Сумма значений поля по всем записям

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

Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

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

Групповые операции в access как включить

Запрос позволяет для данных, относящихся к одинаковым группам, подводить итоги: подсчитывать их количество, сумму, среднее, максимальное и минимальное значение.

Задание: создать запрос, который будет подсчитывать общее количество наименований и суммарный объем товаров, поступивших по каждой накладной.

Рассмотрим данные таблицы «Поступление товаров» (рис.13). В таблице одинаковые значения столбца Номер накладной могут повторяться многократно (в зависимости от того, сколько товаров поступило по накладной). Необходимо создать запрос, который будет для накладных с одинаковым номером суммировать количество товара.

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

Рис. 13. Таблица «Поступление товаров»

Порядок работы:

1. В Окне навигации выбирается Категория объекта — Тип объекта, а в разделе Фильтр по группам устанавливается переключатель Запросы. На ленте в разделе Создание в группе Запросы нажимается кнопка Конструктор Запросов.

2. В диалоговом окне Добавление таблиц, выделяются таблицы, данные из которых требуется отобразить в запросе («Поступление товаров») и нажимается кнопка Добавить.

3. На экране отобразится окно конструктора запроса, состоящее из двух частей: в верхней части отображены макеты таблиц, а в нижней — раздел для определения параметров запроса.

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

5. Для реализации запроса в режиме Конструктора добавляется дополнительная строка Групповые операции (рис.14) нажатием на панели инструментов кнопки .

Под полем № накладной в строке Групповые операции выбирается команда Группировка (накладные с одинаковым номером группируются), под полем Код товара в строке Групповые операции выбирается команда Count (количество), а под полем Количество — команда Sum (количество суммируется). Операции, используемые в запросе на группировку, описаны в табл.2.

Результат выполнения запроса представлен на рис. 15. (сравните результат с данными рис. 13).

Использование групповых операций в запросах

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

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

Девять статистических функций, встроенных в Access:

Last Последнее значение в группе

First Первое значение в группе

Var Вариация значений поля

StDev Стандартное отклонение величин ноля от среднего

Count Число значений поля в группе без учета пустых значений

Мах Максимальное значение

Min Минимальное значение

Avg Среднее значение поля

Sum Сумма значений поля по всем записям

На рисунке 16 представлен запрос с группировкой, с помощью которого можно определить значение среднего оклада, суммарную величину окладов по должностям согласно штатному расписанию, среднеквадратическое отклонение от среднего значения оклада.

Рис.16. Запрос с использованием групповых операций

7. Даты и их использование в базах данных

Операции с данными, осуществляемые пользователями, регистрируется во времени. Ввод, корректировка, удаление и другие операции регистрируются во времени. Даты используются в различных вычислениях.

Операторы работы с датами:

Date() — вывод текущей даты (число, месяц и год);

Year(Date()) — вывод текущего года;

Month(Date()) -вывод текущего месяца;

Day(Date()) — вывод текущего дня.

Стаж вычисляется путем определения количества лет между датой найма и текущей датой (Рис.17).

Рис. 17. Определение стажа

Начисление надбавки за стаж:

Рис.18. Определение стажа

8. Проектирование и разработка форм

Пользователь имеет возможность создать форму самостоятельно или воспользоваться мастером. Мастер форм ускоряет процесс создания формы, так как автоматически выполняет всю основную работу. При использовании мастера Microsoft Access выдает приглашение на ввод данных, на основе которых создается форма. В созданных формах мастер можно использовать для быстрого создания элементов управления в форме. Для настройки формы следует переключиться в режим конструктора.

Недостатком форм, создаваемых мастером, является то, что они однообразны и не содержат пояснительных надписей. Чтобы приукрасить форму и расположить поля более удобным образом, следует воспользоваться конструктором форм, который позволяет передвигать и масштабировать элементы формы, связывать их с источником данных и настраивать любые другие параметры формы.

Любой элемент формы имеет контекстное меню, в котором есть команда Свойства. Выбор такой команды открывает окно свойств, подобное окну свойств формы. Вкладка Макет содержит параметры форматирования объекта. Вкладка Данные — описание источника данных, различные ограничения и условия, накладываемые на данные поля. Вкладка События (Event) позволяет писать функции отклика на различные события (например, перемещение мыши на объект). Параметры, не вошедшие впервые три группы, размещаются на вкладке Другие (Other). Вкладка Все (АН) обеспечивает одновременный доступ ко всем параметрам.

Следовательно, создание формы может осуществляться двумя способами:

· Создание формы с помощью мастера;

· Создание формы в режиме конструктора.

Также формы могут быть созданы на основе

· Запросов для ввода данных в связные таблицы.

Рассмотрим создание однотабличных форм на основе таблицы Т-Кадры (Рис.19, Рис.20):

Рис.19. Однотабличная форма

Рис.20. Однотабличная форма с использованием панели элементов

С помощью кнопок панели элементов в форму можно добавлять различные объекты. Элементы управления форм и отчетов сходны между собой, поэтому такая же панель элементов имеется и в конструкторе отчетов, а все операции одинаково применимы как к формам, так и к отчетам.

Элементы управления форм:

· Надпись (Label) — Надписи создаются автоматически вместе с элементами типа текстовое поле, поле со списком и т. п. Они разъясняют смысл поля ввода. Дополнительные надписи могут использоваться для заголовков и пояснений.

· Поле (Text Box) — Используется для ввода и отображения информации полей таблиц и запросов, а также для вывода результатов вычислений.

· Группа переключателей (Option Group) — Группа переключателей ссылается на некоторое поле таблицы. Каждый переключатель группы соответствует определенному целочисленному значению этого поля.

· Выключатель (Toggle Button) — Позволяет вводить информацию типа да/нет. Величине «да» или «истина» соответствует утопленное положение выключателя.

· Переключатель (Option Button) — Элемент группы переключателей, в которой может быть отмечен только один из них (его кружок выглядит зачерненным).

· Флажок (Check Box) — Предназначен для представления полей типа да/нет. Отмеченный квадратик соответствует величине «да», а пустой — «нет».

· Поле со списком (Combo Box) — Разрешает как непосредственный ввод значения в поле, так и его выбор в раскрывающемся списке предопределенных значений.

· Список (List Box) — Позволяет выбирать данные из набора пунктов и не разрешает непосредственный ввод значений.

· Кнопка (Command Button) — Щелчок на такой кнопке выполняет команду, с помощью которой можно перейти в другую форму, переместиться по записям и выполнить многие другие операции.

· Рисунок — Рисунок любого графического формата с рамкой

· Линия (Line) — Добавляет прямую линию

· Прямоугольник (Rectangle) — Рисует прозрачный или непрозрачный прямоугольник с цветной границей любой толщины

· Другие элементы (More Controls) — Открывает меню с элементами управления, не вошедшими в панель элементов.

Рассмотрим форму созданную на основе многотабличного запроса. В ней отражаются фамилии сотрудников, чей оклад составляет более 4000 руб. Также с помощью данной формы возможно ввести данные в связанные таблицы Т-Кадры и Т-Должность (Рис.21).

Рис. 21. Форма на основе многотабличного запроса

Создадим форму, с помощью которой можно было бы редактировать данные в таблице Т-Кадры (Рис. 22).