Как сделать запрос с вычисляемым полем в access?

Вычисления и групповые операции СУБД MS Access

Подробности

Создано: 12 ноября 2013

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

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

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

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

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

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

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

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

• Вычисляемое поле представляет собой выражение, состоящее из операторов (арифметических, сравнения, логических, конкатенации) и операндов. В качестве операндов могут быть использованы константы, встроенные или определенные пользователем функции и идентификаторы, например
• Стоимость: Товары! Цена * Количество * (1-Скидка)
• КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

ФИО: Фамилия &» «& Left(Имя;1) &». «& Left(Отчество;1) &».»

1. Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.
2. Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например
3. Стоимость: Цена*[Количество товара]

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 6.1

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

Day(дата)	Возвращает значение дня месяца от 1 до 31
Month(дата)	Возвращает значение месяца от 1 до 12
MonthName(месяц[; флаг])	Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.
Year(дата)	Возвращает значение года от 100 до 9999
Weekday(дата[; число])	Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)
Hour(дата)	Возвращает целое число от 0 од 23, представляющее значение часа
DatePart(интервал; дата)	Возвращает числовое значение в зависимости от значения аргумента интервал: «q» – квартал (от 1 до 4); «m» – месяц (от 1 до 12); «yyyy» – год (от 100 до 9999); «ww» – неделя (от 1 до 53); и т. п. (см. справку по функции).
Date()	Возвращает текущую системную дату

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

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

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

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

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

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

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

Left(текст; n)	Возвращает n левых символов аргумента текст
Right(текст; n)	Возвращает n правых символов аргумента текст
Mid(текст; нач_поз[; n])	Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.
Len(текст)	Возвращает количество символов (длину строки) в аргументе текст
LTrim(текст)	Возвращает строковое значение аргумента текст без начальных пробелов
RTrim(текст)	Возвращает строковое значение аргумента текст без заключительных пробелов
Trim(текст)	Возвращает строковое значение аргумента текст без начальных и заключительных пробелов
Str(число)	Возвращает строковое значение аргумента число
Format(переменная; формат)	Возвращает значение аргумента переменная в формате заданным аргументом формат

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

1. При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:
2. —  количество сделок с Партнерами за определенный промежуток времени;
3. —  средний объем продаж по каждому месяцу за предыдущий год.
4. Ответы на такие вопросы дает итоговый запрос.
5. Для вычисления итоговых значений необходимо нажать кнопку Групповые операции на панели инструментов Конструктор запросов, чтобы в бланке QBE появилась строка Групповая операция (после имени таблицы).
6. По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

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

Таблица 6.2

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

Функция	Назначение
Sum	Возвращает сумму набора значений
Avg	Возвращает среднее арифметическое набора значений
Min	Возвращает наименьшее значение из набора значений
Max	Возвращает наибольшее значение из набора значений
Count	Возвращает количество записей в наборе значений отличных от Null
First	Возвращает первое значение поля в группе
Last	Возвращает последнее значение поля в группе
StDev	Возвращает среднеквадратичное отклонение набора значений
Var	Возвращает дисперсию набора значений

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

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

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

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

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

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

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

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

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

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

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

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

Источник: http://po-teme.com.ua/informatika/lektsii-po-informatike/1578-vychisleniya-i-gruppovye-operatsii-subd-ms-access.html

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

• Лабораторная работа №3.
• Создание и использование таблиц, форм, запросы и отчеты в Access.
• Добавление вычисляемого поля в запрос.

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

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

Итак, сформируйте в режиме конструктора таблицу Типы регионов, включив в нее всего два поля – Код (с типом данных Счетчик) и Тип региона (тип данныхТекстовый). Ключевые поля не задавайте. Перейдите в режим таблицы и введите следующие значения (в шестой строке впишите прочерк):

Создайте еще одну небольшую таблицу Федеральные округа РФ. В режиме конструктора задайте ее структуру: Код округа(тип данных – Счетчик) и Федеральный округ(тип данных – Текстовый). Ключевые поля использовать не будем. Перейдите в режим ввода данных и заполните таблицу:

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

При вводе типа данных Денежныйв поля Площадьи Сбор ниже, в окне «Свойства поля» щелкните в строке «Формат поля» и выберите из предложенного списка Фиксированный.

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

Сохраните таблицу под именем Производство овощей в федеральных округах и регионах РФ. Заполните ее данными:

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

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

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

Для этого нажмите на названии поля Код таблицы Типы регионов и, Удерживая мышь, перетащите указатель на название поля Код типа региона таблицы Производство овощей в федеральных округах и регионах РФ.

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

Затем выберите из указанных таблиц поля включения их в запрос:

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

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

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

Нажмите кнопку ОК. Выражение помещено в конструктор запроса. Перейдите в режим таблицы запроса, и вы увидите в нем вычисляемое поле Выражение 1:

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

Вернитесь в режим конструктора запроса. Измените слово Выражение 1 на Урожайность (будьте осторожны, чтобы не повредить формулу). Вызовите для этого поля окно свойств, нажав кнопку Свойства (или вызвав одноименную команду из контекстного меню). Выберите из списка форматов поля Фиксированный, число десятичных знаков – 2;

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

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

