Как установить sql для вертикаль

Улучшение наглядности сводных данных с помощью перекрестного запроса

Если вы хотите сделать сводные данные в Access более простыми для понимания, используйте перекрестный запрос. Он вычисляет сумму, среднее или результаты другой агрегатной функции, а затем группирует их с использованием двух наборов значений — сбоку и сверху таблицы. На ленте откройте вкладку Создание, а затем в группе Запросы нажмите кнопку Мастер запросов. В диалоговом окне Новый запрос дважды щелкните Перекрестный запрос.

Если в одном из наборов заголовков содержатся значения дат, мастер позволяет сгруппировать записи по стандартным интервалам, например по месяцам или кварталам.

В этой статье

• Обзор
• Создание перекрестного запроса с помощью мастера
• Создание перекрестного запроса в конструкторе
• Создание перекрестного запроса в режиме SQL
• Использование диапазонов и интервалов в качестве заголовков
• Ограничение допустимых заголовков строк с помощью параметра
• Замена пустых значений нулями
• Советы по использованию перекрестных запросов

Примечание: Перекрестные запросы недоступны в веб-приложениях Access, а в веб-базах данных поддерживаются только частично: их не могут использовать веб-объекты.

Общие сведения

Перекрестный запрос — это разновидность запроса на выборку. При выполнении перекрестного запроса результаты отображаются в таблице, структура которой отличается от других типов таблиц.

Результаты перекрестных запросов нагляднее, чем у обычных запросов на выборку с теми же данными (см. рисунок ниже).

1. Этот запрос на выборку группирует сводные данные вертикально по сотрудникам и категориям.

2. С помощью перекрестного запроса можно отобразить те же данные, но они группируются как по горизонтали, так и по вертикали. Это позволяет сделать таблицу более компактной и наглядной.

При создании перекрестного запроса необходимо указать, какие поля содержат заголовки строк, какие — заголовки столбцов, а какие — значения, по которым вычисляются сводные данные. При задании заголовков столбцов и суммируемых значений можно использовать только одно поле. При задании заголовков строк можно использовать до трех полей.

Для определения суммируемых значений и заголовков строк и столбцов можно также использовать выражения. Дополнительные сведения см. в статье Агрегатные функции SQL.

1. Эта часть содержит один, два или три столбца с заголовками строк. Имена полей, используемых в качестве заголовков строк, показаны в верхней строке этих столбцов.

2. Здесь показаны заголовки строк. Число строк в перекрестной таблице может быстро возрасти, если использовать более одного поля заголовков строк, поскольку отображаются все комбинации заголовков строк.

3. В столбцах с этой стороны содержатся заголовки столбцов и сводные значения. Обратите внимание на то, что имя поля заголовков столбцов не отображается в таблице.

4. Здесь показаны сводные значения.

Способы создания перекрестных запросов

С помощью мастера перекрестных запросов. Использование мастера перекрестных запросов — обычно наиболее простой и быстрый способ создать такой запрос. Большая часть работы при этом выполняется автоматически, однако некоторые возможности недоступны.

У мастера есть три преимущества:

• Простота использования. Для создания перекрестного запроса требуется лишь запустить мастер, а затем ответить на ряд вопросов, следуя инструкциям.
• Возможность автоматически группировать даты по интервалам. Если есть поле со значениями дат или времени, которое вы используете для заголовков столбцов, с помощью мастера можно сгруппировать даты по интервалам, таким как месяцы или кварталы.

Совет: Если вы хотите использовать значения из такого поля в качестве заголовков столбцов, но при этом нужно сгруппировать даты по интервалам, недоступным в мастере (например, по финансовым годам или двухлетним периодам), не используйте его для создания запроса. Вместо этого создайте перекрестный запрос в конструкторе и задайте интервалы с помощью выражения.

Тем не менее с помощью мастера невозможно сделать следующее:

• использовать больше одной таблицы или одного запроса в качестве источника записей;
• использовать для создания полей выражение;
• добавить запрос на ввод параметров;
• задать список фиксированных значений, которые будут использоваться в качестве заголовков столбцов. На последнем этапе в мастере можно перейти в конструктор и настроить запрос более точно. Благодаря этому вы можете добавить элементы, недоступные в мастере, например дополнительные источники записей.

С помощью конструктора. В конструкторе можно более точно настроить структуру запроса. В этом режиме доступны возможности, отсутствующие в мастере.

Создавать перекрестный запрос в конструкторе рекомендуется в следующих случаях:

