Как можно сделать вычисления в запросах

Работа с данными при помощи запросов

Основные типы запросов

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

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

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

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

Вычисления в запросе

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

Для выполнения вычислений в запросе необходимо в бланке запроса создать новое вычисляемое поле. Для этого следует в пустую ячейку строки Поле ввести вычисляемое выражение, в котором могут использоваться имена полей таблиц, константы и функции, связанные с помощью арифметических операторов. Для изменения порядка вычислений и группировки данных в выражениях используются круглые скобки, а имена полей таблицы должны быть заключены в квадратные скобки. Если в запросе используется несколько таблиц, во избежание ошибок следует указывать полное имя поля, помещая перед именем поля имя таблицы. В этом случае для разделения имен объектов используется !. Таким образом, запись полного имени поля таблицы выглядит следующим образом: [Имя таблицы]![Имя поля]. Имя вычисляемого поля выводится перед выражением и отделяется от него двоеточием. Например,

Выражение1: [Цена]*[Количество], где Цена и Количество — имена полей.

Имя вычисляемого поля — Выражение1 — становится заголовком столбца в таблице с результатами выполнения запроса. Это имя можно изменить.

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

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

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

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

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

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

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

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

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

Запрос на изменение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вычисления в запросе

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

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

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

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

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

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

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

2. В узкий столбец непросто записать длинную формулу, но если нажать комбинацию клавиш Shift+F2, то открывается вспомогательное диалоговое окно, которое называется Область ввода. В нем можно ввести длинную формулу, а потом щелчком на кнопке ОК перенести ее в бланк запроса по образцу.

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

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

Запросы с вычисляемыми полями

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

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

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

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

Аргументами функций могут быть сложные выражения, в состав которых входят поля таблицы. Для создания таких выражений Построитель имеет область объектов (таблицы, запросы, формы и т.д.) и область полей указанных объектов. При раскрытии объекта Функции появляется список групп функций и список функций выделенной группы.

MOD – остаток от деления целого выражения на целое. При необходимости происходит округление обоих выражений;

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

1)Вычислить стоимость партии товара.

· Поставить курсор в пустую ячейку строки Поле:;

· вызвать Построитель выражений – ;

· дважды щёлкнуть по объекту Таблицы;

— выбрать таблицу Товар, затем поле Цена, кнопку Вставить;

— выбрать таблицу Продажи, затем поле Количество, кнопку Вставить;

· в пустой ячейке строки Поле получилось выражение:

· заменить служебное слово Выражение1 на смысловое СтоимостьПартии;

· сохранить запрос c вычисленным полем, задать имя запроса:

2)Ввести 10% скидку на стоимость партии товара более 10 штук.

· Ввести в запросный бланк таблицу Товар и запрос Стоимость;

· создать вычисляемое поле с помощью Построителя выражений, используя функцию управления – развилку:

IIf (логическое выражение;

действие1 по значению истина;

действие2 по значению ложь).

СтоимПартии: IIf([продажи]![Количество]>10;

3)Создать новое поле «ФИО», составленное из полей «Фамилия», «Имя», «Отчество» одной таблицы.

[Фамилия] & “,” & [Имя] & “,” & [Отчество]

1. Вы­числяемое поле существует только в результирующей таблице запроса.

2. После выполнения запроса вычисляемое по­ле выводит на экран результат вычислений, а не само выражение.

3. Самый быстрый способ вставки поля в выражение в Построителе выражений – это двойной щелчок левой кнопкой мыши по имени поля.

4. Вы­числяемое поле можно создать путем простого ввода (с клавиатуры) выражения для вычисления в ячейку «Поле» пустого столб­ца бланка запроса.

5. Вычисляемые поля имеют стандартные имена: Выражение1,2… Для удобства работы с ними следует изменить стандартные имена на смысловые непосредственно в запросной форме (в ячейке строки вычисляемого поля).

6. Для просмотра и ввода длинных выражений ячейки строки Поле: следует открыть окно Область ввода нажатием клавиш Shift–F2.

Вычисления в запросах. Создание отчетов

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

Цена: [Истрачено (сумма)]/[Количество изделий]

название: [Поле 1] Знак [Поле 2]

Для записи длинной формулы, нажав сочетание клавиш Shift+F2, следует открыть диалоговое окно Область ввода. После ввода формулы щелчком по кнопке ОК ее можно перенести в бланк запроса по образцу. Результаты расчетов будут представлены в результирующей таблице при включении отображения вычисляемого поля.

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

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

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

