В этой статье объясняется, как использовать запрос к верхним значениям в Access для поиска самых последних или самых ранних дат в наборе записей. Результаты можно использовать для ответов на бизнес-вопросы, например, когда клиент в последний раз разместил заказ.
Выберите нужное действие
- Сведения о работе запросов на набор значений с датами
- Поиск самой последней или самой давней даты
- Поиск самых последних или самых давних дат для записей в категории или группе
- Одновременный поиск самых последних и самых давних дат
Сведения о работе запросов на набор значений с датами
Запросы на набор значений используются, когда возникает необходимость найти в таблице или группе записи, содержащие самую последнюю или самую давнюю дату. Полученные данные позволят отвечать на различные деловые вопросы, например следующие:
- Когда сотрудник в последний раз продавал товар? Ответ поможет вам определить наиболее продуктивного или наименее продуктивного сотрудника.
- Когда клиент делал заказ в последний раз? Если в течение определенного периода заказов не было, его можно перенести в список неактивных клиентов.
- У кого следующий день рождения или следующий день рождения?
Правила создания и использования запросов на набор значений
Вы создаете запрос с верхними значениями, сначала создав запрос на выборку. В зависимости от нужных результатов вы либо применяете порядок сортировки к запросу, либо преобразуете его в итоговый запрос. При преобразовании запроса используйте агрегатную функцию, например Max или , Min чтобы вернуть наибольшее или наименьшее значение, или LastFirst для возврата самой ранней или последней даты. Используйте запросы итогов и агрегатные функции только в том случае, если необходимо найти данные, которые попадают в группы или категории. Например, если вам нужно найти номера продаж для заданной даты для каждого города, в котором работает ваша компания, города становятся категориями, поэтому вы используете запрос итогов.
По мере продолжения помните, что в запросах должны использоваться поля, содержащие описательные данные, такие как имена клиентов, и поле, содержащее значения дат, которые вы хотите найти. Значения даты должны находиться в поле Date/Time типа данных. Запросы, приведенные в этой статье, завершаются ошибкой, если они выполняются по значениям Short Text даты в поле. Если вы хотите использовать запрос итогов, поля данных также должны содержать сведения о категориях, например о городе или стране или регионе.
Выбор между запросом на набор значений и фильтром
Чтобы определить, стоит ли создавать запрос на набор значений или же следует применить фильтр, примите во внимание следующее:
- Если вы хотите получить записи, в полях которых содержатся самые последние или самые давние даты, а точные значения дат неизвестны или не имеют значения, следует создать запрос на набор значений.
- Если вы хотите получить все записи, в которых даты совпадают с определенной датой, предшествуют ей или следуют за ней, используйте фильтр. Например, для просмотра дат продаж между апрелем и июлем нужно применить фильтр. Подробное обсуждение фильтров выходит за пределы данной темы. Дополнительные сведения о создании и использовании фильтров см. в статье Применение фильтра для просмотра избранных записей в базе данных Access.
Поиск самой последней или самой давней даты
В этом разделе описано, как создать запрос основных значений, использующий порядок сортировки, и более сложный запрос, использующий выражения и другие критерии. В первой части показаны основные шаги по созданию запроса с максимальными значениями. Во второй части объясняется, как найти следующие несколько дней рождения сотрудников путем добавления условий. Используются данные из следующей таблицы.
| Фамилия | Имя | Адрес | Город | Страна или регион | Дата рождения | Дата приема на работу |
|---|---|---|---|---|---|---|
| Авдеев | Григорий | Загородное шоссе, д. 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 |
При необходимости вы можете ввести эти примеры данных в новую таблицу вручную или скопировать этот пример таблицы в электронную программу, например Microsoft Excel, а затем с помощью Access импортировать полученный лист в таблицу.
Создание простого запроса на набор значений
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
В диалоговом окне щелкните таблицу, которую вы хотите использовать в запросе, нажмите Добавить, чтобы поместить ее в верхний раздел конструктора запросов, и нажмите кнопку Закрыть. Или дважды щелкните таблицу и нажмите кнопку Закрыть. Если вы используете пример данных, перечисленных в предыдущем разделе, добавьте таблицу
Employeesв запрос.Добавьте на бланк поля, которые вы хотите использовать в запросе. Вы можете дважды щелкнуть каждое поле или перетащить его в пустую ячейку в строке Поле. Если вы работаете с примером таблицы, то добавьте поля "Фамилия", "Имя" и "Дата рождения".
В поле, которое содержит искомые наибольшие или наименьшие значения (при использовании примера таблицы — поле "Дата рождения), в строке Сортировка выберите порядок сортировки По возрастанию или По убыванию. При сортировке по убыванию будут возвращены самые последние даты, при сортировке по возрастанию — самые давние.
Важно
В строке Сортировка следует установить значение только для полей, содержащих даты. Если порядок сортировки задан по другому полю, запрос не вернет ожидаемых результатов.
На вкладке Конструктор запросов в группе Настройка запросов щелкните стрелку вниз рядом с пунктом Все в списке Верхние значения . Затем введите количество записей, которое вы хотите просмотреть, или выберите параметр из списка.
Выберите Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.
Сохраните запрос и оставьте его открытым, чтобы использовать на следующих шагах.
Как вы видите, этот тип запросов на набор значений дает ответы на основные вопросы, например "Кто из сотрудников самый старший или самый молодой?". Ниже описано, как с помощью выражений и других условий создавать более точные и гибкие запросы. Запрос по описанным ниже условиям выдает ближайшие дни рождения у трех сотрудников.
Добавление условий в запрос
Примечание
В этих инструкциях предполагается, что вы используете запрос, описанный в предыдущем разделе.
- Откройте запрос, созданный на предыдущих шагах, в Конструкторе.
- В сетке конструктора запроса в столбце справа от столбца Дата рождения скопируйте и вставьте или введите следующее выражение:
Expr1: DatePart("m",[Birth Date]). Затем нажмите кнопку Выполнить. ФункцияDatePartизвлекает часть месяца в поле Дата рождения . - Переключитесь в Конструктор.
- Справа от первого выражения вставьте или введите это выражение:
Expr2: DatePart("d",[Birth Date]). Затем нажмите кнопку Выполнить. В этом случаеDatePartфункция извлекает часть дня поля Дата рождения . - Переключитесь в Конструктор.
- Для обоих введенных выражений снимите флажки в строке Показать, щелкните строку Сортировка и выберите пункт По возрастанию.
- Выберите команду Выполнить.
- При необходимости вы можете указать условия для ограничения области запроса. После этого запрос будет сортировать только записи, удовлетворяющие им, и определять первые или последние значения полей из отсортированного списка.
Для продолжения работы с примером данных откройте Конструктор. Затем в строке Условия отбора столбца Дата рождения введите следующее выражение:
Month([Birth Date]) > Month(Date()) Or Month([Birth Date]) = Month(Date()) And Day([Birth Date]) > Day(Date())Это выражение работает следующим образом: частьMonth([Birth Date]) > Month(Date())проверяет дату рождения каждого сотрудника, чтобы увидеть, приходится ли она на будущий месяц. Если это так, запрос включает в себя запись. ЧастьMonth([Birth Date]) = Month(Date()) And Day([Birth Date]) > Day(Date())проверяет даты рождения в текущем месяце, чтобы узнать, приходится ли день рождения на текущий день или позже. Если это так, запрос включает в себя запись. Короче говоря, это выражение игнорирует все записи, в которых день рождения приходится на период с 1 января по день выполнения запроса. Другие примеры выражений условий для запросов можно найти в статье Примеры условий запроса. - На вкладке Конструктор запросов в группе Настройка запросов щелкните стрелку вниз рядом с пунктом Все в списке Верхние значения . Затем введите количество записей, которое вы хотите просмотреть, или выберите параметр из списка.
Чтобы просмотреть следующие три дня рождения, введите
3. - Выберите Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.
Если отображается больше записей, чем требовалось
Если в данных есть записи с одинаковым значением даты, запрос может возвращать больше записей, чем вы указали. Например, вы можете создать запрос на набор значений для получения записей о трех сотрудниках, н запрос вернет четыре, поскольку у Измайлова и Быкова дни рождения совпадают, как указано в следующей таблице.
| Фамилия | ДатаРождения |
|---|---|
| Белых | 26.09.1968 |
| Бутусов | 02.10.1970 |
| Измайлов | 15.10.1965 |
| Быков | 15.10.1969 |
Если отображается меньше записей, чем требовалось
Предположим, что вы создали запрос, возвращающий наибольшие или наименьшие пять записей в поле, но он возвращает только три. Как правило, чтобы решить эту проблему, нужно открыть запрос в Конструкторе и проверить строку Условия отбора для столбцов в бланке запроса.
Дополнительные сведения об условиях см. в статье Примеры условий запроса.
Если выводятся повторяющиеся записи
Если запрос на набор значений возвращает повторяющиеся значения, то либо базовые таблицы содержат повторяющиеся записи, либо записи отображаются как одинаковые, потому что запрос не включает поля, значения которых позволяют их различить. Например, в следующей таблице показан результат запроса, отображающего пять последних отгруженных заказов вместе с именем продавца, который проводил транзакцию.
| Дата поставки | Продавец |
|---|---|
| 12.11.2004 | Ковалев |
| 12.11.2004 | Маслов |
| 12.10.2004 | Попов |
| 12.10.2004 | Попов |
| 12.10.2004 | Ковалев |
Третья и четвертая записи кажутся одинаковыми, но это может объясняться тем, что Попов обработал два различных заказа, отгруженных в один день.
Чтобы избежать отображения повторяющихся записей, можно выполнить одно из двух действий в зависимости от требуемого результата. Вы можете изменить структуру запроса, добавив поля, которые позволят различить записи, например поля "КодЗаказа" и "КодКлиента". Или, если достаточно показать только одну из повторяющихся записей, вы можете выбрать отображение только уникальных записей, задав значение Да для свойства запроса Уникальные значения. Чтобы задать значение этого свойства, в Конструктор щелкните правой кнопкой мыши в любом свободном месте в верхней половине окна конструктора запросов и выберите в контекстном меню команду Свойства. В окне свойств найдите свойство Уникальные значения и задайте для него значение Да.
Дополнительные сведения о работе с повторяющимися записями см. в статье Поиск повторяющихся записей с помощью запроса.
Поиск самых последних или самых давних дат для записей в категории или группе
Для поиска самых последних или самых давних дат для записей, входящих в группы или категории, используются итоговые запросы. Запрос итогов — это запрос на выборку, который использует агрегатные функции, такие как Min, Max, Sum, Firstи Last , для вычисления значений для заданного поля.
В этом разделе предполагается, что вы запускаете бизнес управления событиями. Вы обрабатываете промежуточное хранение, освещение, питание и другие части больших функций. Мероприятия, которыми вы управляете, делятся на несколько категорий, таких как запуски продуктов, уличные ярмарки и концерты. В этом разделе объясняется, как ответить на распространенный вопрос: Когда является следующим событием по категориям? Другими словами, когда будет следующий запуск продукта, следующий концерт и так далее?
По мере продолжения помните следующее: по умолчанию тип запроса итогов, который вы создаете здесь, может включать только поле, содержащее данные группы или категории, и поле, содержащее даты. Нельзя включать другие поля, описывающие элементы в категории, например имена клиентов или поставщиков. Однако вы можете создать второй запрос, в котором будут содержаться итоговые запросы и поля с описательными данными. Далее в этом разделе описано, как это сделать.
Инструкции в данном разделе предполагают использование следующих трех таблиц:
Таблица "Типы мероприятий"
| КодТипа | Тип мероприятия |
|---|---|
| 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. | 14.04.2003 | 10 000 ₽ |
| 2 | Корпоративное мероприятие | Лесопитомник | 21.04.2003 | 8000 ₽ |
| 3 | Выставка-продажа | Лесопитомник | 01.05.2003 | 25000 ₽ |
| 4 | Выставка | НИИ железа | 13.05.2003 | 4 500 ₽ |
| 5 | Выставка-продажа | Contoso, Ltd. | 14.05.2003 | 55 000 ₽ |
| 6 | Концерт | Художественная школа | 23.05.2003 | 12 000 ₽ |
| 7 | Презентация товара | А. Datum | 01.06.2003 | 15 000 ₽ |
| 8 | Презентация товара | Лесопитомник | 18.06.2003 | 21 000 ₽ |
| 9 | Мероприятие по сбору средств | Adventure Works | 22.06.2003 | 1300 ₽ |
| 10 | Лекция | НИИ железа | 25.06.2003 | 2450 ₽ |
| 11 | Лекция | Contoso, Ltd. | 04.07.2003 | 3800 ₽ |
| 12 | Уличная ярмарка | НИИ железа | 04.07.2003 | 5500 ₽ |
Примечание
В этом разделе предполагается, что таблицы Customers и Event Type находятся на стороне "один" связей "один ко многим" с таблицей События . В этом случае в таблице События есть поля CustomerID и TypeID . Запросы итогов, описанные в следующих разделах, не будут работать без этих связей.
Как добавить эти данные в базу данных?
Чтобы добавить эти примеры таблиц в базу данных, можно скопировать данные в Excel, а затем импортировать их, но за некоторыми исключениями:
- При копировании таблиц "Типы событий " и "Клиенты" в Excel не копируйте
TypeIDстолбцы иCustomerID. Access добавляет значения первичного ключа при импорте листов, что экономит время. - После импорта таблиц необходимо открыть таблицу События в режиме конструктора и преобразовать столбцы Тип события и Клиент в поля подстановки. Для этого выберите столбец Тип данных для каждого поля, а затем выберите Мастер подстановки. В ходе создания полей подстановки Access заменяет текстовые значения столбцов "Тип мероприятия" и "Клиент" числовыми значениями из исходных таблиц. Дополнительные сведения о создании и использовании полей подстановки см. в статьях Создание и удаление многозначного поля. В этой статье объясняется, как создать тип поля подстановки, который позволяет выбрать несколько значений для заданного поля, а также как создавать списки подстановок.
Создание итогового запроса
На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
Дважды щелкните нужные таблицы. Таблицы появятся в верхней части конструктора запросов. При использовании приведенных выше примеров добавьте таблицы "Мероприятия" и "Типы мероприятий".
Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. На данном этапе к запросу следует добавить только поля категорий или групп и поле значений. При использовании данных из трех приведенных выше таблиц следует добавить либо поле "Тип мероприятия" из таблицы "Типы мероприятий", либо поле "Дата мероприятия" из таблицы "Мероприятия".
При необходимости вы можете указать условие для ограничения области запроса. Сортироваться будут только записи, удовлетворяющие этому условию, и в отсортированном списке будут определены первые и последние значения полей. Например, если вы хотите вернуть события в категории Частная функция, введите это выражение в строке Условия столбца Тип события :
<>"Private Function". Другие примеры выражений условий для запросов можно найти в статье Примеры условий запроса.Преобразуйте запрос в итоговый запрос, выполнив следующие действия. На вкладке Конструктор запроса в группе Показать и скрыть щелкните Итоги. В бланке запроса появится строка Итоги.
Убедитесь, что в строке Итоги поля каждой группы или категории выбран пункт Группировка по, и выберите для строки Итоги поля значения (поля с наибольшими или наименьшими значениями) функцию Max или Min.
Maxвозвращает наибольшее значение в числовом поле и самое последнее значение даты или времени вDate/Timeполе.Minвозвращает наименьшее значение в числовом поле и самое раннее значение даты или времени вDate/Timeполе.На вкладке Конструктор запросов в группе Настройка запросов щелкните стрелку вниз рядом с пунктом Все в списке Верхние значения . Затем введите количество записей, которое вы хотите просмотреть, или выберите параметр из списка. В этом случае для просмотра результатов в режиме таблицы выберите параметр Все и нажмите кнопку Выполнить.
Примечание
В зависимости от функции, выбранной на шаге 6, Access изменяет имя поля значения в запросе на MaxOfFieldName или MinOfFieldName. В нашем примере поле будут переименовано в Максимум_Дата мероприятия или Минимум_Дата мероприятия.
Сохраните запрос и переходите к следующим шагам.
Запрос не возвращает названия продуктов и другую информацию о них. Чтобы просмотреть дополнительные данные, необходимо создать второй запрос, который включает в себя запрос, который вы только что создали. Далее описано, как это сделать.
Создание второго запроса для отображения более подробных данных
- На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
- Перейдите на вкладку Запросы и дважды щелкните запрос итогов, созданный в предыдущем разделе.
- Откройте вкладку Таблицы и добавьте таблицы, которые вы использовали в итоговом запросе, а также таблицы, в которых содержатся дополнительные данные. Если вы использовали три таблицы из примера, добавьте в новый запрос таблицы "Типы мероприятий", "Мероприятия" и "Клиенты".
- Свяжите поля в итоговом запросе с соответствующими полями в родительских таблицах. Для этого перетащите каждое поле из итогового запроса на соответствующее поле в таблице. Если вы используете пример данных из трех таблиц, перетащите столбец Тип события в запросе итогов в поле Тип события в таблице Тип события . Затем перетащите столбец MaxOfEvent Date в запросе итогов в поле Дата события в таблице События . Эти соединения позволяют новому запросу на выборку объединить данные в запросе итогов и данные в других таблицах.
- Добавьте в запрос поля с дополнительной информацией из других таблиц. При использовании примеров данных из трех таблиц можно добавить поля "Компания" и "Контакт" из таблицы "Клиенты".
- При желании вы можете задать порядок сортировки по одному или нескольким столбцам. Например, для вывода категорий в алфавитном порядке задайте в строке Сортировка столбца Тип мероприятия значение По возрастанию.
- На вкладке Конструктор запросов в группе Результаты выберите Выполнить. Результаты запроса отображаются в режиме таблицы.
Совет.
Если вы не хотите, чтобы заголовок столбца Цена отображалось как MaxOfPrice или MinOfPrice, откройте запрос в режиме конструктора и в столбце Цена в сетке введите Price: MaxOfPrice или Price: MinOfPrice.
Цена отображается в виде заголовка столбца в режиме таблицы.
Одновременный поиск самых последних и самых давних дат
Запросы, созданные ранее в этой статье, возвращают либо наибольшие, либо наименьшие значения, но не оба набора сразу. Если вы хотите просмотреть оба набора значений в одном представлении, создайте два запроса: один из них получает верхние значения, а другой — нижние значения. Затем объедините результаты и сохраните их в одной таблице.
Поиск наибольших и наименьших значений и отображение этих данных в таблице состоит из следующих основных этапов:
Создайте запросы к верхним и нижним значениям. Если необходимо сгруппировать данные, создайте запросы итогов, использующие
Minфункции иMax.Преобразуйте запрос с верхними значениями или
Maxзапрос итогов в запрос на создание таблицы и создайте новую таблицу.Преобразуйте запрос с нижними значениями или
Minзапрос итогов в запрос на добавление и добавьте записи в таблицу с верхними значениями. Действия, описанные в этих разделах, объясняют, как это сделать:- Создайте запросы на поиск наибольших и наименьших значений. Шаги, необходимые для создания запроса на поиск наибольших или наименьших значения, описаны выше в разделе Поиск самой последней или самой давней даты. Если нужно сгруппировать записи по категориям, обратитесь к разделу Поиск самых последних или самых давних дат для записей в категории или группе. Если используются таблицы примеров из предыдущего раздела, используйте только данные из таблицы "Мероприятия". Используйте в обоих запросах поля "Тип мероприятия", "Клиент" и "Дата мероприятия" из таблицы "Мероприятия".
- Сохраните оба запроса, присвоив им описательные имена, например "Наибольшее значение" и "Наименьшее значение", и оставьте их открытыми для использования на следующих этапах.
Создание запроса на создание таблицы
- Запрос с верхними значениями открыт в режиме конструктора. На вкладке Конструктор запроса в группе Тип запроса щелкните Создать таблицу. Откроется диалоговое окно Создание таблицы.
- В поле Имя таблицы введите имя таблицы, которая будет хранить записи с наибольшими и наименьшими значениями. Например, введите
Top and Bottom Recordsи нажмите кнопку ОК. Каждый раз при выполнении запроса вместо отображения результатов в режиме таблицы запрос будет создавать таблицу и замещать значения текущими данными. - Сохраните и закройте запрос.
Создание запроса на добавление
- Запрос нижнего значения в режиме конструктора: на вкладке Конструктор запроса в группе Тип запроса нажмите кнопку Добавить.
- Откроется диалоговое окно Добавление.
- Введите то же имя, которое вы указали в диалоговом окне Создание таблицы.
Например, введите
Top and Bottom Recordsи нажмите кнопку ОК. При каждом выполнении запроса вместо отображения результатов в режиме таблицы запрос добавляет записи в таблицуTop and Bottom Records. - Сохраните и закройте запрос.
Выполнение запросов
- Теперь вы готовы выполнить два запроса. В области навигации дважды щелкните запрос с верхним значением, а затем выберите Да при появлении запроса Access. Затем дважды щелкните запрос нижнего значения и выберите Да при появлении запроса Access.
- Откройте таблицу Верхняя и Нижняя записи в режиме таблицы.
Важно
Если при попытке выполнения запроса на создание или добавление ничего не происходит, проверьте, не появляется ли в строке состояния Access следующее сообщение:
Данное действие или событие заблокировано в режиме отключения.
Если выводится это сообщение, сделайте следующее:
- Выберите Включить это содержимое, а затем нажмите кнопку ОК.
- Выполните запрос еще раз.