• У вас есть больше контроля над процессом. Мастер принимает некоторые решения за вас.
• в качестве источника записей необходимо использовать более одной таблицы или одного запроса;
• требуется запрашивать ввод параметров;
• в качестве полей запроса нужно использовать выражения;
• необходимо задать набор фиксированных значений, которые будут использоваться в качестве заголовков столбцов;
• вы хотите попрактиковаться в использовании бланка запроса.

Написание запроса SQL представлении При этом перекрестный запрос можно написать SQL представлении. Однако в представлении «Параметры» невозможно указать SQL данных. Если вы хотите использовать параметр в перекрестных запросах, необходимо указать тип данных параметра, внося изменения в запрос в конструкторе.

Совет: Помните, что при создании перекрестных запросов необязательно ограничиваться одним способом. Например, всегда можно создать запрос с помощью мастера, а затем изменить его структуру в конструкторе.

Создание перекрестного запроса с помощью мастера

Для использования мастера необходимо использовать одну таблицу или один запрос в качестве источника записей для перекрестного запроса. Если данные, которые вы хотите включить, содержатся в нескольких таблицах, сначала создайте запрос на выборку, возвращающий их. Дополнительные сведения о создании запросов на выборки см. в разделе «См. также».

В этом примере мы создадим перекрестный запрос, использующий таблицу «Товары» из примера базы данных «Борей». Мы хотим отобразить количество товаров в каждой категории для всех поставщиков.

1. На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов.
2. В диалоговом окне Новый запрос выберите пункт Перекрестный запроси нажмите кнопку ОК. Будет запущен мастер создания перекрестных запросов.
3. На первой странице мастера выберите таблицу или запрос, которые вы хотите использовать для создания перекрестного запроса. В данном примере выберите таблицу Товары, а затем нажмите кнопку Далее.
4. На следующей странице выберите поле, содержащее значения, которые требуется использовать в качестве заголовков строк. В качестве источников заголовков строк можно выбрать до трех полей, однако чем меньше используется заголовков строк, тем легче будет изучать перекрестную таблицу. Если для формирования заголовков строк используется несколько полей, то порядок их выбора определяет, как будут по умолчанию сортироваться результаты. В данном примере выберите ИДы поставщиков.Value и нажмите кнопку с символом >. Обратите внимание на то, что Access отображает имя поля в левой части окна предварительного просмотра запроса внизу диалогового окна. Нажмите кнопку Далее, чтобы продолжить.
5. На следующей странице выберите поле, содержащее значения, которые требуется использовать в качестве заголовков столбцов. Обычно следует выбирать поле, содержащее меньше значений, чтобы результаты было удобно читать. Например, использовать поле с небольшим количеством возможных значениями (такое как пол) обычно предпочтительнее, чем поле, которое может содержать множество различных значений (такое как возраст). Если поле, выбранное для создания заголовков столбцов, содержит данные типа «Дата/время», мастер выполнит дополнительное действие, предложив указать способ группировки дат по интервалам, например по месяцам или кварталам. В этом примере выберите категорию и обратите внимание на то, что в верхней части окна предварительного просмотра запроса в нижней части диалоговых окна отображаются имена категорий. Нажмите кнопку Далее, чтобы продолжить.
6. Если для заголовков столбцов выбрано поле типа «Дата/время», на следующей странице мастера будет предложено задать интервал для группировки дат. Возможные варианты: Год, Квартал, Месяц, Дата и Дата/время. Если поле, выбранное для формирования заголовков столбцов, не относится к типу «Дата/время», эта страница мастера пропускается.
7. На следующей странице выберите поле и функцию для расчета сводных значений. Набор доступных функций зависит от типа данных поля. На той же странице установите или снимите флажок Да, чтобы соответственно включить в запрос или исключить из него итоговые значения по строкам. Если итоги строк включены, в перекрестный запрос добавляется заголовок строки, в котором используются те же поле и функция, что и для значения поля. При включении итога по строке вставляется дополнительный столбец, содержащий сводные данные по остальным столбцам. Например, если перекрестный запрос вычисляет средний возраст по расположению и полу (с использованием заголовков столбцов, определяемых полом), в дополнительном столбце будет рассчитываться средний возраст по расположению независимо от пола. В данном примере выберите ИД в области Поля и Число в области Функции, чтобы подсчитать количество товаров для каждого сочетания поставщика и категории. Не снимайте флажок Да. Access создаст столбец, в котором суммируется число товаров каждого поставщика. Нажмите кнопку Далее, чтобы продолжить.
8. На последней странице мастера введите имя запроса и укажите, требуется ли просмотреть результаты или изменить структуру запроса. Функцию, используемую для получения итогов по строкам, можно изменить, отредактировав перекрестный запрос в режиме конструктора.
9. Если вы использовали таблицу «Товары» из базы данных «Борей», перекрестный запрос отобразит список поставщиков в виде строк, названия категорий продуктов в виде столбцов, а количество товаров на их пересечении.

