В этой статье объясняется, как использовать запросы с наиболее строгими значениями и итоговые запросы для поиска самых последних и ранних дат в наборе записей. Это поможет вам ответить на различные бизнес-вопросы, например, когда клиент в последний раз разместил заказ или какие пять кварталов были лучшими для продаж по городам.
В этом разделе...
- Обзор
- Подготовьте образцы данных для работы с примерами
- Поиск наиболее или наименьшей последней даты
- Поиск наибольших или наименьших последних дат для групп записей
Обзор
Вы можете ранжировать данные и просматривать элементы с наивысшим рейтингом с помощью запроса "Лучшие значения". Запрос на лучшее значение — это запрос на выборку, который возвращает указанное число или процент значений из верхней части результатов, например, пять самых популярных страниц на веб-сайте. Запросы к верхним значениям можно использовать для любых типов значений — это не обязательно должны быть числа.
Если необходимо сгруппировать или обобщить данные перед их ранжированием, нет необходимости использовать запрос "Основные значения". Предположим, что нужно найти объем продаж за указанную дату для каждого города, в котором работает компания. В этом случае города становятся категориями (необходимо собрать данные по городам), поэтому можно использовать итоговый запрос.
При использовании запроса на основные значения для поиска записей, которые содержат самые ранние даты в таблице или группе записей, можно ответить на различные бизнес-вопросы, включая следующие:
- Кто добился наибольших продаж в последнее время?
- Когда клиент делал заказ в последний раз?
- Когда следующие три дня рождения в команде?
Чтобы создать запрос на наибольшее значение, сначала создайте запрос на выборку. Затем отсортируйте данные в соответствии с вашим вопросом — ищете ли вы верхнюю или нижнюю часть. Если необходимо сгруппировать или обобщить данные, преобразуйте запрос на выборку в итоговый запрос. Затем можно использовать агрегатные функции, например Max или Min , для возврата наибольшего или наименьшего значения, а также First или Last для возврата самой ранней или самой поздней даты.
В этой статье предполагается, что используемые значения дат имеют тип данных "Дата/время". Если значения дат находятся в текстовом поле, .
Рассмотрите возможность использования фильтра вместо запроса основных значений
Фильтр обычно лучше, если вы помните о конкретной дате. Чтобы определить, стоит ли создавать запрос на набор значений или же следует применить фильтр, примите во внимание следующее:
- Если вы хотите вернуть все записи, в которых эта дата совпадает, предшествует или позже определенной даты, используйте фильтр. Например, для просмотра дат продаж между апрелем и июлем нужно применить фильтр.
- Если требуется вернуть указанное количество записей с недавними или самыми поздними датами в поле, но точные значения дат неизвестны или они не имеют значения, создайте запрос на основные значения. Например, чтобы просмотреть пять лучших кварталов продаж, используйте запрос с максимальными значениями.
Дополнительные сведения о создании и использовании фильтров см. в статье "Применение фильтра для просмотра отдельных записей в базе данных Access".
Подготовьте образцы данных для работы с примерами
В действиях, описанных в этой статье, используются данные из следующих примеров таблиц.
Таблица "Сотрудники"
| Фамилия | Имя | Адрес | Город | СтранаOrR egion | Дата рождения | Дата приема на работу |
|---|---|---|---|---|---|---|
| Авдеев | Григорий | Загородное шоссе, д. 150 | Москва | РФ | 05-фев-1968 | 10-июн-1994 |
| Кузнецов | Артем | ул. Гарибальди, д. 170 | Пермь | РФ | 22-май-1957 | 22-ноя-1996 |
| Дегтярев | Дмитрий | ул. Кедрова, д. 54 | Красноярск | РФ | 11-ноя-1960 | 11-мар-2000 |
| Зуева | Ольга | ул. Губкина, д. 233 | Тверь | РФ | 22-мар-1964 | 22-июн-1998 |
| Белых | Николай | пл. Хо Ши Мина, д. 15, кв. 5 | Москва | РФ | 05-июн-1972 | 05-янв-2002 |
| Комарова | Лина | ул. Ляпунова, д. 70, кв. 16 | Красноярск | РФ | 23-янв-1970 | 23-апр-1999 |
| Зайцев | Сергей | ул. Строителей, д. 150, кв. 78 | Омск | РФ | 14-апр-1964 | 14-окт-2004 |
| Ермолаева | Анна | ул. Вавилова, д. 151, кв. 8 | Иркутск | РФ | 29-окт-1959 | 29-мар-1997 |
Таблица EventType
| КодТипа | Тип мероприятия |
|---|---|
| 1 | Презентация товара |
| 2 | Корпоративное мероприятие |
| 3 | Частное мероприятие |
| 4 | Мероприятие по сбору средств |
| 5 | Выставка-продажа |
| 6 | Лекция |
| 7 | Концерт |
| 8 | Выставка |
| 9 | Уличная ярмарка |
Таблица "Клиенты"
| КодКлиента | Компания | Контакт |
|---|---|---|
| 1 | Contoso, Ltd. НИИ | Николай Белых |
| 2 | Лесопитомник | Регина Покровская |
| 3 | Fabrikam | Елена Матвеева |
| 4 | Лесопитомник | Афанасий Быков |
| 5 | А. Datum | Лилия Медведева |
| 6 | Adventure Works | Максим Измайлов |
| 7 | железа | Арина Иванова |
| 8 | Художественная школа | Полина Кольцова |
Таблица "Мероприятия"
| КодМероприятия | Тип мероприятия | Клиент | Дата мероприятия | Цена |
|---|---|---|---|---|
| 1 | Презентация товара | Contoso, Ltd. | 4/14/2011 | 10 000 ₽ |
| 2 | Корпоративное мероприятие | Лесопитомник | 4/21/2011 | 8000 ₽ |
| 3 | Выставка-продажа | Лесопитомник | 01.05.2011 | 25000 ₽ |
| 4 | Выставка | НИИ железа | 5/13/2011 | 4 500 ₽ |
| 5 | Выставка-продажа | Contoso, Ltd. | 5/14/2011 | 55 000 ₽ |
| 6 | Концерт | Художественная школа | 5/23/2011 | 12 000 ₽ |
| 7 | Презентация товара | А. Datum | 6/1/2011 | 15 000 ₽ |
| 8 | Презентация товара | Лесопитомник | 6/18/2011 | 21 000 ₽ |
| 9 | Мероприятие по сбору средств | Adventure Works | 6/22/2011 | 1300 ₽ |
| 10 | Лекция | НИИ железа | 6/25/2011 | 2450 ₽ |
| 11 | Лекция | Contoso, Ltd. | 04.07.2011 | 3800 ₽ |
| 12 | Уличная ярмарка | НИИ железа | 04.07.2011 | 55 000 ₽ |
Примечание
Действия, описываемые в данном разделе, предполагают, что таблицы "Клиенты" и "Типы мероприятий" находятся на стороне "один" отношения "один-ко-многим" с таблицей "Мероприятия". В данном случае таблица "Мероприятия" имеет с этими таблицами общие поля "КодКлиента" и "КодТипа". Итоговые запросы, описанные в следующих разделах, не будут работать, если эти связи отсутствуют.
Вставка данных примера в лист Excel
- Запустите Excel. Откроется пустая книга.
- Нажмите клавиши SHIFT+F11, чтобы вставить лист (потребуется четыре).
- Скопируйте данные из каждого образца таблицы на пустой лист. Добавьте заголовки столбцов (первая строка).
Создание таблиц базы данных на основе листов
- Выберите данные из первого листа, включая заголовки столбцов.
- Щелкните область навигации правой кнопкой мыши и выберите команду "Вставить".
- Нажмите кнопку " Да ", чтобы подтвердить, что первая строка содержит заголовки столбцов.
- Повторите шаги 1–3 для каждого из оставшихся листов.
Поиск наиболее или наименьшей последней даты
В этом разделе на основе примеров данных используется процесс создания запроса на основные значения.
Создание простого запроса на набор значений
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу "Сотрудники" и нажмите кнопку "Закрыть".
Если используется пример данных, добавьте в запрос таблицу "Сотрудники".Добавьте на бланк поля, которые вы хотите использовать в запросе. Вы можете дважды щелкнуть каждое поле или перетащить его в пустую ячейку в строке Поле.
Если вы работаете с примером таблицы, то добавьте поля "Фамилия", "Имя" и "Дата рождения".В поле, которое содержит искомые наибольшие или наименьшие значения (при использовании примера таблицы — поле "Дата рождения), в строке Сортировка выберите порядок сортировки По возрастанию или По убыванию.
При сортировке по убыванию будут возвращены самые последние даты, при сортировке по возрастанию — самые давние.Важно
В строке Сортировка следует установить значение только для полей, содержащих даты. Если порядок сортировки задан по другому полю, запрос не вернет ожидаемых результатов.
На вкладке Конструктор в группе Сервис щелкните стрелку вниз рядом со значением Все (список Набор значений) и либо введите число записей, которые вы хотите просмотреть, либо выберите значение из списка.
Нажмите
", чтобы запустить запрос и отобразить результаты в режиме таблицы.Сохраните запрос как NextBirthDays.
Как вы видите, этот тип запросов на набор значений дает ответы на основные вопросы, например "Кто из сотрудников самый старший или самый молодой?". Ниже описано, как с помощью выражений и других условий создавать более точные и гибкие запросы. Запрос по описанным ниже условиям выдает ближайшие дни рождения у трех сотрудников.
Добавление условий в запрос
На этих шагах используется запрос, созданный в предыдущей процедуре. Можно использовать другие запросы с максимальными значениями, если они содержат фактические данные даты и времени, а не текстовые значения.
Совет
Чтобы лучше понять, как работает этот запрос, переключайтесь между режимами конструктора и таблицы на каждом шаге. Если вы хотите увидеть фактический код запроса, переключитесь в режим SQL. Для переключения между представлениями щелкните правой кнопкой мыши вкладку в верхней части запроса и выберите нужное представление.
В области навигации щелкните правой кнопкой мыши запрос NextBirthDays и выберите пункт "Конструктор".
В бланке запроса в столбце справа от поля "ДатаРождения" введите следующее:
MonthBorn: DatePart("m",[BirthDate]).
Это выражение извлекает месяц из BirthDate с помощью функции DatePart .В следующем столбце бланка запроса введите следующее:
DayOfMonthBorn: DatePart("d",[BirthDate])
Это выражение извлекает день месяца из BirthDate с помощью функции DatePart .Снимите флажки проверка в строке "Показать" для каждого из двух введенных выражений.
Щелкните строку "Сортировка " для каждого выражения и выберите вариант "По возрастанию".
В строке "Условие отбора " столбца "Дата рождения " введите следующее выражение:
Month([Дата рождения]) > Month(Date()) OR Month([Дата рождения])= Month(Date()) AND Day([Дата рождения])>Day(Date())
Это выражение выполняет следующие действия:Месяц([дата рождения]) > Month(Date()) указывает, что дата рождения каждого сотрудника приходится на будущий месяц.
Параметр Month([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date()) указывает, что если дата рождения приходится на текущий месяц, то день рождения приходится на текущий день или следующий за ним.
Короче говоря, это выражение исключает любые записи, где день рождения приходится на период между 1 января и текущей датой.Совет
Дополнительные примеры выражений условий запроса см. в статье Примеры условий запроса.
На вкладке " Конструктор " в группе "Настройка запроса " введите "3 " в поле "Возврат ".
На вкладке "Дизайн" в группе "Результаты" нажмите
"Запустить".
Примечание
В запросе с использованием собственных данных иногда может отображаться больше записей, чем указано. Если данные содержат несколько записей с общим значением, которое входит в число верхних, запрос вернет все такие записи, даже если это означает возврат большего числа записей, чем ожидалось.
Поиск наибольших или наименьших последних дат для групп записей
Итоговый запрос используется для поиска самых ранних или самых поздних дат для записей, которые попадают в группы, например событий, сгруппированных по городам. Итоговый запрос — это запрос на выборку, в котором используются агрегатные функции (например, Group By, Min, Max, Count, One и Last) для вычисления значений для каждого выходного поля.
Добавьте поле, которое нужно использовать для категорий (для группировки), и поле со значениями, которые нужно суммировать. При добавлении других полей вывода, например имен клиентов при группировке по типу события, запрос также будет использовать эти поля для группировки, изменяя результаты так, чтобы они не отвечали на исходный вопрос. Чтобы пометить строки другими полями, создайте дополнительный запрос, использующий итоговый запрос в качестве источника, и добавьте в него дополнительные поля.
Совет
Создание запросов по шагам — очень эффективная стратегия для ответа на более сложные вопросы. Если вам сложно заставить работать сложный запрос, подумайте о том, нельзя ли разбить его на несколько более простых запросов.
Создание итогового запроса
В этой процедуре на этот вопрос используются примеры таблицы "События " и пример таблицы "Тип события ".
Когда было самое последнее событие каждого типа события, кроме концертов?
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните таблицы "События" и "Тип события".
Каждая таблица появится в верхнем разделе конструктора запросов.Дважды щелкните поле EventType в таблице EventType и поле EventDate в таблице Events, чтобы добавить поля в бланк запроса.
На бланке запроса в строке "Условие отбора " поля "Тип события " введите <>"Concert".
Совет
Дополнительные примеры выражений условий см. в статье Примеры условий запроса.
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.
На бланке запроса щелкните строку "Итог " поля "ДатаСобытия" и выберите значение "Максимальный".
На вкладке Конструктор в группе Результаты выберите команду Режим, а затем — пункт SQL.
В окне SQL в конце предложения SELECT сразу после ключевого слова AS замените MaxOfEventDate на MostRecent.
Сохраните запрос как MostRecentEventByType.
Создание второго запроса для отображения более подробных данных
Чтобы ответить на этот вопрос, в этой процедуре используется запрос MostRecentEventByType из предыдущей процедуры:
Кто был клиентом последнего события в каждом типе события?
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
На вкладке "Запросы " дважды щелкните запрос MostRecentEventByType.
На вкладке "Таблицы " дважды щелкните таблицы "События" и "Клиенты".
В конструкторе запросов дважды щелкните следующие поля:
- В таблице "События" дважды щелкните "Тип события".
- В запросе MostRecentEventByType дважды щелкните MostRecent.
- В таблице "Клиенты" дважды щелкните "Компания".
На бланке запроса в строке "Сортировка " столбца "Тип события " выберите "По возрастанию".
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.