У цьому візуально практичному керівництві показано, як додати підтримку денормалізації таблиць "Замовлення" і "Відомості про замовлення" в зразку бази даних "Борей", додавши стовпець в таблицю "Замовлення", в якому зберігається загальна сума на замовлення. Хоча денормализация даних зазвичай не є оптимальним рішенням, вона може підвищити продуктивність при створенні звітів.
Багато процедури обробки даних пов'язані з однаковими обов'язками, а варіюються певні деталі, такі як конкретний первинний ключ для рядка, яку потрібно змінити. Ви можете повторювати одні й ті ж дії в кількох макросах даних. Проте, якщо ви знайдете загальні дії, ви можете отримати їх і помістити в іменований макрос даних, який можна викликати з інших макросів. Для обробки подій даних можна створити іменований макрос даних і потім викликати його з події або ж можна створити макрос і зв'язати його з подією даних в таблиці. Якщо спланувати все заздалегідь, часто вам вдасться спростити роботу, створюючи іменовані макроси.
З огляду на, що мета прикладу - отримати наростаючий підсумок загальної суми даного замовлення, вам необхідно додати макрос в події даних "Після оновлення", "Після вставки" і "Після видалення" в таблиці "Відомості про замовлення". Макрос оновлює значення нового поля в таблиці "Замовлення", грошового поля "OrderTotal".
Крім того, зразок бази даних "Борей" містить дані в таблиці "Відомості про замовлення", і вам потрібно якийсь спосіб, щоб об'єднувати ці дані і форматувати поле "OrderTotal" в таблиці "Замовлення". Це означає, що необхідно створити макрос даних, який можна викликати для ініціалізації поля "OrderTotal". У цьому візуальному практичному керівництві ви починаєте з створення зразка бази даних, а потім додаєте автономний іменований макрос. Після цього ви створюєте іменований макрос, який можна викликати з подій "Після оновлення", "Після вставки" і "після видалення" для таблиці "Відомості про замовлення".
Створення зразка бази даних
Щоб створити зразок бази даних, використовуйте наступну процедуру.
Створення зразка бази даних
У списку доступних шаблонів в розділі Шаблони Office.com виберіть Приклади.
Виберіть шлях і натисніть кнопку Завантажити. щоб завантажити і встановити чисту копію зразка бази даних.
Натисніть кнопку Увімкнути вміст на панелі повідомлень. якщо з'явиться відповідний запит, щоб включити код VBA в зразку бази даних.
Прийміть ідентифікатор входу за замовчуванням на формі Діалогове вікно входу. а потім натисніть кнопку Ввійти.
Зміна таблиці "Замовлення"
Для тестування макросів даних необхідно додати нове поле в таблицю "Замовлення". Щоб змінити цю таблицю, використовуйте наступну процедуру.
Зміна таблиці "Замовлення"
Розгорніть список, що розкривається у верхній частині області навігації та виберіть Тип об'єкта зі списку доступних параметрів.
Розгорніть вкладку Таблиці. клацніть правою кнопкою миші таблицю Замовлення і виберіть Конструктор.
У нижній частині списку полів в стовпці "Ім'я поля" введіть "OrderTotal". В поле "Тип даних" введіть "Грошовий".
Збережіть роботу та закрийте конструктор.
Оновлення поля "OrderTotal"
Для поля OrderTotal в таблиці "Замовлення" необхідно додати макрос даних, який може ініціювати значення поля на основі поточного набору рядків в таблиці "Відомості про замовлення". Макрос повинен перебрати всі рядки в таблиці "Замовлення" і для кожної з них знайти відповідний набір рядків в таблиці "Відомості про замовлення". На їх основі макрос повинен обчислити суму полів "Кількість * [ціна одиниці]" і зберегти її в поточному рядку в таблиці "Замовлення".
В області навігації двічі клацніть таблицю Замовлення. щоб відкрити її. Відкрийте вкладку Таблиця на стрічці. Потім в групі Іменовані макроси виберіть елемент Іменований макрос. Нарешті, виберіть команду Створити іменований макрос (див. Рис. 1).
Мал. 1. Створення іменованого макросу
У конструкторі макросів виберіть в поле зі списком дію ДляКаждойЗапісі. Воно дозволяє перебрати всі рядки джерела даних. В поле зі списком поле поруч з дією ДляКаждойЗапісі виберіть Замовлення. На рис. 2 показаний конструктор макросів на цьому етапі.
Мал. 2. Перебір рядків
В поле зі списком в області ДляКаждойЗапісі виберіть ЗадатьЛокПеременную і створіть змінну з ім'ям varTotalPurchased і значенням 0, як показано на рис. 3.
Мал. 3. Створення локальної змінної, в якій будуть зберігатися результати
В поле зі списком порожній в дії макросу ForEachRecord виберіть ДляКаждойЗапісі (щоб можна було пройти по всіх рядках замовлення в таблиці "Відомості про замовлення"). Введіть значення, як показано на рис. 4, використовуючи IntelliSense під час введення. Зверніть увагу на поле "Ім'я користувача" - це значення створює іменований набір рядків, щоб ви могли посилатися на кілька рядків. Ім'я набору довільне.
Мал. 4. Вибір набору співпадаючих рядків в таблиці "Відомості про замовлення"
В поле зі списком в дії ДляКаждойЗапісі виберіть ЗадатьЛокПеременную і введіть вираження, показані на рис. 5. Ця дія накопичує суму для кожного елемента в таблиці "Відомості про замовлення".
Мал. 5. Накопичення загальної суми для замовлення
Для завершення макросу і поновлення значення в таблиці "Замовлення", клацніть крайню макрокоманду ForEachRecord. В поле зі списком в нижній частині команди виберіть ІзменітьЗапісь. Потім в поле зі списком виберіть ЗадатьПоле. У полі Ім'я введіть "Закази.OrderTotal", а в полі Значення виберіть varTotalPurchased, як показано на рис. 6.
Мал. 6. Установка значення поля OrderTotal за допомогою локальної змінної
Ви створили іменований макрос даних, що проходить через всі рядки в таблиці "Замовлення". Для кожного рядка в таблиці макрос знаходить все відповідні рядки в таблиці "Відомості про замовлення", накопичує значення полів "Кількість" та "Ціна за одиницю", зберігає суму в змінної varTotalPurchased і, нарешті, копіює її значення в поле OrderTotal в таблиці "Замовлення ".
Створення макросу для виконання макросу даних
На стрічці натисніть кнопку Зберегти. введіть ім'я макросу UpdateTotals. а потім натисніть кнопку Закрити.
Для запуску макросу і ініціалізації всіх полів OrderTotal натисніть на стрічці кнопку Створити. а в групі Макроси і код виберіть Макрос.
У спадному меню Додати нову макрокоманду виберіть дію ЗапускМакросаДанних. потім виберіть макрос Orders.UpdateTotals. як показано на рис. 7, збережіть макрос як UpdateTotals і натисніть кнопку Виконати.
Після завершення макросу виберіть таблицю "Замовлення" і переконайтеся, що поле "OrderTotal" тепер містить загальну суму покупок за замовленням.
Ви можете створити запит підсумків, щоб перевірити результати.
Мал. 7. Створення макросу для виконання макросу даних
Створення іменованого макросу UpdateTotal
Кожне з цих подій даних ( "Після вставки", "Після оновлення" і "Після видалення") має обробляти по суті одну і ту ж задачу - їм необхідно обчислити нову суму для складання (або віднімання для події "Після оновлення" і "Після видалення ") на основі значення поля" OrderTotal ", а потім внести необхідні зміни. Так як всім макросам подій потрібні загальні можливості, має сенс створити один іменований макрос, який можна викликати з подій даних. Для цього відкрийте таблицю "Відомості про замовлення" і створіть іменований макрос, як і для таблиці "Замовлення". Цьому макросу потрібно два параметри. Клацніть посилання Створити параметр два рази і вкажіть значення, як показано на рис. 8.
Мал. 8. Створення параметрів макросу
В поле зі списком виберіть макрокоманду НайтіЗапісь і вставте параметри, як показано на рис. 9. Ця команда дозволяє знайти в таблиці "Замовлення" рядок, що відповідає значенню OrderID, яке передається в макрос.
Мал. 9. Пошук правильного замовлення
У макрокоманді НайтіЗапісь додайте макрокоманду ІзменітьЗапісь, а в ньому додайте дію ЗадатьПоле. Налаштування відповідних параметрів макрокоманд, як показано на рис. 10. Ці значення задають для поля Закази.OrderTotal старе значення, збільшене на значення, передане цього макросу.
Мал. 10. Оновлення поля "OrderTotal"
На стрічці натисніть кнопку Зберегти. збережіть новий іменований макрос як UpdateTotal. а потім натисніть кнопку Закрити.
Обробка події "Після вставки"
При вставці нового рядка в таблицю "Відомості про замовлення", необхідно додати значення поля "Кількість * [ціна одиниці]" до відповідного рядка в таблиці "Замовлення". Для цього спочатку переконайтеся, що таблиця "Відомості про замовлення" відкрита і обрана. На стрічці відкрийте вкладку Таблиця. Потім в групі Події після виберіть подія Після вставки. При цьому буде створено макрос, що викликається Access після створити новий рядок в таблицю "Відомості про замовлення". У новому макросі виберіть макрокоманду ЗапускМакросаДанних і вставте параметри, як показано на рис. 11. На стрічці натисніть кнопку Закрити і збережіть макрос коли з'явиться відповідний запит.
Мал. 11. Створення макросу даних "Після вставки"
Обробка події "Після оновлення"
Для обробки події "Після оновлення" потрібно трохи більше зусиль. Необхідно відняти старе значення, пов'язане з відомостями про замовлення, а потім додати нове значення. Access надає вихідні значення для кожного поля в якості властивостей об'єкта Old в цьому макросі даних, тому ви можете працювати з поточними та старими значеннями.
На стрічці клацніть Після оновлення. щоб створити макрос даних для події, а в поле зі списком виберіть Якщо і використовуйте вбудовану функцію Updated. щоб визначити, чи були поля "Ціна за одиницю" або "Кількість" оновлені в рамках поточного оновлення. Якщо це не так, не змінюйте таблицю "Замовлення". На рис. 12 показано вираз, яке слід ввести в конструкторі макросів.
Мал. 12. Вказівка умовного виразу для макрокоманди "Якщо"
У макрокоманді If викличте макрокоманду ЗапускМакросаДанних вказавши параметри, як показано на рис. 13. Майте на увазі, що ця дія віднімає старе значення для розширеної суми. Ви додасте розширену суму поточного рядка в наступній дії.
Мал. 13. Віднімання старої розширеної суми поточного рядка
Завершіть макрос, додавши останній виклик макрокоманди ЗапускМакросаДанних і додавши поточну розширену суму (див. Рис. 14).
Мал. 14. Додавання розширеної суми поточного рядка
На стрічці натисніть кнопку Зберегти. а потім натисніть Закрити.
Обробка події "Після видалення"
При видаленні рядка таблиці "Відомості про замовлення" необхідно відняти стару розширену суму, як в попередньому прикладі. Повторіть дії з попереднього розділу, але виберіть подія "Після видалення" і додайте тільки один виклик макрокоманди ЗапускМакросаДанних. По завершенні параметри буде виглядати, як показано на рис. 15. Натисніть кнопку Закрити і збережіть макрос коли з'явиться відповідний запит.
Мал. 15. Віднімання розширеної суми вилученої рядки