Создайте также более простой запрос Производство овощей в Южном ФО. Он будет построен на основе созданного запроса (в окне добавления таблицы следует перейти на вкладку Запросы). Включите в него следующие поля и условия отбора:

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

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

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

В следующем окне укажите, что по полю Тип региона необходимо произвести сортировку по убыванию. Нажмите на кнопку Итоги, укажите, что следует рассчитать средние показатели по полю Сбор (Avg поставить галочку). Нажмите ОК, затем – Далее.

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

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

Источник: https://megaobuchalka.ru/7/21924.html

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

2010-02-28

Владимир Ткаченко

Источник: Обучение в интернет

В БД Microsoft Access вычисляемые поля могут быть созданы в запросе, форме или отчете.

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

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

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

• идентификаторы (имя поля и элемента управления, которые заключаются в квадратные скобки);
• операторы (арифметические, логические, сравнения и другие операторы);
• функции (готовые формулы, которые состоят из имени и аргумента, помещенного в круглые скобки)
• константы (элементы, которые не изменяются в Выражении, например Null, Истина, Ложь);
• значения (например, значения даты и времени, численные положительные или отрицательные значения).

• Примеры Выражения:

• =[Оценка]/1,2, где «=» — опрератор, [Оценка] — имя поля, а 1,2 — значение;
• Date(), где Date — имя функции текущей даты, которая не имеет аргумента.

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

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

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

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

Рис. 1.

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

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

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

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

Рис. 3.

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

Рис. 4.

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

Рис. 5.

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

Источник: https://www.lessons-tva.info/articles/informat/8.html

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

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

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

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

18 показан пример такого поля в запросе, созданном на базе таблиц «Сотрудники» (Employees) и «Заказы» (Orders).

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

Перед выражением нужно написать имя поля: ФИО и отделить его двоеточием от выражения.

Рис. 4.18.
Вычисляемое поле в запросе

Совет

Если выражение длинное, его неудобно писать в строке Поле (Field). Нажмите комбинацию клавиш +. Появится диалоговое окно Область ввода (Zoom) (рис. 4.19), в котором вводить выражение удобнее.

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

последний раздел данной главы.)

Рис. 4.19.
Диалоговое окно
Область ввода

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

Источник: http://access.bos.ru/Glava%204/Index5.htm

Основные хитрости, применяемые в запросах

Все специалисты Access хранят в своих БД несколько (или несколько десятков) полезных запросов, упрощающих решение повседневных задач.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Определение вычисляемого поля
• Для создания вычисляемого поля следует задать два компонента: имя поля и выражение, определяющее вычисление, которые должна выполнить программа Access. Вычисляемые поля определяются с помощью следующего состоящего из двух частей шаблона:
• ИмяВычисляемогоПоля:   Выражение
• Например, поле цены с налогом, PriceWithTax, определяется следующим образом:
• PriceWithTax:    [Price]   *   1.10

По сути, это выражение сообщает программе Access о том, что нужно взять поле Price и умножить его на 1.10 (что эквивалентно повышению цены на 10%).

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

Но вовсе необязательно отображать отдельно это поле в окне результатов запроса.

Можно сослаться на поле Price, используя его полное имя, состоящее из имени таблицы с последующей точкой, за которой указано имя поля:

PriceWithTax:    [Products].[Price]   *   1.10

«Запросы и связанные таблицы» главы 6), и одно и то же поле есть в обеих таблицах. В этой ситуации следует применять полное имя для того, чтобы избежать неоднозначности.

(Если не сделать этого, Access выдаст сообщение об ошибке при попытке выполнить запрос.)

Примечание

Пользователи предыдущих версий программы Access иногда вместо точки используют восклицательный знак (например, [Products] ! [ Price]), что равнозначно.

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

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

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

«Применение функций » далее в этой главе) и форматирования (см.разд. «Форматирование чисел «далее в этой главе).

У вычисляемых полей есть одно ограничение — поскольку информация не сохраняется в вашей таблице, вы не можете их редактировать. Если нужно изменить цену, необходимо отредактировать базовое поле Price — попытка корректировать поле PriceWithTax привела бы программу Access в полное замешательство.

Примечание

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

Рис. 7.1. Этот запрос отображает два поля непосредственно из БД (ID и Name)

1. и вставляет вычисляемое поле PriceWithTax. Обычное поле Price, которое программа Access
2. применяет для вычисления поля PriceWithTax, вообще не отображается
3. 

Рис. 7.2. Результаты запроса отображают поле PriceWithTax с надбавкой 10%. Главное состоит в том, что вычисляемая информация теперь доступна постоянно, несмотря на то, что она не хранится в БД. Попробуйте проверить это с помощью карманного калькулятора

На профессиональном уровне. Синхронизация запросов

