Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

При написании шаблон заключается в двойные кавычки.

Like «А*» – в поле Фамилия все фамилии, начинающиеся на А.

Like «*/1/99» – в поле Дата – все записи за январь 1999г.

Like «[ABC]*» – в поле Имя – любое имя, начинающееся с указанных букв.

Access агрегатные функции в запросах

Функция– это ранее определенная последовательность действий (программа). В Access можно использовать функции двух видов: стандартные, входящие в состав Access или VBA (140 функций) и пользовательские, которые пользователь пишет сам на языке VBA. Все функции сгруппированы по категориям:

1. Функции даты и времени. Предназначены для управления значениями типа Дата и Время.

Date() – возвращает текущее системное время и дату;

Day() – возвращает целое число от 1 до 31 – день месяца;

Month() – месяц от 1 до 12 значения даты;

Weekday() – возвращает день недели (целое число, воскресенье соответствует 1);

Year() – возвращает год (целое число).

2. Функции преобразования типов данных. Позволяют назначить наиболее подходящий тип данных:

str() – возвращает число в виде строки;

Val() – возвращает число из строки;

Format() – возвращает строку в формате, определенном пользователем.

3. Математические и тригонометрические функции. Выполняют вычисления над числовыми значениями.

Abs() – возвращает абсолютное значение числа;

Sqr() –вычисляет квадратный корень числа;

Fix() – возвращает целую часть числа;

Int() – возвращает первое целое число, меньшее аргумента.

4. Текстовые (строковые) функции. Выполняют операции над текстовыми значениями.

Asc() – возвращает числовой код символа;

Chr() – возвращает символ по числовому коду;

Instr() – возвращает номер позиции знака в тексте;

Left() – возвращает указанное число знаков текста слева;

Right() – возвращает указанное число знаков справа;

Mid() – возвращает указанное число знаков, начиная с указанной позиции.

5. Финансовые функции. Эти функции аналогичны имеющимся финансовым функциям в Excel.

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

В Access предусмотрено 9 статистических функций:

Sum — сумма значений некоторого поля для группы;

Avg — среднее значение некоторого поля для группы;

Max, Min — максимальное или минимальное значение поля для группы;

Count — число значений поля в группе (пустые значения поля не учитываются);

StDev — среднеквадратическое отклонение от среднего;

Var — дисперсия значений поля в группе;

First, Last — значение поля из первой или последней записи.

Логические функции

IIF(условие, значение_если_истина, значение_если_ложь). Запросы могут производить обобщенное групповое значение полей точно также как и значение одного пол. Это делает с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Имеется список этих функций: поля.

Запросы QBE на выборку.

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

— простой запрос на выборку;

— запрос с параметром;

— запрос с итогами;

— запрос с вычисляемым полем.

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

Бланк простого запроса содержит шесть строк:

— вывод на экран (указывает, будет ли поле присутствовать в динамическом наборе данных);

— условие отбора (содержит первое условие, ограничивающее набор данных);

— или (содержит другие условия ограничения данных).

— Разработка простого запроса выполняется в несколько этапов:

— выбор полей (добавление полей в запрос);

— установление критериев отбора;

— задание порядка расположения записей (сортировка).

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

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

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

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

— выполнить команду ЗАПРОС/Перекрестный;

— в строке Перекрестная таблица указать, какое поле используется в качестве заголовков строк, какое – в качестве заголовков столбцов и какое — для выполнения вычислений в соответствии с выбранной групповой операцией;

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

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

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

Чтобы создать запрос с параметром, необходимо в строку Условия отбора для заданного поля ввести текст приглашения для ввода данного, заключив его в прямоугольные скобки. Можно задать параметры для нескольких полей или для одного поля определить несколько параметров для отбора, используя запись условия в несколько строк совместно с логической операцией «ИЛИ».

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

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

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

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

В запросах с итогами можно выполнять следующие операции:

1. Групповые операции над всеми записями. Для этого в полях указываются итоговые функции. Результат запроса представляет собой одну запись, заголовки столбцов соответствуют названию функции и имени поля.

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

3. Группировку записей, которые соответствуют условию отбора. Для этого необходимо указать условие отбора для поля, где выбрана функция Группировка.

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

Запросы QBE — действия.

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

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

Существует 4 типа запросов на изменение:

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

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

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

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

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

Для создания запроса необходимо выполнить следующие действия:

— создать запрос на выборку и отладить его (добавить таблицы, значения полей которых будут использоваться для добавления записей);

— отменить свойство Вывод на экран для полей запроса;

