Всі фахівці Access зберігають в своїх БД кілька (або кілька десятків) корисних запитів, що спрощують вирішення повсякденних завдань. У попередньому розділі ви дізналися, як створювати запити, які обробляють величезні обсяги інформації і пропонують вам саме те, що ви хочете побачити. Але знавці програми Access знають, що набагато більше мощі ховається за межами вікна конструктора запитів.

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

обчислювані поля

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

Навіть знаючи, що не слід створювати поля, такі як 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

Така синтаксична запис потрібна, якщо в ваш запит включено кілька таблиць (наприклад, використання запиту з операцією об'єднання (query join), описаного в розд. "Запити та пов'язані таблиці" глави 6), і один і той же поле є в обох таблицях. У цій ситуації слід застосовувати повне ім'я для того, щоб уникнути неоднозначності. (Якщо не зробити цього, Access видасть повідомлення про помилку при спробі виконати запит.)

Примітка

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

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

Тепер ви готові до виконання запиту. Коли ви виконаєте його, обчислювані дані з'являться поруч з іншими стовпцями (рис. 7.2). Якщо ви не задоволені тим, що обчислювані дані дещо інакше відформатовані - більше знаків у дробовій частині і немає символу валюти - це можна виправити за допомогою округлення (див. Розд. "Застосування функцій" далі в цьому розділі) і форматування (см.разд. "Форматування чисел" далі в цьому розділі).

У обчислюваних полів є одне обмеження - оскільки інформація не зберігається у вашій таблиці, ви не можете їх редагувати. Якщо потрібно змінити ціну, необхідно відредагувати базове поле Price - спроба коригувати поле PriceWithTax привела б програму Access в повне замішання.

Примітка

Вираз в кожен конкретний момент часу обробляє окремий запис. Якщо ви хочете об'єднати інформацію з деяких записів для обчислення підсумків або середніх значень, необхідно використовувати властивості угруповання, описані в розд. "Підсумкові дані" далі в цій главі.

Мал. 7.1. Цей запит відображає два поля безпосередньо з БД (ID і Name)

і вставляє обчислюване поле PriceWithTax. Звичайне поле Price, яке програма Access

застосовує для обчислення поля PriceWithTax, взагалі не відображається

Мал. 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). Технічно дужки потрібні тільки,

якщо в імені нуля є прогалини або спеціальні символи. Але якщо в Конструкторі ви вводите в запит вираження без квадратних дужок, програма Access автоматично додає їх, просто щоб убезпечити себе.

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

Втомилися від довгих імен полів у вікні результатів ваших запитів? Використовуючи толькочто отримані знання, що стосуються виразів, можна безболісно перейменувати поле у ​​вікні результатів вашого запиту. Все що вам потрібно - це обчислюване поле. Хитрість полягає в створенні (за допомогою виразу) обчислюваного поля, що збігається з одним з існуючих полів і присвоєння йому нового імені. Технічно в цьому полі не виконуються ніякі обчислення, але воно все одно коректно діє. Нижче наведено приклад обчислюваного поля, яке перейменовує DateCustomerPlacedPurchaseOrder в Date: Date: DateCustomerPlacedPurchaseOrder Нове ім'я (в даному прикладі Date) називають псевдонімом (alias).

Використовуючи цей прийом, пам'ятайте про те, що вихідне иоле (в даному випадку DateCustomerPlacedPurchaseOrder) не треба включати до своєї черги. Потрібна вам інформація відображається в обчислюваному поле (Date).

Ви можете слідкувати за будь-якими відповідями на цей запис через RSS 2.0 стрічку. Ви можете залишити відповідь , або trackback з вашого власного сайту.