Раздел заголовка — дается общий заголовок отчета. Раздел верхнего колонтитула используется для печати подзаголовков, номеров страниц (колонцифры), логотипов и др. Область данных предназначена для размещения элементов управления, в которые выдаются данные из таблиц базы для печати на принтере. Раздел нижнего колонтитула используется для печати таких же данных, как и в верхнем колонтитуле. При этом могут использоваться встроенные функции для вывода текущей даты и страниц (функция Now (), Page (), Pages ()) и др. Оператор & (оператор конкатенации) служит при этом для «склеивания» текста, заключенного в кавычки, со значениями, возвращаемыми функциями. Раздел примечаний предназначен для дополнительной информации.

Для получения практических навыков работы по вычислениям в запросах, включая создание вычисляемого поля и итоговые вычисления, и созданию отчетов на экране и в виде бумажного документа в базе данных под управлением СУБД Access используем БД «Студенты», созданную в разделе 6.1:

Рис. 6.16. Вычисления в запросах

Рис. 6.17. Автоотчет ленточный

Рис. 6.18. Отчет, составленный с помощью Мастера отчетов

Рис. 6.19. Создание отчета в режиме Конструктора стиль заголовка Обычный. Нажимаем кнопку Готово. Результат представлен на рис. 6.18.

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

Подсчет данных при помощи запроса

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

Выберите нужное действие

Способы подсчета данных

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

Сумма для суммирования столбцов чисел;

Среднее для вычисления среднего значения в столбце чисел;

Максимум для нахождения наибольшего значения в поле;

Минимум для нахождения наименьшего значения в поле;

Стандартное отклонение для оценки разброса значений относительно среднего значения;

Дисперсия для вычисления статистической дисперсии всех значений в столбце.

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

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

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

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

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

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

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

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

Подсчет данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма, Минимум, Максимум или Среднее. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.

Создание простого запроса на выборку

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть».

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

Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

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

Каждое поле отображается в столбце в бланке запроса.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

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

При необходимости вы можете сохранить запрос.

Добавление строки итогов

Откройте запрос в режиме таблицы. Если база данных имеет формат ACCDB, щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы.

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

Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.

На вкладке Главная в группе Записи нажмите кнопку Итоги.

Под последней строкой данных в таблице появится новая строка Итог.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

Скрытие строки итогов

На вкладке Главная в группе Записи нажмите кнопку Итоги.

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

Подсчет данных с помощью итогового запроса

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

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

Подсчет всех записей в запросе

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

Дважды щелкните таблицу, которую вы хотите использовать в запросе, и нажмите кнопку «Закрыть».

Таблица появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

При необходимости вы можете сохранить запрос.

Подсчет записей в группе или категории

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть».

Таблица (или таблицы) появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

При необходимости вы можете сохранить запрос.

Справочные сведения об агрегатных функциях

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

Поддерживаемые типы данных

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

«Число», «Действительное», «Денежный»

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

«Число», «Действительное», «Денежный», «Дата/время»

Подсчитывает число элементов в столбце.

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

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

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

«Число», «Действительное», «Денежный», «Дата/время»

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

«Число», «Действительное», «Денежный», «Дата/время»

Показывает, насколько значения отклоняются от среднего.

Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице.

«Число», «Действительное», «Денежный»

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

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

Примеры выражений

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

Формы и отчеты

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

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

Операции с текстом

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

Отображает значения, хранящиеся в полях таблицы FirstName и LastName. В данном примере оператор & используется для объединения значения в поле «Имя», пробела (заключенного в кавычки) и значения в поле «Фамилия».

Функция Left используется для отображения первого знака в значении поля или элемента управления «НазваниеТовара».

Функция Right используется для отображения последних 2 знаков в значении поля или элемента управления «КодТовара».

Функция Trim используется для отображения значения в элементе управления «Адрес» и удаления всех пробелов в начале или конце строки.

=IIf(IsNull([Region]), [City] & » » & [PostalCode], [City] & » » & [Region] & » » & [PostalCode])

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

=[City] & (» » + [Region]) & » » & [PostalCode]

Оператор + и распространение значений Null используются для отображения значений в элементах управления «Город» и «Индекс», если элемент управления или поле «Область» имеет значение Null; в противном случае отображаются значения в элементах управления или полях «Город», «Область» и «Индекс», разделенные пробелами.

Распространение значений Null означает, что если хотя бы один компонент выражения возвращает значение Null, то и все выражение также возвращает Null. Оператор + поддерживает распространение значений Null, а оператор & — нет.

Колонтитулы

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

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

MS Access. Работа с данными при помощи запросов

Понятие запроса. Основные типы запросов

Создание запросов в режиме конструктора

Вычисления в запросах