— выполнить команду ЗАПРОС/Добавление – для пре­обра­зо­вания в запрос на добавление. При этом в бланке запроса появляется строка Добавление. Далее необходимо включить в бланк запроса поля, данные которых будут добавляться в принимающую таблицу. Можно ввести также условия отбора записей для добавления.

Читать еще:  Офис 2020 ключ активация 2020

— указать имя таблицы, куда будут добавляться записи;

— выполнить команду ЗАПРОС/Запуск.

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

Технология создания других типов запросов — действий аналогична.

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

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

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

Лекция 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.

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

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

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

Назначение групповых операций

Групповые операции позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для этих групп одну из ста­тистических функций.

В Access предусматривается девять статистических функций:

  • sum — сумма значений некоторого поля для группы;
  • Avg — среднее от всех значений поля в группе;
  • мах, Min — максимальное, минимальное значение поля в группе;
  • count — число значений поля в группе без учета пустых значений;
  • StDev — среднеквадратичное отклонение от среднего значения поля в группе;
  • var — дисперсия значений поля в группе;
  • First и Last — значение поля из первой или последней записи в группе.
Читать еще:  Майкрософт офис 2020 активированная

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

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

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

Выполняется команда Вид|Групповые операции(View|Totals) или на панели инструментов конструктора запросов нажимается кнопка Групповые операции(Totals). Можно также нажать правую кнопку мыши и выбрать в контекстном меню Групповая операция(Total) (курсор мыши должен быть уста-1новлен в бланке запроса). В бланке запроса появляется строка Групповая Операция (Total),в которой для всех полей записано группировка (Group By).

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

Рассмотрим конструирование запроса с групповой операцией на примере таблицы ПОСТАВКА_ПЛАН.

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

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

Создадим запрос на выборку для таблицы ПОСТАВКА_ПЛАН. Из списка таблицы перетащим в бланк запроса поле код_тов — код товара. Это поле создадим для дальнейшей группировки по нему. Перетащим в бланк запроса поле кол_пост, по которому будет вычисляться функция sum для подсчета суммарного количества конкретного товара, заказанного во всех договорах.

Нажмем кнопку Групповые операции(Totals). Заменим слово группировка

(croup By) в столбце кол_пост на функцию Sum. Для этого вызовем список и выберем эту функцию. Бланк запроса примет вид, показанный на рис. 4.13.

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

Подпись поляSum КОЛ_ПОСТ можно заменить наЗаказано товаров. Дляввода этой подписи перейдем в режим конструктора, в бланке запроса установим курсор мыши на поле кол_пост и нажмем правую кнопку. В контекстном меню выберем Свойства(Properties). В окне Свойства поля(Field Properties) наберем в строке Подпись(Caption) — заказано товаров.

Таблица результата после доработки запроса показана на рис. 4А5.

Рис. 4.15. Таблица результата с измененной подписью поля

Сохраним запрос-выборку под именем «Заказано товаров».

Если необходимо подсчитать количество товаров, заказанных в каждом месяце, группировка должна быть произведена по двум полям код_тов и срок_пост, во втором поле хранится номер месяца поставки (рис. 4.16, рис. 4.17).

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

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

Определим, сколько раз отгружался товар по каждому из договоров. Факт отгрузки фиксируется документом «Накладная».

Создадим запрос на выборку на основе таблицы накладная. Из списка таблицы накладная перетащим в бланк запроса поле ном_дог — номер договора. По этому полю должна производиться группировка. По сути, смысл задачи сводится к подсчету в таблице числа строк с одинаковым номером договора, поэтому неважно, по какому полю будет вычисляться функция count. Перетащим в бланк запроса любое поле, например, опять ном_дог.

Нажмем кнопку Групповые операции(Totals). Заменим слово группировка (Group By) в одном из столбцов с именем ном_дог на функцию count. Бланк запроса примет вид, показанный на рис. 4.20.

