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

Устали от длинных имен полей в окне результатов ваших запросов? Используя толькочто полученные знания, касающиеся выражений, можно безболезненно переименовать поле в окне результатов вашего запроса. Все что вам требуется — это вычисляемое поле. Хитрость заключается в создании (с помощью выражения) вычисляемого поля, совпа­дающего с одним из существующих полей и присвоении ему нового имени. Технически в этом поле не выполняются никакие вычисления, но оно все равно корректно действу­ет. Далее приведен пример вычисляемого поля, которое переименовывает DateCustomerPlacedPurchaseOrder в Date: Date: DateCustomerPlacedPurchaseOrder Новое имя (в данном примере Date) называют псевдонимом (alias).

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

Простая математическая обработка числовых полей

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

Таблица 7.1. Арифметические операции

Операция	Название	Пример	Результат
+	Сложение	1+1	2
-	Вычитание	1-1	0
	Умножение	2*2	4
	Возведение в степень	2^3	8
/	Деление	5/2	2.5
\	Деление нацело (возвращает наименьшее целое число и отбрасывает остаток)	5\2	2
Mod	Остаток от деления (возвращает остаток, полученный в результате деления нацело)	5 Mod 2	1

Для создания выражения можно использовать произвольное количество полей и опера­ций. Рассмотрим таблицу Products с полем QuantityInStock (количество на складе), в ко­тором записано количество единиц товара, находящихся в вашем хранилище. Для определения

стоимости данного товара, имеющегося у вас под рукой, введите следующее выраже­ние, применяющее два поля:

ValueInStock: [UnitsInStock] * [Price]

Подсказка

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

Поля с датами

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

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

ExtendedDeadline: [DueDate] + 14

Если это выражение применить к дате 10 января 2007 г., новая дата будет.24 января 2007 г.

Используя операцию вычитания, можно найти число дней между двумя датами. Далее показано, как вычисляется период между временем помещения заказа и временем доставки: ShippingLag: [ShipDate] - [OrderDate]

Если доставка произошла через 12 дней после того, как сделан заказ, вы увидите значе­ние 12.

Примечание

Поля типа Дата/время включают информацию о времени суток. В вычислениях данные о вре­мени представляются как дробная часть числа. Если вы вычли одну дату из другой и получили число 12.25, оно представляет период 12 дней и 6 часов (поскольку 6 часов равно 25% суток).

Помните, что если вы хотите включить фиксированные даты в ваши запросы, их необхо­димо обрамлять символами # и использовать формат Месяц/День/Год (Month/Day/Year). Далее приведен пример, использующий такой подход для вычисления количества дней ме­жду датой своевременного предоставления задания (20 марта 2007 г.) и датой действитель­ного его предоставления: LateDays: [DateSubmitted] - #03/20/07#

Положительное значение указывает на то, что значение в поле DateSubmitted больше (более поздняя дата), чем предельный срок сдачи — другими словами, студент опоздал. Зна­чение 4 указывает, что студент на 4 дня задержался, в то время как -4 означает, что студент сдал работу на четыре дня раньше назначенного в расписании срока.

Порядок выполнения операций

Если о вашем выражении длинная строка вычислений, программа Access следует строгим правилам определения старшинства операций или, говоря математическим языком, учитывает,

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

1. Скобки (любые вычисления, заключенные в скобки, Access всегда выполняет первыми).

2. Проценты.

3. Возведение в степень.

4. Деление и умножение.

5. Сложение и вычитание.

Предположим, что вы хотите использовать поля QuantityInStock (количество на складе) и QuantityOnOrder (количество в заказах) для подсчета всех товаров, имеющихся в нали­чии и находящихся на пути к клиенту. Если вы не знаете правил старшинства операций, то можете сформировать следующее выражение:

TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]

Проблема состоит в том, что программа Access перемножит поля QuantityOnOrder и Price и добавит полученное значение к значению ноля QuantityInStock. Для устранения этой оплошности нужно применить скобки следующим образом:

TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]

Теперь поля QuantitylnStock и QuantityOnOrdeг суммируются, а затем умножаются на поле Price для получения общей суммы.

Подсказка

Вам нужно больше свободного места для набора длинного выражения? Можно расширить лю­бой столбец в Конструкторе запросов для увеличения видимой зоны, но в случае сложных вычислений этого все равно не хватит. Лучше щелкнуть кнопкой мыши в области Поле и на­жать сочетание клавиш <Shift>+<F2>. Это действие распахивает на экране диалоговое окно Область ввода (Zoom), отображающее все содержимое в большом текстовом поле, разделен­ном на столько строк, сколько вам нужно. Когда просмотр или редактирование выражения за­вершены, щелкните мышью кнопку ОК, чтобы закрыть окно и сохранить изменения, или кнопку Отмена для отказа от них.

Выражения с текстовыми значениями

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

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

Для слияния текста применяется оператор амперсанд (&). Далее показано, как создать поле FullName (ФИО), в котором собрана информация из полей FirstName и LastName:

FullName: [FirstName] & [LastName]

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

друг к другу, например, так: BenJenks. Лучше слить три фрагмента текста: имя, пробел и фамилию. Вот исправленная версия: FullName: [FirstName] & " " & [LastName]

Приведенное выражение создаст значение: Ben Jenks. Можно также поменять местами имя и фамилию и отделить их запятой, если вы предпочитаете указывать фамилию первой (например, Jenks, Ben) для облегчения сортировки: FullName: [LastName] & ", " & [FirstName]

Примечание

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

Вы даже можете применять амперсанд для сцепления текста с числовыми значениями. Если вы хотите, чтобы слегка бесполезный текст "The price is" появлялся перед каждым зна­чением цены, примените следующее вычисляемое поле:

Price: "The price is: " & [Price]

Функции запросов

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

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

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

• вычисляемых полях для включения данных в результаты ваших запросов;

• условиях отбора для задания записей, отображаемых в запросе;

• коде на языке Visual Basic, многоцелевой расширяемой системе для программы Access, с которой вы познакомитесь в части V.

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

Примечание

Функции — это встроенная часть версии языка SQL (см. разд. "Режим SQL" главы 6), приме­няемой в программе Access для обработки данных.

Применение функций

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

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

SalePrice: [Price] * 0.95

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

SalePrice: Round([Price] * 0.95, 2)

Технически функции Round ( ) требуется две порции информации или два аргумента. Первый — это число, которое округляется (в данном случае это результат вычисления Price * 0. 95), а второй — количество цифр, которое вы хотите оставить справа от деся­тичной точки (2). В результате получается значение, округленное до двух десятичных зна­ков, или 41.77.

Примечание

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