Создание перекрестного запроса в конструкторе

В конструкторе для создания перекрестного запроса можно использовать сколько угодно источников записей (таблиц и запросов). Однако вы можете сделать ее простой, создав запрос на выборку, возвращающий все нужные данные, а затем используя его в качестве только источника записей для перекрестного запроса. Дополнительные сведения о создании запросов на выборки см. в разделе «См. также».

При построении перекрестного запроса в режиме конструктора используются строки бланка запроса Всего и Перекрестная таблица, в которых указывается, какие значения поля станут заголовками столбцов, какие — заголовками строк, а какие будут использоваться для вычисления суммы, среднего, количества или других расчетов.

1. Значения в этих строках определяют, является ли поле заголовком строки, заголовком столбца или сводным значением.

2. Этот параметр задает отображение значений поля в виде заголовков строк.

3. Этот параметр задает отображение значений поля в виде заголовков столбцов.

4. Эти параметры задают получение сводных значений.

Создание запроса

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.
2. В диалоговом окне Добавление таблицы дважды щелкните каждую таблицу или запрос, которые необходимо использовать в качестве источников записей. Если используется несколько источников записей, убедитесь, что таблицы или запросы соединены по общим полям. Дополнительные сведения о присоединении к таблицам и запросам см. в разделе «См. также».
3. Закройте диалоговое окно Добавление таблицы.
4. На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица.
5. В окне конструктора запроса дважды щелкните каждое поле, которое необходимо использовать как источник заголовков строк. Можно выбрать до трех полей для создания заголовков строк.
6. В бланке запроса в строке Перекрестная таблица для каждого поля заголовков строк выберите значение Заголовки строк. Можно ввести выражение в строке Условие отбора, чтобы ограничить число результатов для этого поля. Можно также использовать строку Сортировка, чтобы указать порядок сортировки для поля.
7. В окне конструктора запросов дважды щелкните поле, которое необходимо использовать в качестве источника заголовков столбцов. Для этого можно выбрать только одно поле.
8. В бланке запроса в строке Перекрестная таблица для поля заголовков столбцов выберите значение Заголовки столбцов. Можно ввести выражение в строке Условие отбора, чтобы ограничить число результатов для поля заголовков столбцов. Однако использование условного выражения для поля заголовков столбцов не ограничивает число столбцов, возвращаемых перекрестным запросом. Ограничения касаются того, какие столбцы могут содержать данные. Предположим, например, что имеется поле заголовков столбцов с тремя возможными значениями: красный, зеленый и синий. Если к полю заголовков столбцов применить условие =’синий’, в перекрестной таблице останутся столбцы «Красный» и «Зеленый», но только столбец «Синий» будет содержать данные. Чтобы ограничить набор значений, отображаемых в качестве заголовков столбцов, можно задать список фиксированных значений с помощью свойства запроса Заголовки столбцов. Дополнительные сведения см. в следующем разделе.
9. В окне конструктора запроса дважды щелкните поле, которое необходимо использовать для расчета сводных значений. Для расчета сводных значений можно выбрать только одно поле.
10. В бланке запроса в строке Всего для поля сводных значений выберите статистическую функцию, с помощью которой следует вычислять значения.
11. В строке Перекрестная таблица для поля сводных значений выберите элемент Значение. Задавать условия отбора или выполнять сортировку по полю сводных значений нельзя.
12. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Задание фиксированных значений для заголовков столбцов

Чтобы задать фиксированные значения для использования в заголовках столбцов, можно воспользоваться свойством запроса Заголовки столбцов.

1. Откройте перекрестный запрос в режиме конструктора.
2. Если окно свойств не открыто, откройте его, нажав клавишу F4.
3. Убедитесь, что в окне свойств над вкладкой Общие указано Возможен выбор:Свойства запроса. В противном случае щелкните в пустом месте над бланком запроса.
4. В окне свойств на вкладке Общие в строке свойства Заголовки столбцов введите через запятую значения, которые следует использовать в качестве заголовков столбцов. В заголовках столбцов запрещается использовать некоторые символы (например, большинство знаков препинания). Если такие символы будут вводиться в списке значений, каждый из них будет автоматически заменяться символом подчеркивания (_).