Сохраним запрос под именем «Число отгрузок по договорам». Результат выполнения запроса показан на рис. 4.21.

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

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

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

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

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

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

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

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

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

    7. Щелкните на кнопке Групповые операции (Totals) панели инструментов. В бланке запроса появится дополнительная строка Групповая операция: (Total), позволяющая выполнять статистические операции со значениями конкретных полей.

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

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

    В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      В окне диалога Сохранение (Save As) введите имя Итоговый запрос и щелкните на кнопке ОК.

      Запросы на выборку

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

      Простые запросы на выборку. Действие таких запросов ограничивается отбором данных без их обработки. Пример такого запроса был приведен ранее (см. рис. 8.12—8.14). Запрос на выборку может быть создан на основе данных нескольких таблиц и запросов. Предположим, что необходимо получить данные о наименовании и стоимости комплектующих, закупленных по заказу № 2. Этот запрос в режиме конструктора представлен на рис. 8.15. Можно убедиться, что этот запрос содержит поля:

      • Номер заказа отношения Продажи;

      • Наименование отношения Комплектующие;

      • Цена отношения Цены.

      Рис. 8.15. Многотабличный запрос в режиме конструктора

      Читать еще:  Коды для офиса 2020

      По этому запросу пользователю будут представлены соответствующие данные (рис. 8.16).

      Рис. 8.16. Результат выполнения многотабличного запроса

      Как уже отмечалось, при создании запросов в строке «Условие отбора» можно записать не только сами значения признаков, но и выражения, содержащие эти значения. При этом СУБД MS Access предоставляет в распоряжение пользователя инструмент, облегчающий процесс создания выражений — Построитель. Этот инструмент включается либо кнопкой Построить на панели инструментов (обозначена «волшебной палочкой»), либо командой Построить, которую можно выбрать из контекстного меню, вызываемого правой клавишей мыши, когда курсор находится в строке «Условие отбора». Рассмотрим пример запроса, который позволит выбрать из нашей БД записи, содержащие сведения о комплектующих, стоимость которых находится в пределах 110—170 евро. В этом случае макет запроса принимает вид, изображенный на рис. 8.17.

      Рис. 8.17. Макет запроса

      Условие отбора в этом запросе сформулировано в форме выражения Between ПО And 170. Для его записи был использован построитель выражений, представляющий собой диалоговое окно. Это окно, внешний вид которого представлен на рис. 8.18, состоит из трех разделов, располагающихся сверху вниз [1]:

      [1] поле выражения. В верхней части окна построителя расположено поле, в котором создается выражение. Ниже находится раздел, предназначенный для создания элементов выражения и их последующей вставки в поле выражения.

      Рис. 8.18. Построитель выражений

      Допускается непосредственный ввод части выражения в поле выражения;

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

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

      В результате исполнения запроса будут получены необходимые сведения (рис. 8.19).

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

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

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

      Таблица 8.7. Статистические функции MS Access

      Окончание табл. 8.7

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

      Рис. 8.20. Макет группового запроса

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

      • нажать кнопку Групповые операции на панели инструментов (обозначена символом Σ) или

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

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

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

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

      Рис. 8.22. Макет перекрестного запроса

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

      1) для поля Наименование отношения Комплектующие, значения которого должно быть представлено в виде заголовков строк, выбрать значение Заголовки строк в строке Перекрестная таблица и в строке Групповая операция оставить значение Группировка;

      2) затем для поля Номер заказа отношения Продажи, значения которого должны быть представлены в виде заголовков столбцов, следует выбрать значение Заголовки столбцов строки Перекрестная таблица. Следует иметь в виду, что значение Заголовки столбцов можно задать только для одного поля. Для этого поля нужно оставить в ячейке строки Групповая операция значение Группировка;

      3) на завершающем этапе создания перекрестного запроса для поля Цена отношения Цены, значения которого используются при создании перекрестной таблицы, надлежит выбрать значение Значение строки Перекрестная таблица. Для перекрестных запросов обязательным является требование, чтобы значение Значение было установлено только в одном поле. Для этого поля в строке Групповая операция выбирается статистическая функция, использующаяся для заполнения перекрестной таблицы (например, Sum, Avg или Count).

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

      Рис. 8.23. Результат выполнения перекрестного запроса

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

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

      2. Пользовательские вычисления для выполнения расчетов с числовыми и строковыми значениями или значениями дат для каждой записи с использованием данных из одного или нескольких полей. Для ввода таких выражений необходимо создать новое вычисляемое поле непосредственно в бланке запроса.

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

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

      Пользовательские вычисления позволяют выполнять расчеты с числовыми и строковыми значениями или значениями дат для каждой записи с использованием данных из одного или нескольких полей. Например, выражение позволяет умножить значения одного поля на итоговое значение, найти разность значений двух полей типа «Дата, время», соединить несколько строковых значений в текстовом поле или создать подчиненные запросы. Примеры выражений приведены в приложении 2.

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

      Стоимость: [цены]! [Цена] * [Количество_комплектующих]!

      Особенность синтаксиса выражений MS Access заключается в том, что в качестве оператора присвоения здесь используется не знак равенства, а двоеточие. Макет запроса представлен на рис. 8.24.

      Для записи выражения использован построитель (рис. 8.25).

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

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

      Рис. 8.24. Макет запроса с вычисляемым полем

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

      Рис. 8.26. Результат выполнения запроса с вычисляемым полем

      Ссылка на основную публикацию
      Adblock
      detector