Настольная СУБД Access 2002

z2 крышка топливного бака



Глава 8. Редактирование и анализ данных с помощью запросов


    Глава 8. Редактирование и анализ данных с помощью запросов
    Глава 8. Редактирование и анализ данных с помощью запросов Создание таблиц с помощью запроса Преобразование запроса на выборку в запрос на изменение Выполнение запроса на изменение Создание запрос...
    Редактирование и анализ данных с помощью запросов
    В первой части книги мы познакомили вас с одним из важнейших объектов базы данных Access — запросами. При этом рассматривался только один вид запросов — запросы на выборку данных из таблиц. Micros...
    Использование фиксированных заголовков...
    Использование фиксированных заголовков столбцов в перекрестных запросах Для того чтобы в построенном запросе вывести столбцы в порядке календарной очередности месяцев, нужно задать фиксированные з...
    Рис. 8.16. Задание значения свойства Заголовки столбцов
    Рис. 8.16. Задание значения свойства Заголовки столбцов...
    Рис. 8.17. Перекрестный запрос на SQL
    Рис. 8.17. Перекрестный запрос на SQL Замечание В ANSI SQL слова TRANSFORM и PIVOT не являются зарезервированными словами, а ключевое слово IN интерпретируется по-разному в Access и ANSI SQL. Обра...
    Изменение уровня детализации в запросе
    Изменение уровня детализации в запросе Для создания удобного для анализа представления или наглядного графика можно сократить число строк и столбцов в результатах построенного запроса. С этой цель...
    Рис. 8.19. Запрос для вывода выручки...
    Рис. 8.19. Запрос для вывода выручки от продаж по типам товаров Рис. 8.20. Перекрестная таблица с итогами по кварталам...
    Статистические функции для выполнения расчетов
    Статистические функции для выполнения расчетов Статистические функции используются в запросах главным образом для вычисления всевозможных итоговых значений, например для числового поля таблицы мож...
    Вычисления на всем диапазоне записей
    Вычисления на всем диапазоне записей Первый запрос, который мы предлагаем построить, должен ответить на вопрос: Сколько заказов и на какую сумму разместил каждый клиент?. Для этого нам придется по...
    Рис. 8.21. Использование статистических функций в запросе
    Рис. 8.21. Использование статистических функций в запросе...
    Рис. 8.22. Запрос "Итоги по клиентам"
    Рис. 8.22. Запрос Итоги по клиентам...
    Вычисления на выделенных записях таблицы
    Вычисления на выделенных записях таблицы В предыдущем примере расчеты производились над всеми имеющимися в таблице Заказы (Orders) записями. Однако часто требуется выполнить вычисления над определ...
    Рис. 8.23. Задание условия отбора...
    Рис. 8.23. Задание условия отбора записей в запросе Итоги по клиентам...
    Рис. 8.24. Сообщение об ошибке...
    Рис. 8.24. Сообщение об ошибке при попытке отобразить поле, для которого задано условие Измените это предложение, заменив названия полей, в которых ведутся расчеты: AS [Число заказов] вместо AS [C...
    Рис. 8.25. Результаты запроса...
    Рис. 8.25. Результаты запроса Итоги по клиентам по городам Статистические функции можно использовать также в вычисляемых полях запроса. Например, для создания запроса Итоги по клиентам мы могли бы...
    Рис. 8.26. Использование статистической...
    Рис. 8.26. Использование статистической функции в выражении для вычисляемого поля Внимание Статистические функции при вычислениях не обрабатывают записи, которые имеют значение Null в том поле, к...
    Отбор повторяющихся записей и записей без подчиненных
    Отбор повторяющихся записей и записей без подчиненных Типичной операцией, которую приходится выполнять в базе данных, является поиск дубликатов, т. е. повторяющихся записей в таблице. Например, мо...
    Рис. 8.27. Диалоговое окно Мастера...
    Рис. 8.27. Диалоговое окно Мастера поиска повторяющихся записей...
    Рис. 8.28. Запрос, отбирающий...
    Рис. 8.28. Запрос, отбирающий повторяющиеся записи в таблице Клиенты Данный запрос имеет сложное выражение в строке Условие отбора (Criteria) поля Название: In (SELECT [Название] FROM [Клиенты] As...
    Сводные таблицы
    Сводные таблицы Сводная таблица — это еще одно удобное средство для анализа данных. Они позволяют превратить обычную таблицу или результирующее множество запроса, содержащее большое число записей...
    Создание сводной таблицы
    Создание сводной таблицы Для того чтобы продемонстрировать возможности сводных таблиц, рассмотрим запрос Счета (Invoices). Раскройте список запросов и откройте этот запрос в режиме Таблицы. Вы уви...
    Рис. 8.30. Макет сводной таблицы
    Рис. 8.30. Макет сводной таблицы...
    Рис. 8.31. Запрос "Счета" в режиме сводной таблицы
    Рис. 8.31. Запрос Счета в режиме сводной таблицы Чтобы определить столбцы таблицы, переместите поле Дата размещения по месяцам (OrderDate By Month) в область столбцов, которая имеет надпись Перета...
    Рис. 8.32. Панель инструментов Сводные таблицы
    Рис. 8.32. Панель инструментов Сводные таблицы Краткое описание кнопок этой панели приведено в табл. 8.2.[ Как и в других случаях, в таблице представлены те кнопки, которые используются только в д...
    Таблица 8.2. Описание кнопок панели...
    Таблица 8.2. Описание кнопок панели инструментов Сводные таблицы Кнопка Описание ...
    Рис. 8.33. Отображение итоговых...
    Рис. 8.33. Отображение итоговых данных в режиме сводной таблицы Определим итоговые столбцы. Щелкните левой кнопкой мыши на заголовке столбца Отпускная цена (ExtendedPrice) — все столбцы (кроме ито...
    Фильтрация данных сводной таблицы
    Фильтрация данных сводной таблицы Сейчас в таблице отображаются данные о продажах по всем товарам и всем странам. Она напоминает результат перекрестного запроса, но далее вы убедитесь, как легко м...
    Рис. 8.34. Выпадающий список значений поля
    Рис. 8.34. Выпадающий список значений поля Сбросьте флажок у первой строки — Все (Аll), щелкнув по нему мышкой. Все флажки одновременно сбросятся. Затем установите его только у одной страны, напри...
    Рис. 8.35. Диалоговое окно Свойства
    Рис. 8.35. Диалоговое окно Свойства Раскройте список значений поля Страна (Country). Теперь в списке значений полей отсутствуют флажки, и вы можете выбрать только одну страну (рис. 8.36)....
    Рис. 8.36. Окно значений поля...
    Рис. 8.36. Окно значений поля с отключением множественного выбора Теперь рассмотрим, как можно фильтровать отображаемые в таблице данные по значениям поля Марка (ProductName), которое находится в...
    Рис. 8.37. Сводная таблица с фильтром по товарам
    Рис. 8.37. Сводная таблица с фильтром по товарам Чтобы сбросить установленный фильтр, нажмите кнопку Автофильтр (AutoFilter) на панели инструментов. Обратите внимание, что при этом сбрасываются вс...
    Рис. 8.38. Товары — лидеры продаж
    Рис. 8.38. Товары — лидеры продаж По умолчанию в таблице отображаются все значения (All items). Выберите из списка в поле Отображать (Display the) значение первые (Тор). В поле Элементы (Items) мо...
    Изменение сводной таблицы
    Изменение сводной таблицы Сводные таблицы предназначены только для чтения, данные в них нельзя изменять. Но зато пользователь может очень легко трансформировать сводную таблицу: добавлять новые ст...
    Рис. 8.40. Отображение в сводной таблице данных по клиентам
    Рис. 8.40. Отображение в сводной таблице данных по клиентам Совет При переносе полей обращайте внимание на форму указателя мыши. Когда он попадает в одну из областей таблицы — строк, столбцов, дан...
    Создание таблиц с помощью запроса
    С помощью этого вида запроса вы можете выбрать нужные данные из уже существующей таблицы с помощью обычного запроса на выборку, а затем поместить их в новую таблицу, структура которой определяется...
    Сортировка в сводной таблице
    Сортировка в сводной таблице В отличие от аналогичных таблиц в перекрестном запросе, данные в сводной таблице легко сортировать. Для этого используются стандартные кнопки Сортировать по возрастани...
    Изменение уровня детализации
    Изменение уровня детализации Вернемся к первоначальному виду таблицы, когда в столбцах отображались данные по годам (см. рис. 8.33). Рассмотрим, как можно изменить уровень детализации данных, выво...
    Рис. 8.41. Детализация данных по столбцам сводной таблицы
    Рис. 8.41. Детализация данных по столбцам сводной таблицы Для того чтобы отображать или скрывать детальные данные, можно использовать также маленькие кнопочки с изображением знаков плюса и минуса,...
    Рис. 8.42. Итоговые данные по строкам сводной таблицы
    Рис. 8.42. Итоговые данные по строкам сводной таблицы Чтобы снова скрыть детальные данные, нажмите кнопку со знаком минус. Чтобы отобразить детальные данные сразу по всем строкам, выделите весь ст...
    Рис. 8.43. Добавление поля в область строк
    Рис. 8.43. Добавление поля в область строк Выделите поле Категория (CategoryName) и нажмите кнопку Свернуть (Collapse) на панели инструментов. Таблица быстро перестроится и будет иметь вид, привед...
    Общие и промежуточные итоги
    Общие и промежуточные итоги Как вы уже убедились, при создании сводной таблицы Microsoft Access автоматически добавляет общие итоги для строк и столбцов. Кроме того, если в области строки или стол...
    Рис. 8.45. Сводная таблица со скрытыми общими итогами
    Рис. 8.45. Сводная таблица со скрытыми общими итогами Например, если сводная таблица содержит данные с оценками по всем предметам учеников в школьном классе, то итоговым значением, скорее всего, б...
    Рис. 8.46. Сводная таблица с несколькими итоговыми полями
    Рис. 8.46. Сводная таблица с несколькими итоговыми полями...
    Рис. 8.47. Создание вычисляемого поля
    Рис. 8.47. Создание вычисляемого поля Для отображения итоговых значений могут быть использованы также вычисляемые поля. Для добавления вычисляемого поля нажмите кнопку Итоги и вычисляемые поля (Ca...
    Форматирование сводной таблицы
    Форматирование сводной таблицы Для форматирования полей сводной таблицы используется диалоговое окно Свойства (Properties). Можно задать формат для полей области фильтра, строк, столбцов и детальн...
    Рис. 8.48. Диалоговое окно Свойства...
    Рис. 8.48. Диалоговое окно Свойства сводной таблицы, вкладка Формат Из этого рисунка видно, что для поля можно определить: тип, размер и цвет используемого шрифта; способ выравнивания данных в пол...
    Рис. 8.49. Диалоговое окно Свойства...
    Рис. 8.49. Диалоговое окно Свойства сводной таблицы, вкладка Заголовки В поле Заголовок (Caption) можно задать текст, который будет отображен в названии поля, и затем формат этого текста....
    Сводные диаграммы
    Сводные диаграммы Для того чтобы отображение данных было еще более наглядным, можно использовать сводные диаграммы. Сводная диаграмма строится автоматически на основе уже созданной сводной таблицы...
    Создание сводной диаграммы
    Создание сводной диаграммы Создать диаграмму на основе сводной таблицы очень просто. Переключитесь в режим Сводная диаграмма (PivotChart View), и на экране появится сводная диаграмма, примерно так...
    Рис. 8.50. Сводная диаграмма...
    Рис. 8.50. Сводная диаграмма, построенная на основе сводной таблицы Но можно создать диаграмму и непосредственно на базе запроса или таблицы. При этом одновременно будет создаваться и сводная табл...
    Рис. 8.51. Макет сводной диаграммы
    Рис. 8.51. Макет сводной диаграммы Перетащите из списка полей в область фильтра поле Страна (Country), в область категорий — поля Фамилия (Last Name) и Имя (First Name), в область рядов — поле Дат...
    Рис. 8.52. Сводная диаграмма
    Рис. 8.52. Сводная диаграмма...
    Рис. 8.53. Сводная таблица "Продажи...
    Рис. 8.53. Сводная таблица Продажи по сотрудникам и странам Примечание Каи уже говорилось выше, одновременно со сводной диаграммой создается и сводная - таблица. Щелкните по стрелке на кнопке Вид...
    Изменение вида диаграммы
    Изменение вида диаграммы Так же, как и сводная таблица, сводная диаграмма является очень гибкой, и ее можно легко трансформировать, добавляя и удаляя поля, перемещая их из одной области в другую....
    Рис. 8.54. Диаграмма в виде графика
    Рис. 8.54. Диаграмма в виде графика Для того чтобы изменить тип диаграммы, щелкните мышью в любом месте области диаграммы и убедитесь, что эта область выделена. Тогда становится доступна кнопка Ти...
    Рис. 8.55. Изменение типа сводной диаграммы
    Рис. 8.55. Изменение типа сводной диаграммы Можно очень легко поменять местами поля рядов и категорий. Для этого достаточно нажать кнопку По строке или по столбцу (By Row/By Column) на панели инст...
    Форматирование элементов сводной диаграммы
    Форматирование элементов сводной диаграммы Форматирование элементов сводной диаграммы выполняется с помощью различных вкладок диалогового окна Свойства (Properties): для любых надписей на диаграмм...
    Настройка свойств полей в запросе и свойств самого запроса
    Настройка свойств полей в запросе и свойств самого запроса
    По умолчанию поля в запросе наследуют все свойства, которые определены для поля соответствующей таблицы. Однако можно изменить значение этих свойств, и эти изменения будут влиять только на поля в...
    Рис. 8.57. Диалоговое окно Свойства запроса
    Рис. 8.57. Диалоговое окно Свойства запроса Свойство Описание (Description) используется, чтобы задать текст, который будет выводиться в строке состояния при отображении результирующего множества...
    Создание запросов SQL
    Все запросы, которые мы рассматривали до сих пор, создавались либо с помощью мастера, либо с помощью Конструктора запросов. Конструктор запросов представляет собой графический инструмент для созда...
    Преобразование запроса на выборку в запрос на изменение
    Преобразование запроса на выборку в запрос на изменение Чтобы создать таблицу из результатов запроса, использовавшегося для отбора записей в связанных таблицах, его нужно преобразовать из запроса...
    Рис. 8.1. Диалоговое окно Создание таблицы
    Рис. 8.1. Диалоговое окно Создание таблицы...
    Рис. 8.2. Запрос на изменение в окне базы данных
    Рис. 8.2. Запрос на изменение в окне базы данных Нажмите стрелку на кнопке Вид (View) панели инструментов и выберите из списка элемент SQL View (Режим SQL). Вы увидите инструкцию SQL, которая гене...
    Сравнение ANSI и Jet SQL
    Сравнение ANSI и Jet SQL Язык Jet SQL почти соответствует стандарту ANSI SQL-89. В реализацию языка SQL для Microsoft Jet 4.x (используемого, начиная с версии Microsoft Access 2000) внесены нескол...
    Зарезервированные слова Jet SQL Приведенные здесь таблицы предназначены для сравнения зарезервированных слов ANSI SQL и Jet SQL. Ниже перечисляются зарезервированные слова Jet SQL, которые идентич...
    Таблица 8.3. Зарезервированные...
    Таблица 8.3. Зарезервированные слова ANSI SQL, не поддерживаемые Access SQL Зарезервированное слово Эквивалент в Access ...
    Функции и операторы Access, используемые...
    Функции и операторы Access, используемые вместо ключевых слов ANSI SQL Часть зарезервированных слов ANSI SQL заменяется в Jet SQL операторами или функциями. В табл. 8.4 перечислены функции и опера...
    Таблица 8.4. Функции и операторы...
    Таблица 8.4. Функции и операторы Access, используемые вместо зарезервированных слов ANSI SQL Access ANSI SQL ...
    Зарезервированные слова Jet SQL...
    Зарезервированные слова Jet SQL и функции, не входящие в ANSI SQL Существует несколько зарезервированных слов Jet SQL, которые не имеют аналогов в ANSI SQL. Это слова: DISTINCTROW — позволяет искл...
    Таблица 8.5. Дополнительные статистические...
    Таблица 8.5. Дополнительные статистические функции Access SQL Функция Access Назначение...
    Эквивалентные типы данных Jet SQL и ANSI SQL
    Эквивалентные типы данных Jet SQL и ANSI SQL В табл. 8.6 перечислены типы данных ANSI SQL, эквивалентные им типы данных языка Jet SQL и допустимые синонимы....
    Таблица 8.6. Эквивалентные типы данных Jet SQL и ANSI SOL
    Таблица 8.6. Эквивалентные типы данных Jet SQL и ANSI SOL Типы данных ANSI SQL ...
    Подстановочные знаки при сравнении строк
    Подстановочные знаки при сравнении строк В данном случае речь идет о трех подстановочных знаках, приведенных в табл. 8.7....
    Таблица 8.7. Соответствие подстановочных знаков
    Таблица 8.7. Соответствие подстановочных знаков Jet SQL ANSI SQL Зарезервированные слова DISTINCTROW и DISTINCT
    Зарезервированные слова DISTINCTROW и DISTINCT Зарезервированные слова DISTINCTROW и DISTINCT в инструкции SELECT Jet SQL позволяют исключить из результирующего множества повторяющиеся строки. Раз...
    Рис. 8.58. Инструкция SQL с модификатором DISTINCTROW
    Рис. 8.58. Инструкция SQL с модификатором DISTINCTROW В данном случае разницы между употреблением этих модификаторов нет (в других случаях может получиться иной результат). Однако при попытке реда...
    Использование разделителей и символов SQL
    Использование разделителей и символов SQL Разделителями в SQL также являются запятая, точка, точка с запятой и двоеточие. Описанные ниже разделители применяются с небольшими отличиями в ANSI SQL и...
    Использование инструкций SQL для создания запросов
    Использование инструкций SQL для создания запросов Есть несколько типов запросов, которые невозможно создать с помощью Конструктора запросов. Их можно создать, вводя соответствующую инструкцию в о...
    Создание запросов на объединение записей
    Создание запросов на объединение записей Этот тип запроса позволяет объединить в одном результирующем наборе результаты нескольких запросов, таблиц и инструкций SELECT. Синтаксис этого запроса сле...
    Рис. 8.59. Запрос на объединение
    Рис. 8.59. Запрос на объединение Рис. 8.60. Результирующее множество запроса Клиенты и поставщики по городам...
    Выполнение запроса на изменение
    Выполнение запроса на изменение Выполнение запроса на изменение приведет к появлению новой таблицы с запися--ми, которые будут идентичны результирующему набору записей того запроса на изменение, к...
    Рис. 8.3. Предупреждающее сообщение о числе новых записей
    Рис. 8.3. Предупреждающее сообщение о числе новых записей Нажмите кнопку Да (Yes). Поскольку запрос запускается впервые, будет создана новая таблица Список холодных клиентов. Щелкните по ярлыку Та...
    Рис. 8.4. Новая таблица в окне базы данных
    Рис. 8.4. Новая таблица в окне базы данных Дважды щелкните мышью по значку этой таблицы, чтобы ее открыть. Она содержит те же данные, что и запрос на создание таблицы в режиме Таблицы (рис. 8.5)....
    Рис. 8.5. Таблица, созданная при помощи запроса
    Рис. 8.5. Таблица, созданная при помощи запроса Запрос на создание таблицы может использоваться многократно для создания временных таблиц, при этом каждый раз будет создаваться новая таблица и уда...
    Создание запроса к серверу
    Создание запроса к серверу Еще одним типом запроса SQL является запрос к серверу. Он обрабатывается не процессором Jet, как все остальные запросы, а непосредственно передается на сервер базы данны...
    Управляющие запросы
    Управляющие запросы Управляющие запросы относятся к третьему типу запросов, которые создаются в окне Режим SQL и используют инструкции Jet SQL. Такие запросы позволяют работать с таблицами и индек...
    Создание подчиненных запросов
    Создание подчиненных запросов Инструкции Jet SQL, такие как SELECT, SELECT. .. INTO, INSERT...INTO, DELETE или UPDATE позволяют для вычисления предиката в предложении WHERE использовать другой зап...
    Выводы Запросы являются основой...
    Мы говорили о новых режимах, которые появились в Microsoft Access 2002 для просмотра содержимого таблиц и результатов запросов. И хотя сводные таблицы и диаграммы, получаемые в Access, немного отл...
    Создание запросов на доьавление данных
    Создание запросов на доьавление данных Поэтому сначала создается запрос на выборку данных, в результат которого включаются те поля, которые должны составлять добавляемые записи, а также поля, в ко...
    Рис. 8.6. Диалоговое окно Добавление
    Рис. 8.6. Диалоговое окно Добавление В бланке запроса на добавление появляется дополнительная строка Добавление (Append To), содержащая названия полей таблицы, в которую добавляются записи (рис. 8...
    Рис. 8.7. Бланк запроса на добавление
    Рис. 8.7. Бланк запроса на добавление Сохраните запрос в базе данных, присвоив ему имя Копирование заказов. Обратите внимание на значок в списке запросов, соответствующий запросу на добавление. Ка...
    Создание запросов на обновление таблиц
    Запросы на обновление используются главным образом для того, чтобы внести изменения сразу в большое количество записей с помощью одного запроса. Классическим примером такого запроса является измен...
    Рис. 8.8. Запрос на обновление записей
    Рис. 8.8. Запрос на обновление записей Совет Прежде чем выполнять запрос на обновление, щелкните левой кнопкой мыши по стрелке на кнопке Вид (View) и выберите Режим таблицы (Datasheet View). Вы ув...
    Рис. 8.9. Диалоговое окно Изменение связей
    Рис. 8.9. Диалоговое окно Изменение связей Для выполнения каскадного обновления: Откройте таблицы Сотрудники (копия) и Заказы (копия) и разместите их на экране так, чтобы были видны обе таблицы (р...
    Рис. 8.10. Каскадное обновление записей
    Рис. 8.10. Каскадное обновление записей...
    Создание запросов на удаления записей
    Создание запросов на удаления записей Запросы на удаление записей позволяют отобрать требуемые записи и удалить их за один прием. Принцип создания такого запроса аналогичен созданию запроса на обн...
    Рис. 8.11. Запрос на удаление...
    Рис. 8.11. Запрос на удаление записей из таблицы Заказы Нажмите кнопку Запуск (Run). Выводятся сообщение с приглашением подтвердить удаление записей и информация о количестве записей, которые буду...
    Рис. 8.12. Запрос на удаление
    Рис. 8.12. Запрос на удаление Замечание Для того чтобы выполнялась автоматическая проверка целостности данных, не забудьте для всех связей в базе данных установить флажок Обеспечение целостности д...
    Анализ данных с помощью запросов
    Еще одной функцией запросов Access является анализ данных, которые распределены по разным таблицам. Анализ данных может выполняться с помощью: перекрестных запросов, которые позволяют выполнить не...
    Перекрестные запросы
    Перекрестные запросы Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel....
    Рис. 8.13. Сообщение об ошибке в выражении вычисляемого поля
    Рис. 8.13. Сообщение об ошибке в выражении вычисляемого поля Выберите в ячейке Групповая операция (Total) того же столбца значение Выражение (Expression), а затем в ячейке Перекрестная таблица — з...
    Рис. 8.14. Перекрестный запрос в режиме Конструктора
    Рис. 8.14. Перекрестный запрос в режиме Конструктора Нажмите кнопку Запуск (Run). Появится результирующее множество записей, представленное на рис. 8.15....
    Рис. 8.15. Результирующее множество перекрестного запроса
    Рис. 8.15. Результирующее множество перекрестного запроса Стоит обратить внимание, что выведенная перекрестная таблица обладает одним недостатком: расположение столбцов определяется алфавитным пор...









Начало