Рис. 6.23. Результирующий бланк итогового запроса с группировкой
Текст итогового запроса с группировкой приведен в листинге 6.12.
Листинг 6.12.
Запрос на подсчет количества записей с группировкой
SELECT Count(Клиенты. [Номер (ID)]) AS [Count-Номер (ID)], Клиенты.
Рейтинг
FROM Клиенты
GROUP BY Клиенты. Рейтинг;
7. Перейдите на вкладку Конструктор и выберите команду Выполнить (рис. 6.24).
Рис. 6.24. Результат выполнения итогового запроса с группировкой
Вы можете видеть, что найдены две записи о клиентах с рейтингом 1000 и одна – с рейтингом 1500 (см. рис. 6.24).
Теперь составьте запрос так, чтобы выбиралась запись о товаре с максимальной ценой. Для этого выполните следующие действия.
1. На вкладке Создание в группе Другие нажмите кнопку Конструктор запросов .
2. В окне Добавление таблицы дважды щелкните кнопкой мыши на названии таблицы Товары . Нажмите кнопку Закрыть .
3. Дважды щелкните кнопкой мыши на названиях полей Код , Наименование , Описание , Цена , чтобы добавить поля в выборку.
4. В бланке запроса в строке Условие отбора для поля Цена введите условие In (SELECT MAX(Цена) FROM Товары). Такое условие означает, что сначала будет выполнен подзапрос, находящий максимальное значение цены на товар. Запись о товаре будет выведена только в том случае, если значение поля Цена совпадает с найденной максимальной ценой.
Результирующий бланк итогового запроса показан на рис. 6.25.
Рис. 6.25. Результирующий бланк итогового запросаТекст итогового запроса приведен в листинге 6.13.
Листинг 6.13.
Запрос на выборку записи с максимальным значением поля
SELECT Товары. Код, Товары. Наименование, Товары. Описание, Товары. Цена
FROM Товары
WHERE (((Товары. Цена) In (SELECT MAX(Цена) FROM Товары)));
5. Перейдите на вкладку Конструктор и выберите команду Выполнить . Результат запроса показан на рис. 6.26.
Рис. 6.26. Результат выполнения итогового запроса на поиск записи с максимальным значением поля
Вы видите, что выборка содержит одну запись о товаре, цена на который в таблице Товары является максимальной. Аналогично можно найти товар с минимальной ценой, используя функцию MIN .
Часто для анализа данных требуется выборка из нескольких таблиц. В каче стве примера выведите список клиентов с подсчетом общей суммы их заказов.
1. На вкладке Создание в группе Другие нажмите кнопку Конструктор запросов .
2. В окне Добавление таблицы дважды щелкните кнопкой мыши на названиях таблиц Клиенты и Заказы . Нажмите кнопку Закрыть .
3. Дважды щелкните кнопкой мыши на названиях полей Номер , Имя , Телефон , Адрес , Рейтинг таблицы Клиенты и поля Цена таблицы Заказы , чтобы добавить поля в выборку.
4. Теперь дважды щелкните кнопкой мыши на связи между таблицами. Откроется окно Параметры объединения (рис. 6.27).
Рис. 6.27. Окно Параметры объединения5. Установите переключатель в положение Объединение ВСЕХ записей из “Клиенты” и только тех записей из “Заказы”, в которых связанные поля совпадают и нажмите кнопку ОК .
6. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги . В бланке запроса появится строка Групповая операция .
7. В бланке запроса в строке Групповая операция для поля Сумма выберите из раскрывающегося списка функцию Sum . Для всех остальных полей должно быть выбрано значение Группировка .
Результирующий бланк итогового запроса с объединением показан на рис. 6.28.
Рис. 6.28. Результирующий бланк итогового запроса с объединениемТекст итогового запроса с объединением приведен в листинге 6.14.
Листинг 6.14.
Итоговый запрос с объединением таблиц
SELECT Клиенты. [Номер (ID)], Клиенты. Имя, Клиенты. Телефон, Клиенты.
Адрес, Клиенты. Рейтинг, Sum(Заказы. Сумма) AS [Sum-Сумма]
FROM Клиенты LEFT JOIN Заказы ON Клиенты. [Номер (ID)] = Заказы. Клиент
GROUP BY Клиенты. [Номер (ID)], Клиенты. Имя, Клиенты. Телефон, Клиенты.
Адрес, Клиенты. Рейтинг;
8. Перейдите на вкладку Конструктор и выберите команду Выполнить .
Результат выполнения итогового запроса с объединением таблиц показан на рис. 6.29.
Рис. 6.29. Результат выполнения итогового запроса с объединением таблиц
Удаление повторяющихся записей с помощью запросов
Существует еще одна полезная область применения запросов – удаление полностью или частично одинаковых записей. Если взять в качестве примера учебную базу Отдел продаж , то очевидно, что при корректной работе с таблицей Заказы в ней не должно быть записей с одинаковыми значениями полей Товар , Клиент и Дата , так как такие записи должны объединяться в одну с изменением значения поля Количество .
Сначала внесите такие ошибки в таблицу Заказы , просто создав дубликаты существующих записей.
1. Откройте базу данных Отдел продаж .
2. На вкладке Создание в группе Другие нажмите кнопку Конструктор запросов .
3. В окне Добавление таблицы дважды щелкните кнопкой мыши на названии таблицы Заказы . Нажмите кнопку Закрыть .
4. На вкладке Конструктор в группе Тип запроса выберите команду Добавление . Откроется окно Добавление .
5. В раскрывающемся списке выберите название результирующей таблицы – Заказы . Установите переключатель в положение В текущей базе данных . Нажмите кнопку ОК .
6. Дважды щелкните кнопкой мыши на названиях полей Дата , Товар , Количество , Сумма , Клиент , чтобы добавить поля в выборку.
7. В бланке запроса в строке Добавление для каждого поля выборки укажите поле назначения, совпадающее с исходным полем, выбирая названия полей из раскрывающегося списка.
Текст итогового запроса с объединением приведен в листинге 6.15.
Листинг 6.15.
Запрос на добавление
INSERT INTO Заказы (Дата, Товар, Количество, Сумма, Клиент)
SELECT Заказы. Дата, Заказы. Товар, Заказы. Количество, Заказы. Сумма,
Заказы. Клиент
FROM Заказы;
8. Перейдите на вкладку Конструктор и выберите команду Выполнить . Откроется окно подтверждения операции добавления.
9. Нажмите кнопку Да .
10. Откройте таблицу Заказы (рис. 6.30).
Рис. 6.30. Таблица Заказы после выполнения запроса на добавление
Теперь таблица Заказы содержит некорректные данные, которые необходимо исправить. Для этого используйте мастер запросов.
1. На вкладке Создание в группе Другие нажмите кнопку Мастер запросов . Откроется окно Новый запрос (рис. 6.31).
Рис. 6.31. Окно Новый запрос2. Теперь выберите пункт Повторяющиеся записи , а за тем нажмите кнопку ОК . Откроется окно Поиск повторяющихся записей (рис. 6.32). Рис. 6.32. В этом окне нужно указать таблицу для поиска повторяющихся записей
3. Укажите таблицу Заказы и нажмите кнопку Далее .
4. Выберите поля Дата , Товар , Клиент (рис. 6.33) и нажмите кнопку Готово .
Рис. 6.33. Здесь следует задать поля с повторяющимися значениямиБудет выведен список повторений по таблице Заказы (рис. 6.34). Рис. 6.34. Результат поиска повторяющихся записей
Если записи различаются значениями других полей, не вошедших в выборку, возможно, лучшим способом удаления будет удаление записей вручную, ведь только вы знаете критерий, по которому можно отделить корректные записи от ошибочных.
В данном случае записи действительно полностью одинаковые, и процесс их удаления хотелось бы автоматизировать запросом. Если просто преобразовать данный запрос на выборку в запрос на удаление, то удалятся все повторяющиеся записи. Обычно все же требуется оставить по одному экземпляру записей.
Чтобы получить желаемый результат, следуйте нижеприведенным действиям.
1. Добавьте в таблицу временное поле Сохранить логического типа. Это поле будет хранить значение 1 , если запись требуется оставить, и значение 0 – если удалить.
2. Оставьте все записи с минимальным значением поля Номер . Щелкните правой кнопкой мыши на заголовке вкладки запроса. Выберите пункт контекстного меню Режим SQL .
3. Скопируйте содержимое листинга 6.16 в запрос.Листинг 6.16.
Запрос на установку флага для последующего удаления дубликатов
UPDATE Заказы
SET Сохранить = 1
WHERE [Номер (ID)] IN
(SELECT Min([Номер (ID)]) FROM Заказы GROUP BY Заказы. Дата, Заказы.
Товар, Заказы. Клиент HAVING (((Count(Дата))>1) AND
((Count(Клиент))>1)))
Как видите, запрос является запросом на обновление, но в качестве условия используется упрощенный запрос на выборку, составленный мастером запросов. Запись помечается только в том случае, если ее номер совпадает с минимальным значением номера в ее группе дубликатов.
4. Перейдите на вкладку Конструктор и выберите команду Выполнить . Откроется окно подтверждения операции обновления.
5. Нажмите кнопку Да .