СУБД MS Access 2003 (960526), страница 19
Текст из файла (страница 19)
70. Пример конструирования перекрестного запроса:а - исходная таблица; б - результат выполнения перекрестного запросае^Открыть ^Конструктор Ыр Стада- ь|• Нонын запросЩТабп^ыЦ'\Щ1ФоррыОтчеты4i) Страницы3Макросы«§МодулиiСоздание зщ»са,выводящего данные вкомпактном формате,псдобни1 форматуэлектромнш таблицы.о VJSjilSi[Г?]|'ХПКонструкторПростой запросПовторяющиеся записиЗаписи без подчиненныхОКОтменаГрупгыi'*1 ИзбранноеРис. 71. Окно выбора режима создания запросаНа первом шаге запустившегося мастера перекрестного запроса (рис, 72) выбираем исходную таблицу для построения запросаПродажа Лето и нажимаем кнопку Далее.Тема 4. Запросы - в центре внимания рынок сбыта125Создание леренресгиых таблицВыбейте таблицу «пи запрос, полякоторые необходимо вывести вперекрестной мпросе.Таблица; Предлагаемые полепиТаблица: ПризнакиТаблица: ТтДля включения попей нзнескольких таблиц сначаласоздайте обычный запрос,содержащий все необходимые поля.Покаить(• 1а6лииь(Р Загчихы(" Т-^йгицы иРис.
72. Первый шаг мастера перекрестного запроса:выбрать источник данныхНа втором шаге мастера (рис. 73) выбираем поля для заголовков строк Модель и переносим их в правое окошко либо двойнымщелчком мыши, либо нажатием клавиши со знаком «больше» (>)в середине окна, после чего нажимаем кнопку Далее.Создание перекрестных таблицполя, значажя которыхбудут использованы Б качествемголоеков строг.Доступньи толя:Выбранные пола:Допускается выбор не бояте трехполей.Выберите поля по порядкусортировки данных.
Например,можно сначала выполнитессртнроБку эначешн по странам, аззтви по городам.ЗавНоиерОпченаI< НаиднДалее >Рис. 73. Второй шаг мастера перекрестного запроса:выбрать поле для заголовков строкПроектирование баз данных. СУБД MS AccessНа третьем шаге мастера (рис. 74) выбираем поле для заголовков столбцов Филиал. Следует заметить, что как на этом, таки на предыдущем шаге по мере выбора поля в нижней части окнапоявляются образцы соответствующих заголовков результирующей таблицы-запроса в абстрактной форме.Создание перекрестных таблицВыберитк поля для использованияих значений е качестве заголовковстолбцов,1Например, чтобы использовать имякаждого сотрудника в качествезаголовка столбца, выберите полеОтмена< На задРис.
74. Третий шаг мастера перекрестного запроса:выбрать поле для заголовков столбцовНа четвертом шаге мастера (рис. 75) выбираем поле, на основезначений которого будут производиться итоговые значения перекрестной таблицы ЦенаПродажи. Кроме того, на этом же шагеможно отказаться от включения в запрос итоговых результатоввыводимых для каждой строки запроса. По умолчанию мастервключит в список заголовков строк дополнительное поле с итогами, в котором будет вычисляться та же агрегатная функция, чтои в основном поле значений.На пятом шаге мастера (рис.
76) задаем имя запроса, а такжедальнейший режим работы с ним: либо просмотр таблицы-результата, либо продолжение конструирования в ручном режиме.127Тема 4. Запросы - в центре внимания рынок сбытаСоздание перекрестных таблиц<зкне вычисления необходимогроеести для каждой ячейки напересечении строк и столбцов?ДисперсияМаксимумМинимумОтклонениеПервойПоследнийСреднееHanpvmep, можно вычислить суммузаказов лля каждого сотрудника(столбец) по странам и регионам(строка).Вычислить итоговое значение длякаждой строки?I? да.ЧислииОтмена—'< Назад.—Вв.Далее >-J.Рис.
75. Четвертый шаг мастера перекрестного запроса:выбрать поле для вычисления перекрестных результатовСозданное рекрестных таблицЗалайте имя запроса.[Продажа Авто_перекрестньйУказаны есе сведения, необходимые для создания запроса с помощьюмастера,Дальнейшие действия после создания запроса:<" Просмотреть результаты запроса,* |Изненить структуру запроса.Г Вывести справку по работе с перекрестной таблицей.ГотовоРис. 76. Пятый (заключительный) шаг мастера перекрестногозапроса: задать имя запроса и определить дальнейшийрежим работы с нимПроектирование баз данных. СУБД MS Access128В случае выбора первого режима мы увидим таблицу с результатами сконструированного запроса (рис. 77).f — !^?<№ПГ- j|D !X|а? Продажа Двго_пвреирес1ный : перенресг.нми запрос , .МодельИтоговое значение ЦенаЛродажи [284 ЖО ,00р.244 000 ,Шр.80 QGO.OOp2100GO,OOp»ГАЗ-3510Москвич-214Нив а -2235ты И | ' | ГВладимир32 000 .ООрРязаньТула1132 ООС,ООр244 030, ООрй)ОСЮ,ООр210 000 ДОр,_ ^Рис.
77. Таблица-результат перекрестного запроса со столбцомитоговых значенийВ случае выбора второго режима мы перейдем в окно конструктора запроса в обычном режиме (рис. 78). Здесь можно заменить функцию столбца итогов, выводимых в каждой строке запроса. Для нашего примера необходимо заменить функцию Sumв столбце Итоговое значение строки Перекрестная таблицана функцию Count (количество), а также заменить имя Итоговоезначение ЦенаПродаж этого столбца в строке Поле на имя КоличПрод Авто. Результат этого запроса показан на рис. 70, б.к1 Продажа Авга_пс реирвстньш : перекрестный запрос'- ju!i[XJKMtMoAeite[витал|ДагаПродзжи 1ЗавНоивр<>Поле:l*w табтчы:Групповая операция;Перекрестная таЕ'Дта:Ссрп^оека:Условие от&»а:или:МодельЛсодана ДетоГомпиэовкаЗагоповсл СТРЖ- ФмпиалПрэдв*а ДетоГр'/пгисоЕкаЗаголовки столбшиемаПцодахиГЪодажэ ДБГОSumЗначитеИтоговое змэчениПродажа АвтоSumЗагоповкк строкРис.
78. Перекрестный запрос в обычном режиме конструктораОсобенно часто на практике бывает необходимо построениеперекрестной таблицы, отражающей какие-либо итоги по отрезкамвремени, например по месяцам, кварталам, годам и т. д. Для реали-Тема 4. Запросы - в центре внимания рынок сбыта129зации такого запроса можно использовать функцию Format с аргументом типа Дата/Время и построить на ее основе следующеевыражение:Выражение!:FormatdПродажа Авто]![ДатаПродажи];"т"). Здесь функция Формат имеет два аргумента,разделителем между которыми используется точка с запятой (;):1.
Выражение, значение которого преобразуется к нужному формату.2. Сам формат преобразования может быть стандартным (стандартные форматы Дата/Время можно посмотреть в конструкторе таблиц) или определяемым пользователем (Custom Formats). Последний задается в виде текстовой константы, состоящей из форматных символов.Например, чтобы получить перекрестную таблицу, отображающую результаты продаж каждой модели по месяцам, необходимо в качестве поля-заголовка столбца задать выражениеFormat([Продалса Авто]![ДатаПродажи];"тт").
Здесь пользовательский формат «mm» означает преобразование значения поляДатаПродажи типа Дата/Время в номер месяца (от 01 до 12).Результат запроса представлен на рис. 79.Рис. 79. Перекрестный запрос с группированием по месяцамв формате «mm»Если изменить формат на «mmm», то результат будет выглядеть как на рис. 80. При этом надо заметить, что столбикимесяцы упорядочиваются по алфавиту, а не по номеру. Чгобыупорядочить столбцы по номеру месяца, можно, например, добавить в выражение Заголовки столбцов этот номер, например: Выражение!: Formatd Продажа Авто]![ДатаПродажи];"т\ ") &Рогта^[ПродажаАвто]![ДатаПродажи];"ттт").В этом выражении формат «\ » с пробелом нужен для разделения номера месяца и его названия (рис, 63).130Проектирование баз данных. СУБД MS Access1ЕЩИ1и Пвренрсспю1Й_.0/ : перекрестный иптосМадель |ИНДЫГАЗ-35ЮМое IB ич-21 4Нивв-2235_»тоИтоговое значение ЦеиаПродажи234000,00?244000,00р.80000,00р2 SO OGG, ООрГ > IMJ. J m 4элр97000Д]рмар{120000,00р.фее[95000.00р.1240ШДЮр.aOQOOJDOpян в9200000р210 000 ООр- JlJ_jrРис.
80. Перекрестный запрос с группированием по месяцамв формате «mmm»Если этот вариант не подходит, то следует построить промежуточный запрос с упорядоченными месяцами, а на его основе построить перекрестный (рис. 81).]? ПерснрЕстинм_07 : перекрестный запросМодель|•ГАЗ-Э510Москвич-214Итоговое значение ЦенаПродэжи2840ШД)р244 000 .ООреООООЙОр21СОООД)рf- рЭр{|1 яне92000,00р2 феэ[Э5000Д}р.124000,00р.30 OOO.tHp3 марТ4 апр97 000,00р.120000,00р.21СОООД1р;™сь: 1*1 -1ГРис. 81.
Перекрестная таблица с упорядоченными столбцамиТаким образом, ширина перекрестной таблицы зависитот количества уникальных значений поля, выбранного в качествезаголовков столбцов, а также от длины каждого значения, поэтому, решая вопрос о том, какое поле выбрать для заголовков столбцов, надо учитывать этот факт и выбирать поле с меньшим количеством значений. Кроме того, если в перекрестной таблице требуется сгруппировать записи по нескольким полям (вложенныегруппы и подгруппы), то эти поля надо назначать как заголовкистрок.
СУБД Access позволяет иметь три уровня группирования.Например, перекрестная таблица, отражающая результаты продажпо кварталам для каждой модели и в каждом филиале, будет выглядеть как на рис. 82.ts* ПрпдалаАвто .псрсярсстный : перекрестный запросJLinlaidФилиал| Итоговое значение Цена |211ТВладимир92000,00р.92 000,00р.ВАЗ-21043Рязань192000,00р.95 000,00р.3? 000, ООрГАЗ-3510Рязань244 ООО.ООр244 000,00р.Moi:KBH4-2U РязаньКЗ ООО.ООр.80000,00р.Нива-2235Тула210 000,00р. 210 000, ООр_ ^ Н |.' | И1 5чсь ИЫГМодель1ДДД6ЖIРис.
82. Перекрестный запрос с двумя уровнями группированияпо строкам (моделям и филиалам) и поквартальнымгруппированием по столбцам131Тема 4. Запросы - в центре внимания рынок сбытаДля получения такого результата необходимо добавитьв конструкторе запроса поле Филиал на вторую позицию(см. рис. 77) и назначить его Заголовками столбцов, а чтобы изменить группирование по столбцам на поквартальное, используемформат «q».
В средней части рис. 67 показана область ввода дляВыражения! (3-столбец). Эта область может быть использованавместо построителя выражения для редактирования любого выражения. Чтобы открыть окно области ввода какого-либо поля, надоустановить на него указатель мыши, нажать правую кнопкуи из раскрывшегося контекстного меню выбрать пункт Масштаб.Пнчем»I -,>"Пете itoi^I4VT«6*UC.Гр*н.S^ii.- _ г п ,-,Lbii».B«.L-Fg.allin»H«ie.)l'reKin!KH.Hl-a'iнП».тяitouvaAerbЦЯ' SSSSSSSTГЬонаяа4й1п*'ГЛЛВОПЦЖЦИС»ТЧ»«Ггчв™кг*«ч—Рис.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