Можно опробовать интересный прием. Выполните запрос ProductsWithTax и оставьте его открытым, отображающим свои результаты. Теперь откройте таблицу Products, содержащую реальные данные, и измените цену любого продукта. Вернитесь снова в запрос Products WithTax. Изменилось значение в этом запросе?

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

Есть лишь несколько исключений из этого правила.

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

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

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

•    Если несколько пользователей на разных компьютерах редактируют БД (как описано в главе 18), вы не увидите немедленно изменения, внесенные другими пользователями.

Для получения самых свежих результатов можно обновить отдельные записи или весь запрос целиком. Для обновления одной записи выберите Главная > Записи > Обновить > Обновить запись (Home >Records >Refresh > Refresh Record).

Для повторного выполнения запроса и полного обновления выберите Главная > Записи > Обновить > Обновить все (Home > Records > Refresh > Refresh All).

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

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

¦  Всегда выбирайте уникальное имя. Выражение Price:   [Price]   *  1.10 создает циклическую ссылку, поскольку имя используемого вами поля такое же, как имя создаваемого поля. Программа Access не допускает подобных проделок.

¦  Формируйте выражения из полей, чисел и математических операций. Наиболее распространенные вычисляемые поля содержат одно или несколько существующих полей или числовые константы и соединяет их друг с другом с помощью хорошо знакомых знаков математических операций, таких как сложение (+), вычитание (-), умножение (*) или деление (/).

¦  Не удивляйтесь присутствию квадратных скобок. Выражение PriceWithTax:    [Price] *   1.10 эквивалентно выражению PriceWithTax:   Price   *   1.10 (единственное отличие — квадратные скобки вокруг имени поля Price). Технически скобки нужны только,

¦   

Малоизвестная или недооцененная возможность. Переименование поля в запросе

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

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

Далее приведен пример вычисляемого поля, которое переименовывает DateCustomerPlacedPurchaseOrder в Date: Date:   DateCustomerPlacedPurchaseOrder Новое имя (в данном примере Date) называют псевдонимом (alias).

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

Вы можете следить за любыми ответами на эту запись через RSS 2.0 ленту. Вы можете оставить ответ, или trackback с вашего собственного сайта.

Источник: http://crypto.pp.ua/2011/03/osnovnye-xitrosti-primenyaemye-v-zaprosax/

Включение полей в запрос

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

Научиться задавать условия отбора.

3. Освоить использование вычисляемых полей и итоговых значений в запросах.

4. Изучить действие запросов на изменение.

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

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

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

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

Выбор данных из одной таблицы

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

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

В нижней располагается бланк QBE (Query By Example – запрос по образцу), в котором выполняется вся работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе.

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

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

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

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

Рис. 1. Запрос на основе таблицы тСтуденты

Для ввода условия отбора записей используется строка Условие отбора и строки или. На рис.1. показан запрос на выборку ФИО тех студентов, которые имеют оценки от 4 до 5 баллов.

Включение полей в запрос

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

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

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

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

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

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

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

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

Ввод условий отбора

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

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

Очевидно, чтобы результат операции AND имел значение Истина, оба условия должны быть истинными; только в этом случае запись отбирается запросом.

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

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

Результат применения к двум условиям логических операций AND и OR

AND Истина Ложь Истина Истина (Отбирается) Ложь (Отвергается) Ложь Ложь (Отвергается) Ложь (Отвергается)

OR Истина Ложь Истина Истина (Отбирается) Истина (Отбирается) Ложь Истина (Отбирается) Ложь (Отвергается)

• Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий
• < > 4
• В этом случае можно не использовать кавычки.
• Оператор Between позволяет задать диапазон значений, например:
• between 10 and 20
• Оператор Inпозволяет задавать используемый для сравнения список значений. Например:
• in (“первый”,”второй”,”третий”)

Источник: https://poisk-ru.ru/s11359t3.html

Условия отбора записей — Базы данных

Литералы — конкретные значения, воспринимаемые Access так, как они записаны. В качестве литералов могут быть использованы числа, текстовые строки, даты. Текстовые строки заключаются в двойные кавычки, даты — в знаки (#). Например, 567, «Информатика», #1-Января-99#.

Константы — не изменяющиеся значения, которые определены в Access, например, True, False, Да, Нет, Null.

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

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

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

Например, ссылка на поле в таблице примет вид: [Имя таблицы]! [Имя поля], а ссылка на свойство DefaultValue элемента управления Дата рождения в форме СТУДЕНТ: Forms! [СТУДЕНТ]! [ Дата рождения].DefaultValue

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

Эти операторы определяют операцию над одним или несколькими операндами.

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

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

Допускается использование операторов шаблона — звездочка (*) и вопросительный знак (?).

• Оператор Between позволяет задать интервал для числового значения и даты. Например:
• Between 10 And 100
• задает интервал от 10 до 100; можно задать интервал дат:

Between #01.01.1997* And #31.12.1997*

1. Оператор in позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Например:
2. In («Математики»;»Информатики»; » Истории»)

Источник: https://itteach.ru/bazi-dannich/usloviya-otbora-zapisey