Параметрические запросы

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

Запрос на изменение

Запросы в СУБД Access

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

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

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

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

Создание запросов в режиме конструктора

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

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

Каждая строка бланка запроса выполняет определенную функцию:

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

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

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

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

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

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

Выражение, которое указывает, какие записи необходимо включить в динамическую таблицу при выполнении запроса вводится в строку Условие отбора для поля, по которому это условие необходимо проверить. Для задания условий отбора можно использовать операторы меньше ( ), больше или равно (>=), не равно (<>), равно (=), Like (выбор по маске), Between (между), In (в интервале), And (и), Or (или) и другие, а также имена обьектов, константы и функции.

Завершение ввода условия выполняется нажатием клавиши Enter или просто переходом к другой ячейке бланка запроса с помощью клавиш управления курсором или мыши. Мicrosoft Access проводит синтаксический анализ заданного выражения. Например, если было введено значение текстового поля то это выражение будет представлено в кавычках. Если выражение не содержит никакого оператора, Microsoft Access будет исходить из того, что подразумевается оператор = или Like.

Допускается использование нескольких условий отбора, которые можно задать как для разных полей, так и для одного поля. Для создания сложных условий выбора данных используются логические операторы And и Or. Если условия отбора связаны оператором And, запись выбирается только в случае выполнения всех условий. Если же условия отбора связаны оператором Or, запись выбирается при выполнении хотя бы одного из всех условий. При определении нескольких условий отбора, связанных оператором And, для различных полей необходимо просто задать условие в строке Условие отбора для каждого из полей, образующих критерий выбора данных. Если же при определении нескольких условий поместить их в различные строки — строку Условие отбора и строку или — Microsoft Acces будет использовать Or-связь. В результате условия, расположенные в одной строке, связываются оператором And, в разных строках — оператором Or.

Т.о., при формировании условия отбора в запросах могут использоваться:

Подстановочные символы:

Создание выражений

С помощью выражений можно выполнять разнообразные операции с данными. Например, вы можете определить, сколько дней прошло с момента отправки заказа, или объединить содержимое полей FirstName и LastName и поместить результат в поле FullName. Разделы этой статьи содержат пошаговые инструкции по созданию выражений.

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

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

Чтобы вычислить итог, поместите элемент управления «поле» в нижний колонтитул отчета и настройте для свойства ControlSource этого поля следующее выражение:

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

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

Если окно свойств не отображается, нажмите клавишу F4.

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

После ввода выражения окно свойств будет выглядеть так:

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

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

Предположим, вы хотите просмотреть все заказы с датой отгрузки в течение первых трех месяцев 2017 г. Чтобы задать условие, введите следующее выражение в ячейку Условия столбца «Дата и время» в запросе. В нашем примере используется столбец «Дата и время» с именем ShippedDate. Чтобы определить диапазон дат, введите следующее условие:

Столбец ДатаИсполнения будет выглядеть подобным образом:

