Пошук записів, що містять найбільші або найменші значення в групі або в поле
У цій статті пояснюється, як працювати з запитами на набір значень для пошуку найбільших або найменших значень в наборі записів. Запити на набір значень використовуються для відповіді на різні ділові питання, наприклад про те, який товар продається добре, а який погано.
Виберіть потрібну дію
Загальні відомості про запити на набір значень
Запит на набір значень використовується для пошуку записів, що містять найбільші або найменші значення в поле таблиці. Ви можете використовувати такі запит для отримання відповідей на наступні типи питань:
Який товар є найбільш дорогим або найбільш дешевим?
У яких трьох співробітників найближчим часом буде день народження?
Які відділи лідирують або відстають з продажу в цьому році?
Які 5 відсотків товарів найгірше або найкраще продаються?
Коротко кажучи, запит на набір значень сортує дані, потім фільтрує їх і повертає найбільші або найменші значення в поле. Ви можете використовувати його пошуку числових (в тому числі грошових) значень і значень дати.
Створення запиту на набір значень включає наступні основні етапи:
Створення запиту на вибірку або підсумкового запиту.
Застосування до запиту інших умов.
Завдання числа записів, які повинен повертати запит. Ви можете вказати відсоток, наприклад перші 5 відсотків значень в поле, або конкретне число, наприклад перші 10 значень в поле.
Вибір між запитом на набір значень і фільтром
Щоб визначити, чи варто створювати запит на набір значень або ж слід застосувати фільтр, візьміть до уваги наступне:
Якщо ви хочете відібрати записи, що містять найбільші або найменші значення в поле, але точні максимальні і мінімальні значення поля не відомі або не мають значення, то слід створити запит на набір значень.
Якщо ви хочете відібрати всі записи, в яких значення поля рівні потрібного значення або більше або менше його, то слід застосувати фільтр. Наприклад, щоб відібрати товари з ціною від 50 до 100 ₽, краще застосувати фільтр. На малюнку показані приклад фільтра в Конструкторі із запитом і результати його застосування:
Додаткові відомості про створення та застосування фільтрів см. В статті Фільтр: обмеження кількості записів в поданні.
Правила створення і використання запитів на набір значень
Пошук записів, що містять найбільші або найменші значення
У цьому розділі пояснюється, як створювати прості і більш складні запити на набір значень. Спочатку описані основні складові запиту на набір значень, а потім показується, як знайти найближчі дні народження співробітників. Як приклад використовується наступна таблиця:
Як це зробити?
Введення прикладів даних вручну
на вкладці Створення в групі Таблиці натисніть кнопку Таблиця.
Примітка: Цю операцію слід виконувати тільки при необхідності додати таблицю в базу даних. При відкритті нової пустої бази даних ця дія не потрібно.
Двічі клацніть першу клітинку в рядку заголовків і введіть ім'я поля з прикладу таблиці.
За замовчуванням в Access порожні поля позначаються написом Додати поле в рядку заголовків:
За допомогою клавіш зі стрілками перейдіть в наступну вільну позицію заголовка і введіть ім'я другого поля (щоб перейти в іншу клітинку, можна також натиснути клавішу TAB або клацнути цей осередок два рази). Повторіть ці дії, щоб ввести імена всіх полів.
Завершивши введення даних, натисніть кнопку Зберегти
Швидкий доступ: натисніть CTRL + S.
Відкриється діалогове вікно Збереження документа.
В поле Ім'я таблиці введіть ім'я прикладу і натисніть кнопку ОК.
Слід використовувати зазначені імена таблиць, тому що вони використовуються в запитах, наведених в покрокових інструкціях.
Повторюйте ці кроки, поки не створите кожен із прикладів таблиць, наведених на початку цього розділу.
Скопіюйте приклад таблиці з попереднього розділу і вставте його на перший лист, починаючи з першого осередку.
Збережіть файл електронної таблиці в зручному місці і переходите до наступних кроків.
Імпорт таблиці в Access
Натисніть кнопку Огляд. відкрийте файл електронної таблиці, створений на попередніх етапах, і натисніть кнопку ОК.
Відкриється вікно майстра імпорту електронних таблиць.
За замовчуванням майстер вибирає перший лист в книзі (в цьому прикладі - лист "Співробітники"), і дані з цього листа з'являються в нижній частині сторінки майстра. Натисніть кнопку "Далі .
На наступній сторінці майстра виберіть Перший рядок містить назви стовпців. а потім натисніть кнопку Далі.
Якщо потрібно, на наступній сторінці майстра ви можете змінити імена полів і типи даних або пропустити деякі поля, скориставшись текстовими полями і списками в групі Параметри поля. В іншому випадку натисніть кнопку Далі.
Залиште параметр автоматично створити ключ обраним і натисніть кнопку Далі.
За замовчуванням Access використовує ім'я листа для нової таблиці. Залиште це ім'я або введіть інше, а потім натисніть кнопку Готово.
Створення простого запиту на набір значень
На вкладці Створити у групі Інша натисніть кнопку Конструктор запитів.
У діалоговому вікні Додавання таблиці виберіть таблицю, яку ви хочете використовувати для запиту, натисніть кнопку Додати. щоб помістити таблицю на бланк, і натисніть кнопку Закрити.
-або-
Двічі клацніть таблицю.
Якщо ви використовуєте приклад даних, додайте в запит таблицю "Співробітники".
Додайте на бланк поля, які ви хочете використовувати в запиті. Ви можете двічі клацнути кожне поле або перетягнути його в порожню клітинку в рядку Поле.
Якщо ви працюєте з прикладом таблиці, то додайте поля "Прізвище", "Ім'я" і "Дата народження".
В поле, що містить найбільші і найменші значення (в нашому прикладі - поле "Дата народження"), клацніть рядок Сортування і виберіть По зростанню або За спаданням.
Якщо поля значень містять числа, наприклад ціни або обсяги продажів, то порядок сортування По зростанню повертає найменші значення, а порядок сортування По убуванню - найбільші значення. Якщо поля значень містять дати, то порядок сортування По убуванню повертає останню дату, а порядок сортування По зростанню - найбільш ранню дату.
Важливо: Необхідно вказати в рядку Сортування значення По зростанню або За спаданням тільки для полів, в яких ви шукаєте найбільші і найменші значення. Якщо задати порядок сортування для іншого поля (наприклад, поля "НазваніеТовара" замість поля "Ціна"), запит не повертатиме необхідні результати.
Щоб виконати запит і відобразити результати в режимі таблиці, натисніть кнопку Виконати.
Збережіть запит і залиште його відкритим, щоб використовувати на наступних кроках.
Як ви бачите, цей тип запитів на набір значень дає відповіді на основні питання, наприклад "Хто зі співробітників найстарший або наймолодший?". Нижче описано, як за допомогою виразів та інших умов створювати більш точні і гнучкі запити. Запит по описаним нижче умов видає найближчі дні народження у трьох співробітників.
Додавання умов в запит
Примітка: У цих інструкціях передбачається, що ви використовуєте запит, описаний в попередньому розділі.
Відкрийте запит, створений на попередніх кроках, в Конструкторі.
У бланку запиту в стовпці праворуч від стовпця "Дата народження" »скопіюйте і вставте або введіть вираз Expr1: DatePart (" m "; [Дата народження]). Функція DatePart витягує місяць із значення в поле "Дата народження".
Праворуч від стовпчика, що містить вираз, скопіюйте та вставте або введіть вираз Expr2: DatePart ( "d"; [Дата народження]). У цьому випадку функція DatePart витягує день з значення в поле "Дата народження".
У рядку Показати зніміть прапорці для всіх виразів, а потім клацніть осередок Сортування для кожного виразу і виберіть порядок По зростанню.
При необхідності ви можете вказати умови для обмеження області запиту. При завданні умов запит сортує тільки записи, що відповідають їм, і визначає найбільші або найменші значення в відсортованому списку.
У наведеному прикладі введіть або скопіюйте і вставте в рядок Умови відбору поля "Дата народження" такий вираз:
Month ([Дата народження])> Month (Date ()) Or Month ([Дата народження]) = Month (Date ()) And Day ([Дата народження])> Day (Date ())
Інші приклади виразів умов для запитів можна знайти в статті Приклади умов запиту.
Щоб виконати запит і відобразити результати в режимі таблиці, натисніть кнопку Виконати.
Якщо запит повертає більше записів, ніж очікувалося
Третя і четверта записи здаються однаковими, але це може пояснюватися тим, що Попов обробив два різних замовлення, відвантажених в один день.
Щоб уникнути відображення повторюваних записів, можна виконати одну з двох дій в залежності від необхідного результату. Ви можете змінити структуру запиту, додавши поля, які дозволять розрізнити записи, наприклад поля "КодЗаказа" і "КодКліента". Або, якщо достатньо показати тільки одну з повторюваних записів, ви можете вибрати відображення тільки унікальних записів, задавши значення Так для властивості запиту Унікальні значення. Щоб налаштувати це властивість, в Конструкторі натисніть клавішу F4, щоб відобразити сторінку властивостей запиту, знайдіть властивість Унікальні значення і встановіть для нього значення Так.
Додаткові відомості про роботу з повторюваними записами см. В статті Пошук, приховування або видалення повторюваних даних.
Створення підсумкового запиту
На вкладці Створити у групі Інша натисніть кнопку Конструктор запитів.
Перетворіть запит в підсумковий запит.
На вкладці Конструктор у групі Показати чи приховати натисніть кнопку Підсумки.
У бланку запиту з'явиться рядок Підсумок.
На вкладці Конструктор у групі Сервіс переконайтеся, що в списку Набір значень вибрано Все. а потім натисніть кнопку Виконати. щоб запустити запит і відобразити результати в режимі таблиці.
Примітка: В залежності від функції, обраної на кроці 7, Access змінює ім'я поля значення в запиті на Максімум_ ІмяПоля або Мінімум_ ІмяПоля. У нашому прикладі поле будуть перейменовано в Максімум_Цена або Мінімум_Цена.
Збережіть запит і переходите до наступних кроків.
Створення другого запиту для відображення більш докладних даних
На вкладці Створити у групі Інша натисніть кнопку Конструктор запитів.
У діалоговому вікні Додавання таблиці відкрийте вкладку Запити. а потім двічі клацніть підсумковий запит, створений в попередньому розділі.
Зв'яжіть поля в підсумковому запиті з відповідними полями в батьківських таблицях. Для цього перетягніть кожне поле з підсумкового запиту на відповідне поле в таблиці.
У вікні підсумкового запиту двічі клацніть зірочку (*), щоб додати запит цілком в бланк, а потім перетягніть додаткові поля з інших таблиць запиту.
У нашому прикладі двічі клацніть підсумковий запит, створений в попередньому розділі, а потім додайте поля "Найменування товару" і "Постачальник" з таблиць "Товари" і "Постачальники" відповідно.
На вкладці Конструктор у групі Результати натисніть кнопку Виконати.
Запит буде виконаний, а його результати виведуться в режимі таблиці.
Порада. Якщо вам не подобається, що заголовок стовпця "Ціна" називається Максімум_Цена або Мінімум_Цена. то відкрийте запит в Конструкторі і в стовпці "Ціна" в бланку запиту введіть Ціна: Максімум_Цена або Ціна: Мінімум_Цена. Після цього в заголовку стовпця в режимі таблиці буде виводитися назва Ціна.
Пошук записів, що містять найбільші і найменші значення
Запити, створені раніше в цій статті, повертають або найбільші, або найменші значення, але не обидва набору відразу. Якщо ви хочете відобразити обидва набору значень в одному поданні, слід створити два запити (один для отримання максимальних значень і інший для отримання найменших значень), а потім об'єднати і зберегти результати в одній таблиці.
Пошук найбільших і найменших значень і відображення цих даних в таблиці складається з наступних основних етапів:
Створення запиту на пошук максимальних значень і запиту на пошук найменших значень або, при необхідності угруповання даних, створення підсумкових запитів, що використовують функції Min і Max.
Перетворення запиту на пошук максимальних значень (або підсумкового запиту на пошук максимальних значень) в запит на створення таблиці і виконання цього запиту для створення таблиці.
Перетворення запиту на пошук найменших значень (або підсумкового запиту на пошук мінімальних значень) в запит на додавання для додавання записів в таблицю максимальних значень.
Нижче описаний процес створення такого запиту.
Примітка: Якщо база даних не має цифрового підпису або не перебуває у надійному місці розташування, Access забороняє запускати описані тут запити на зміну. Виконайте перший набір кроків для включення заблокованого вмісту, перш ніж намагатися створити описані тут запити на створення таблиці і додавання.
Включення вмісту бази даних
На панелі повідомлень натисніть Параметри.
Відкриється діалогове вікно Параметри безпеки Microsoft Office.
Виберіть Увімкнути це вміст. а потім кнопку ОК.
Виконайте запит ще раз.
Якщо ви не бачите панель повідомлень
На вкладці Робота з базами даних в групі Показати чи приховати натисніть кнопку Панель повідомлень.
Створіть запити на пошук найбільших і найменших значень.
Збережіть кожен запит і залиште його відкритим, щоб використовувати на наступних кроках.
Створення запиту на створення таблиці
У запиті на пошук максимальних значень, відкритому в Конструкторі:
На вкладці Конструктор у групі Тип запиту натисніть кнопку Створення таблиці.
Відкриється діалогове вікно Створення таблиці.
В поле Ім'я таблиці введіть ім'я таблиці, яка буде зберігати записи з найбільшими і найменшими значеннями. Наприклад, введіть Найбільші і найменші значення. а потім натисніть кнопку ОК.
Кожен раз при виконанні запиту замість відображення результатів в режимі таблиці запит буде створювати таблицю і заміщати значення поточними даними.
Збережіть і закрийте запит.
Створення запиту на додавання
Відкрийте запит на пошук найменших значень в Конструкторі.
На вкладці Розробка в групі Тип запиту виберіть команду Додати.
У діалоговому вікні Додавання введіть те ж ім'я, що було зазначено в діалоговому вікні Створення таблиці.
Наприклад, введіть Найбільші і найменші значення. а потім натисніть кнопку ОК. Кожен раз при виконанні запиту замість відображення результатів в режимі таблиці він буде додавати записи в таблицю "Найбільші і найменші значення".
Збережіть і закрийте запит.
виконання запитів
Тепер ви готові запустити обидва запиту.
У вікні бази даних двічі клацніть запит на пошук максимальних значень і натисніть кнопку Так в повідомленнях.
Двічі клацніть запит на пошук найменших значень і натисніть кнопку Так в повідомленнях.
Відкрийте таблицю з набором записів в режимі таблиці.