Для каждой записи в таблице заказов, для которой значение в столбце ShippedDate попадает в указанный интервал дат, будет создана запись в выходных данных запроса. Обратите внимание: в выражении даты заключаются в знаки решетки ( # ). Значение между знаками решетки трактуется в Access как значение с типом данных «Дата и время». Это означает, что с такими значениями могут производиться соответствующие вычисления, например вычитание одной даты из другой.

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

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

Чтобы использовать построитель выражений, на вкладке Конструктор в группе Настройка запроса нажмите кнопку Построитель .

Если для редактирования выражения вам требуется больше места, установите курсор в ячейку Условие отбора, а затем нажмите сочетание клавиш SHIFT+F2, чтобы открыть окно Область ввода:

Совет Чтобы сделать текст более удобным для чтения, выберите Шрифт.

Создание вычисляемого поля в запросе

Предположим, вы создаете запрос и хотите показать результаты расчета, в котором используются другие поля запроса. Чтобы создать вычисляемое поле, нужно ввести выражение в пустую ячейку в строке Поле запроса. Допустим, в вашем запросе есть поля Количество и Цена. Их значения требуется перемножить, чтобы получить вычисляемое поле Сумма. Для этого в строке Поле запроса введите следующее выражение:

При выполнении запроса в Access выполняется расчет каждой строки, как показано ниже:

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

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

Чтобы создать выражение вручную, просто введите его.

Чтобы использовать построитель выражений, на вкладке Конструктор в группе Настройка запроса нажмите кнопку Построитель.

Создание вычисляемого поля в таблице

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

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

Откройте таблицу, дважды щелкнув ее в области навигации.

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

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

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

Выражения в вычисляемых полях не начинаются со знака равенства (=).

Приложение Access добавит вычисляемое поле и выделит его заголовок, чтобы можно было ввести имя поля.

Введите имя вычисляемого поля и нажмите клавишу ВВОД.

Задание значений по умолчанию для поля таблицы

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

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

Таблица откроется в режиме таблицы.

Выберите поле, которое требуется изменить.

На вкладке Поля в группе Свойства нажмите кнопку Значение по умолчанию. В Access откроется построитель выражений.

Введите выражение, начинающееся со знака равенства ( =).

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

Задание значений по умолчанию для элементов управления

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

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

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

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

Если окно свойств не отображается, нажмите клавишу F4.

На вкладке Все окна свойств перейдите к ячейке свойства Значение по умолчанию.

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

Добавление правила проверки в поле таблицы или запись

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

Предположим, что в таблице Inventory имеется поле Units On Hand и требуется задать условие, разрешающее пользователям вводить только неотрицательные значения. Иначе говоря, количество товара никогда не может быть отрицательным. Это можно сделать, указав для поля Units On Hand следующее правило проверки:

ПРОЦЕДУРА: ввод правила проверки для поля или записи

В области навигации дважды щелкните нужную таблицу. Таблица откроется в режиме таблицы.

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

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

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

Не начинайте выражение со знака равенства (=).

ПРОЦЕДУРА: ввод сообщения проверки правильности

Чтобы сделать базу данных более удобной в использовании, можно задать собственные сообщения проверки правильности. Эти сообщения будут использоваться вместо стандартных сообщений, выводимых приложением Access, когда данные не соответствуют условию. В сообщение можно включить сведения, помогающие пользователю ввести правильные данные, например «Значение в поле Units On Hand не может быть отрицательным».

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

Таблица откроется в режиме таблицы.

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

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

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

Добавление правила проверки в элемент управления

Как и у полей таблицы и записей, у элементов управления есть свойство ValidationRule (Правило проверки), для которого можно создать выражение. Предположим, что используется форма ввода интервала дат для отчета и требуется, чтобы начальная дата была не раньше, чем 01.01.2017. В этом случае можно задать свойства ValidationRule и ValidationText (Сообщение об ошибке) для поля, в которое вводится начальная дата:

Условие на значение

Сообщение об ошибке

Невозможно ввести дату ранее 01.01.2017.

При попытке ввести дату раньше 01.01.2017 будет выведено сообщение с текстом, указанным в свойстве ValidationText. Если поле свойства ValidationText пусто, будет выведено стандартное сообщение. После нажатия кнопки ОК фокус ввода вернется в текстовое поле.

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

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

Щелкните правой кнопкой мыши элемент управления, который требуется изменить, и выберите в контекстном меню пункт Свойства. Будет отображено окно свойств элемента управления.

На вкладке Все выберите ячейку свойства Validation Rule (Правило проверки).

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

Не начинайте выражение с оператора =.

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

Группировка и сортировка данных в отчетах

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

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

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

ПРОЦЕДУРА: добавление группировки и сортировки в отчет

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

На вкладке Конструктор в группе Группировка и итоги нажмите кнопку Группировка и сортировка. Ниже отчета появится область Группировка, сортировка и итоги.

Чтобы добавить к отчету уровень группировки, нажмите Добавить группировку.

Чтобы добавить к отчету порядок сортировки, нажмите Добавить сортировку.

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

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

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

ПРОЦЕДУРА: добавление выражения в существующую группировку или сортировку

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

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

Щелкните стрелку вниз рядом с пунктом Группировка (уровни группировки) или Сортировка (порядок сортировки). Появится список доступных полей

В нижней части этого списка нажмите выражение, чтобы открыть построитель выражений.

Введите выражение в поле выражения (верхнем поле) построителя выражений. Убедитесь, что выражение начинается с оператора равенства ( =).

Управление запуском макрокоманд

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

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

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

Перейдите к блоку Если, который требуется изменить, или добавьте блок Если из области «Каталог макрокоманд».

Щелкните верхнюю строку блока Если.

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

Вычисления в запросах

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

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

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

Результативность : [Забито] / [Игры]

Упражнение 1.Создайтепростой запрос на выборкуна основе таблицы Преподаватели,в котором должны отображаться Фамилии, Имена, Отчества преподавателей и их Должность.

1.Для создания простого запроса:

· в окне базы данных откройте вкладку Запросы;

· в открывшемся окне щелкните по кнопке ;

· из появившихся пунктов окна «Новый запрос» выберите Простой запроси щелкните по кнопке ;

· впоявившемся окне в строке Таблицы/запросы выберите таблицу Преподавате­ли (если других таблиц или запросов не было создано, она будет одна в откры­вающемся списке);

· в окне «Доступные поля» переведите выделение на параметр Фамилия;

· щелкните по кнопке . Слово Фамилия перейдет в окно «Выбранные поля»;

· аналогично в окно «Выбранные поля» переведите поля «Имя», «Отчест­во», «Должность» (порядок важен — в таком порядке данные и будут выво­диться);

· щелкните по кнопке ;

· в строке параметра Задайте имя запроса введите новое имя Должности препо­давателей;

2. Сохраните запрос.Для сохранения запроса:

· щелкните по кнопке или выполните команду Файл, Сохранить;

· закройте окно запроса.

Упражнение 2. Создайтезапрос на выборку с параметром,в котором должны отображаться фамилии, имена, отчества преподавателей и преподаваемые ими дисциплины, а в качестве пара­метра задайте фамилию преподавателя и выполните этот запрос для преподавателяГришина.

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

· создайте запрос на выборку для следующих полей таблицы Преподаватели:
«Фамилия», «Имя», «Отчество», «Преподаваемая дисциплина». Запрос создавайте аналогично тому, как это делалось в п.1;

· задайте имя запросу Преподаваемые дисциплины;

· перейдите в режиме конструктора, щелкнув по кнопке или выполнив ко­манду Вид, Конструктор;

· в строке параметра Условия отбора для поля «Фамилия» введите фразу
(скобки тоже вводить): [Введите фамилию преподавателя];

· выполните запрос, щелкнув по кнопке на панели инструментов или выполнив команду Запрос, Запуск;

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

· в появившемся окне введите фамилию Гришин и щелкните по кнопке «ОК». На экране появится таблица с данными о преподавателе Гришине — его имя, отче­ство и преподаваемая им дисциплина;

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

· Из списка создания запроса выберитеКонструктор

· Добавьте нужные таблицы (Студенты, Оценки, Дисциплины), выбирая их и щелкая по кнопке Добавить.

· Завершите выбор, щелкнув по кнопке Закрыть.

· В строке Условие отбора под полями Оценки поставьте 4 Or 5.

· Щелкните по кнопке ! на панели инструментов для представления запроса.

· Сохраните запрос с именем Успеваемость 1, щелкнув по кнопке сохранить.

Упражнение 4. Самостоятельно создайте запрос с вычисляемым полем

Создайте вычисляемое поле «Надбавка», получаемое путем увеличения поля «Зарплата» на 20 %.

Упражнение 5.Самостоятельно изучите создание перекрестного запроса. (см. Access-Справка)

Задания для самостоятельной работы:

1. Задайте к базе данных три запроса различного типа …

Контрольные вопросы:

2. Какие способы создания запросов?

3. Какие типы запросов вы знаете?

4. Как создаются вычисляемые поля и перекрестные запросы?

5. Какие операции используются при создании запроса с условием?

Вычисления в запросе БД Access

Выражения могут содержать следующие элементы:

· идентификаторы (имя поля и элемента управления, которые заключаются в квадратные скобки);

· операторы (арифметические, логические, сравнения и другие операторы);

· функции (готовые формулы, которые состоят из имени и аргумента, помещенного в круглые скобки)

· константы (элементы, которые не изменяются в Выражении, например Null, Истина, Ложь);

· значения (например, значения даты и времени, численные положительные или отрицательные значения).

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

Рассмотрим создание вычисляемого поля в бланке запроса на примере базы данных Training_students_VP. Постановка задачи: в запросе на выборку (Запрос 3) создать поле, в котором должны отображаться итоговые оценки (ниже на 20% от полученных по результатам рейтинга). Для решения этой задачи откроем бланк «Запроса 3» в режиме конструктора (Рис. 1).

Рис. 1.

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

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

Для поставленной задачи в Построитель можно ввести выражение =[Оценка]/1,2 с клавиатуры (Рис.2) и щелкнуть на кнопке ОК.

Рис. 2.

В результате в бланке запроса появится вычисляемое поле =[Оценка]/1,2

Рис. 3.

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

Рис. 4.

С учетом вышеизложенного следует изменить название вычисляемого поля и отформатировать его. Для этого необходимо вернуться в режим конструктора и слово Выражение1 заменить на «Итог_оценка». Затем щелкнуть правой кнопкой мыши на вычисляемое поле и в контекстном меню выбрать команду Свойства, откроется диалоговое окно Свойства поля (Рис. 5)

Рис. 5.

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

Рис. 6.

Дата добавления: 2015-02-16 ; просмотров: 17 | Нарушение авторских прав