Создание запроса в режиме конструктора
Пользоваться сортировкой и фильтрацией очень удобно, но при работе с несколькими таблицами их возможностей недостаточно. В таких случаях применяются запросы. Самый распространенный тип запросов отображает записи, удовлетворяющие определенным условиям, но существуют и другие типы, приведенные ниже.
Запрос на выборку извлекает данные из одной или нескольких таблиц и представляет их в табличном виде. Этот тип запроса можно использовать для группировки записей, вычисления сумм, средних величин и других итоговых значений. Работая с результатами запроса, можно одновременно редактировать данные из нескольких таблиц.
Параметрический запрос запрашивает ввод параметров (например, начальную и конечную дату). Этот тип запросов часто используется для получения отчетов за определенный период времени.
Перекрестный запрос выполняет расчеты и группирует данные для анализа информации. Для элементов, расположенных в левом столбце и в верхней строке результатов запроса, могут вычисляться итоговые значения (сумма, количество или средняя величина). Ячейки на пересечении строк и столбцов также содержат вычисляемые значения.
Запрос на действие вносит множественные изменения за одну операцию. Собственно, это запрос на выборку, который выполняет определенные действия над результатами отбора. Возможны четыре типа действий: обновление, удаление и добавление записей и создание таблицы. В двух последних случаях результаты запроса на выборку либо добавляются в существующую таблицу, либо для них создается новая таблица.
Совет. Access включает также запросы SQL, но в этом курсе они не рассматриваются.
Фильтры, сортировка и запросы
Основные различия между применением фильтрации и сортировки и использованием запросов состоят в следующем:
фильтрация и сортировка выполняются быстрее, чем запросы.;
фильтры и условия сортировки хранятся временно. Запрос можно сохранить на диске и использовать в дальнейшем;
фильтры и сортировка применяются только к активной таблице или форме. Запрос может базироваться на нескольких таблицах и других запросах, причем необязательно, чтобы они были открыты.
Запрос можно создать вручную или с помощью мастера. При этом создается выражение, описывающее критерий, которому должны соответствовать данные в указанных таблицах. В результате выполнения запроса записи, отвечающие заданным условиям, отобразятся в табличном виде.
GardenCo
В этом упражнении вы создадите форму для ввода заказов, полученных по телефону. Форма базируется на запросе, содержащем сведения из таблиц Сведения о заказе и Товары. Запрос создает таблицу, в которой перечислены все товары с указанием их цен, количества, скидок и стоимости покупки. Поскольку стоимость не хранится в базе данных, ее нужно вычислить прямо в запросе. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\QueryDes. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries).
Щелкните дважды на команде Воспользуйтесь диалоговым окном Добавление таблицы (Show Table), чтобы указать таблицы и запросы, которые нужно включить в данный запрос.
На активной вкладке Таблицы (Tables) щелкните дважды на таблицах Сведения о заказе и Товары, чтобы добавить их в окно запроса, и закройте диалоговое окно Добавление таблицы (Show Table).В верхней части окна запроса появятся изображения добавленных таблиц, содержащие списки полей, как показано на следующем рисунке.
Вверху каждого списка полей имеется звездочка, представляющая все поля таблицы. Ключевое поле отображается полужирным шрифтом. Линия, соединяющая поля КодТовара в обеих таблицах, указывает, что эти поля связаны.Совет. Чтобы добавить в запрос дополнительные таблицы, откройте диалоговое окно Добавление таблицы (Show Table). Для этого щелкните правой кнопкой мыши в верхней части окна запроса и воспользуйтесь командой Добавить таблицу (Show Table) в контекстном меню или щелкните на кнопке Отобразить таблицу (Show Table) на панели инструментов.
Нижняя часть окна запроса занята бланком, предназначенным для построения условий отбора.
Чтобы включить поля в запрос, нужно перетащить их из списков вверху окна в последовательные столбцы бланка запроса. Перетащите следующие поля:| Из таблицы | Поле |
|---|
| Сведения о заказе |
КодЗаказа |
| Товары |
ОписаниеТовара |
| Сведения о заказе |
Цена |
| Сведения о заказе |
Количество |
| Сведения о заказе |
Скидка |
Совет. Щелкнув дважды на поле, можно скопировать его в свободный столбец бланка. Чтобы скопировать сразу все поля таблицы, выделите нужный список (щелкнув дважды на его заголовке), а затем перетащите выделенный объект на бланк запроса. Когда вы отпустите кнопку мыши, все поля разместятся в последовательных столбцах бланка. Можно включить все поля таблицы в один столбец бланка, перетащив в него звездочку. Однако если требуется задать условия сортировки или отбора для определенных полей, нужно перетащить каждое из них в отдельный столбец.
Окно запроса должно выглядеть, как показано на следующем рисунке.
Щелкните на кнопке , чтобы выполнить запрос и отобразить результаты в виде таблицы.Измените запрос таким образом, чтобы упорядочить результаты по полю КодЗаказа, и добавьте поле для вычисления стоимости товара, которая определяется умножением цены на количество за вычетом скидки.
Щелкните на кнопке , чтобы вернуться в режим конструктора. Строка Сортировка (Sort) (третья на бланке) позволяет указать поле и принцип сортировки (по возрастанию или убыванию).
Щелкните в ячейке Сортировка (Sort) в столбце КодЗаказа, щелкните на стрелке и щелкните на По возрастанию (Ascending). Поскольку ни одна из таблиц не содержит стоимость покупки, воспользуйтесь построителем выражений, чтобы вставить в бланк запроса выражение для расчета стоимости.
Щелкните правой кнопкой мыши в ячейке Нужно построить следующее выражение: "Ccur([Сведения о заказе].[Цена]*[Количество]*(1-[Скидка])/100)*100"
Функция Ccur, используемая в выражении, преобразует результаты вычислений в денежный формат.
В первом столбце области элементов щелкните дважды на папке Функции (Functions), а затем щелкните на Встроенные функции (Build-In Functions). Во втором столбце отобразятся категории встроенных функций.
Во втором столбце щелкните на категории Преобразование (Conversion), чтобы ограничить список функций в третьем столбце этой категорией. Щелкните дважды на функции Ccur в третьем столбце.Построитель выражений
Выражения, используемые в фильтрах или запросах, обычно вводятся вручную или создаются с помощью функции Построитель выражений (Expression Builder). Чтобы открыть окно построителя, можно воспользоваться командой Построить (Build) в контекстном меню или щелкнуть на кнопке построителя : в конце поля, куда нужно ввести выражение.
Построитель выражений не является мастером, то есть, не проводит пользователя через процедуру построения выражения. Тем не менее он оказывает существенную помощь, предоставляя иерархические списки, содержащие большинство элементов (функций, операторов, полей), которые можно включить в выражение. Выбрав нужный элемент, можно скопировать его в поле выражения, а затем щелкнуть на кнопке ОК, чтобы поместить выражение в фильтр или запрос.
Функция преобразования в денежный формат вставлена в поле выражения. Вместо заполнителя <<expr>>, заключенного в скобки, нужно вставить выражение, вычисляющее число, которое будет преобразовано в денежный формат.
Щелкните на <<expr>>, чтобы выделить его. Элемент, который вы вставите следующим, заменит выделенный фрагмент.
Следующий элемент, который нужно вставить в выражение, - это поле В результате последнего действия курсор оказался в конце выражения, после поля Цена, что и требуется.
Теперь нужно умножить значение поля Цена на значение поля Количество. Щелкните на кнопке * (звездочка) в ряду операторов, расположенном под полем выражения. В выражении появится знак умножения и очередной заполнитель <<Выражение>> (<<expr>>).
Щелкните на заполнителе <<Выражение>> (<<expr>>), чтобы выделить его, и вставьте поле Количество, щелкнув дважды на нем во втором столбце. Введенное выражение вычисляет стоимость товара, умножая цену на количество. Но, чтобы получить окончательный результат, необходимо вычесть скидки, предлагаемые на отдельные товары. Скидки указаны в поле Скидки и составляют 10-20% от стоимости товара. Проще рассчитать процент, который нужно заплатить (80-90% от стоимости), чем вычислять скидку, а затем вычитать ее из стоимости товара.
Введите Хотя поле Скидка определено как процентное, в базе данных оно хранится в виде чисел от 0 до 1 (то есть, на экране отображается 10%, а в памяти хранится 0,1). Поэтому, если скидка составляет 10%, результат выражения (1- Скидка) равняется 0,9. Иначе говоря, при 10% скидке стоимость составит 0,9 от произведения цены на количество.
Щелкните на кнопке ОК. Окно построителя выражений закроется, и выражение будет скопировано в ячейку бланка запроса.
Нажмите на клавишу (Enter), чтобы завершить ввод выражения.Совет. Чтобы быстро подогнать ширину столбца под его содержимое, щелкните дважды на правой границе серой полосы вверху столбца.
Access присвоил выражению имя Выражение1 (Expr1). Щелкните на нем дважды, чтобы выделить, и введите более содержательное имя Окончательная цена.
Щелкните на кнопке Заказы теперь отсортированы по полю КодЗаказа, а в последнем столбце указана вычисленная стоимость товара.
Прокрутите таблицу вниз, чтобы просмотреть несколько записей со скидками. Как видите, стоимость рассчитана правильно.
Закройте окно запроса и щелкните на кнопке Да (Yes) в ответ на предложение сохранить изменения. Введите Сведения о заказе (окончательная цена) в качестве имени запроса и щелкните на кнопке ОК.
Закройте базу данных.
Создание запроса с помощью мастера
Процесс создания простого запроса с помощью мастера Создание запросов (Query Wizard) практически не отличается от создания формы с помощью мастера. Суть его заключается в выборе полей из существующих таблиц и запросов и добавлении их в новый запрос.
Для эффективной работы с несколькими таблицами необходимо установить между ними отношения. Поэтому, прежде чем запускать мастер запросов, откройте окно Схема данных (Relationships) и свяжите совпадающие поля в нужных таблицах.
GardenCo
В этом упражнении вы воспользуетесь мастером, чтобы создать запрос, извлекающий сведения о заказах из таблиц Клиенты и Заказы. Записи этих таблиц связаны через поле КодКлиента. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\QueryWiz. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries), а затем щелкните дважды на команде Создание запроса с помощью мастера (Create query by using wizard). Откроется первая страница мастера Создание простых запросов (Simple Query Wizard).Совет. Можно также запустить мастер, щелкнув на команде Запросы (Queries) в меню Вставка (Insert) или щелкнув на кнопке Новый объект (New Object), а затем щелкнув дважды на Мастер простых запросов (Simple Query Wizard).
В списке Таблицы и запросы (Tables/Queries) выделите Таблица: Заказы (Tables: Orders).
Щелкните на кнопке >>, чтобы переместить все доступные поля в список Выбранные поля (Selected Fields).
В списке Таблицы и запросы (Tables/Queries) выделите Таблица: Клиенты (Tables: Customers).
Щелкните дважды на полях Адрес, Город, Штат, ПочтовыйИндекс и Страна, чтобы переместить их в список Выбранные поля (Selected Fields), а затем щелкните на кнопке Далее (Next).Совет. Если взаимосвязь между таблицами не установлена, будет предложено установить связь, а потом снова запустить мастер.
Щелкните на кнопке Далее (Next), чтобы принять подробный вариант, заданный по умолчанию.
Введите имя запроса Запрос на заказы, оставьте выделенным вариант Открыть запрос для просмотра данных (Open Query to view information) и щелкните на кнопке Готово (Finish).Access выполнит запрос и отобразит результаты в виде таблицы. Прокрутите записи, чтобы убедиться, что отображаются сведения обо всех заказах.
Щелкните на кнопке , чтобы переключиться в режим конструктора. Обратите внимание, что для всех полей выделены флажки в ячейках Вывод на экран (Show). Очистив флажок, можно отменить отображение поля, которое включено в запрос для сортировки или создания условия отбора, но не требуется при просмотре.
Очистите флажки Вывод на экран (Show) для полей КодЗаказа, КодКлиента и КодСотрудника, а затем щелкните на кнопке Вид (View), чтобы переключиться в режим таблицы. Как видите, все три поля исключены из результатов запроса.
Щелкните на кнопке Вид (View), чтобы вернуться в режим конструктора. Этот запрос извлекает все записи из таблицы Заказы. Можно ограничить просмотр заказами, сделанными в определенный период, преобразовав запрос в параметрический, который запрашивает диапазон дат при запуске.
В столбце ДатаРазмещения щелкните в ячейке Условие отбора (Criteria) и введите Between [Введите начальную дату:] And [Введите конечную дату:].
Щелкните на кнопке , чтобы выполнить запрос. Access отобразит следующее диалоговое окно.
Введите 1/1/01 и нажмите на клавишу (Enter).
Во втором диалоговом окне Введите значение параметра (Enter Parameter Value) введите 1/31/01 и нажмите на клавишу (Enter). Появятся результаты запроса, содержащие только те заказы, которые были сделаны в указанный период.
Закройте таблицу и щелкните на кнопке Да (Yes), чтобы сохранить запрос.
Закройте базу данных.
Вычисления в запросе
Обычно запросы используются для поиска информации, удовлетворяющей заданным условиям. В некоторых ситуациях, однако, пользователя интересуют не столько конкретные данные, сколько итоговые значения (например, количество заказов, размещенных за год, или их общая стоимость). Проще всего получить такого рода сведения, создав запрос, который сгруппирует записи и выполнит необходимые вычисления. Это осуществляется с помощью функций группировки, представленных в следующей таблице.
| Функция | Назначение |
|---|
| Sum |
Вычисляет сумму значений, содержащихся в поле |
| Avg |
Вычисляет среднее арифметическое для всех значений поля |
| Count |
Определяет число значений поля, не считая пустых (Null) значений |
| Min |
Находит наименьшее значение поля |
| Max |
Находит наибольшее значение поля |
| StDev |
Определяет среднеквадратичное отклонение от среднего значения поля |
| Var |
Вычисляет дисперсию значений поля |
GardenCo
В этом упражнении вы создадите запрос, который вычисляет число наименований товаров, имеющихся в продаже, среднюю цену товара и общую стоимость всех товаров. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\Aggregate. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries), а затем щелкните дважды на команде Создание запроса в режиме конструктора (Create query in Design View). Откроется окно запроса и диалоговое окно Добавление таблицы (Show Table).
В диалоговом окне Добавление таблицы (Show Table) щелкните дважды на Товары, а затем щелкните на кнопке Закрыть (Close). Access добавит таблицу Товары в окно запроса и закроет диалоговое окно Добавление таблицы (Show Table).
В списке полей таблицы Товары щелкните дважды на КодТовара, а затем - на Цена. Оба поля переместятся на бланк запроса.
Щелкните на кнопке на панели инструментов.На бланке запроса появится дополнительная строка Групповая операция (Total), как показано ниже.
Щелкните в ячейке Групповая операция (Total) столбца КодТовара, щелкните на стрелке и выделите из списка Count. В ячейке Групповая операция (Total) появится слово Count. При выполнении запроса эта функция вычислит число записей, содержащих значение в поле КодТовара.
В ячейке Групповая операция (Total) столбца Цена задайте значение Avg.
Щелкните на кнопке . Результаты запроса представляют собой одну запись, содержащую число товаров и их среднюю цену.
Щелкните на кнопке , чтобы перейти в режим конструктора.
В ячейке Поле (Field) третьего столбца введите = Цена *МинимальныйЗаказ и нажмите на клавишу (Enter).Введенный текст будет преобразован в выражение, о чем свидетельствует префикс Выражение1: (Expr1:). Это выражение умножает цену товара на его количество.
В ячейке Групповая операция (Total) для третьего столбца задайте значение Sum, чтобы просуммировать значения, вычисленные с помощью заданного выражения.
Выделите текст Выражение1 (Expr1) и введите Общая стоимость.
Снова выполните запрос. Результаты показаны на следующем рисунке.
Закройте окно запроса, щелкнув на кнопке Нет (No) в ответ на предложение сохранить запрос.
Закройте базу данных и выйдите из Access.
Создание запроса в режиме конструктора
Пользоваться сортировкой и фильтрацией очень удобно, но при работе с несколькими таблицами их возможностей недостаточно. В таких случаях применяются запросы. Самый распространенный тип запросов отображает записи, удовлетворяющие определенным условиям, но существуют и другие типы, приведенные ниже.
Запрос на выборку извлекает данные из одной или нескольких таблиц и представляет их в табличном виде. Этот тип запроса можно использовать для группировки записей, вычисления сумм, средних величин и других итоговых значений. Работая с результатами запроса, можно одновременно редактировать данные из нескольких таблиц.
Параметрический запрос запрашивает ввод параметров (например, начальную и конечную дату). Этот тип запросов часто используется для получения отчетов за определенный период времени.
Перекрестный запрос выполняет расчеты и группирует данные для анализа информации. Для элементов, расположенных в левом столбце и в верхней строке результатов запроса, могут вычисляться итоговые значения (сумма, количество или средняя величина). Ячейки на пересечении строк и столбцов также содержат вычисляемые значения.
Запрос на действие вносит множественные изменения за одну операцию. Собственно, это запрос на выборку, который выполняет определенные действия над результатами отбора. Возможны четыре типа действий: обновление, удаление и добавление записей и создание таблицы. В двух последних случаях результаты запроса на выборку либо добавляются в существующую таблицу, либо для них создается новая таблица.
Совет. Access включает также запросы SQL, но в этом курсе они не рассматриваются.
Фильтры, сортировка и запросы
Основные различия между применением фильтрации и сортировки и использованием запросов состоят в следующем:
фильтрация и сортировка выполняются быстрее, чем запросы.;
фильтры и условия сортировки хранятся временно. Запрос можно сохранить на диске и использовать в дальнейшем;
фильтры и сортировка применяются только к активной таблице или форме. Запрос может базироваться на нескольких таблицах и других запросах, причем необязательно, чтобы они были открыты.
Запрос можно создать вручную или с помощью мастера. При этом создается выражение, описывающее критерий, которому должны соответствовать данные в указанных таблицах. В результате выполнения запроса записи, отвечающие заданным условиям, отобразятся в табличном виде.
GardenCo
В этом упражнении вы создадите форму для ввода заказов, полученных по телефону. Форма базируется на запросе, содержащем сведения из таблиц Сведения о заказе и Товары. Запрос создает таблицу, в которой перечислены все товары с указанием их цен, количества, скидок и стоимости покупки. Поскольку стоимость не хранится в базе данных, ее нужно вычислить прямо в запросе. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\QueryDes. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries).
Щелкните дважды на команде Воспользуйтесь диалоговым окном Добавление таблицы (Show Table), чтобы указать таблицы и запросы, которые нужно включить в данный запрос.
На активной вкладке Таблицы (Tables) щелкните дважды на таблицах Сведения о заказе и Товары, чтобы добавить их в окно запроса, и закройте диалоговое окно Добавление таблицы (Show Table).В верхней части окна запроса появятся изображения добавленных таблиц, содержащие списки полей, как показано на следующем рисунке.
Вверху каждого списка полей имеется звездочка, представляющая все поля таблицы. Ключевое поле отображается полужирным шрифтом. Линия, соединяющая поля КодТовара в обеих таблицах, указывает, что эти поля связаны.Совет. Чтобы добавить в запрос дополнительные таблицы, откройте диалоговое окно Добавление таблицы (Show Table). Для этого щелкните правой кнопкой мыши в верхней части окна запроса и воспользуйтесь командой Добавить таблицу (Show Table) в контекстном меню или щелкните на кнопке Отобразить таблицу (Show Table) на панели инструментов.
Нижняя часть окна запроса занята бланком, предназначенным для построения условий отбора.
Чтобы включить поля в запрос, нужно перетащить их из списков вверху окна в последовательные столбцы бланка запроса. Перетащите следующие поля:| Из таблицы | Поле |
|---|
| Сведения о заказе |
КодЗаказа |
| Товары |
ОписаниеТовара |
| Сведения о заказе |
Цена |
| Сведения о заказе |
Количество |
| Сведения о заказе |
Скидка |
Совет. Щелкнув дважды на поле, можно скопировать его в свободный столбец бланка. Чтобы скопировать сразу все поля таблицы, выделите нужный список (щелкнув дважды на его заголовке), а затем перетащите выделенный объект на бланк запроса. Когда вы отпустите кнопку мыши, все поля разместятся в последовательных столбцах бланка. Можно включить все поля таблицы в один столбец бланка, перетащив в него звездочку. Однако если требуется задать условия сортировки или отбора для определенных полей, нужно перетащить каждое из них в отдельный столбец.
Окно запроса должно выглядеть, как показано на следующем рисунке.
Щелкните на кнопке , чтобы выполнить запрос и отобразить результаты в виде таблицы.Измените запрос таким образом, чтобы упорядочить результаты по полю КодЗаказа, и добавьте поле для вычисления стоимости товара, которая определяется умножением цены на количество за вычетом скидки.
Щелкните на кнопке , чтобы вернуться в режим конструктора. Строка Сортировка (Sort) (третья на бланке) позволяет указать поле и принцип сортировки (по возрастанию или убыванию).
Щелкните в ячейке Сортировка (Sort) в столбце КодЗаказа, щелкните на стрелке и щелкните на По возрастанию (Ascending). Поскольку ни одна из таблиц не содержит стоимость покупки, воспользуйтесь построителем выражений, чтобы вставить в бланк запроса выражение для расчета стоимости.
Щелкните правой кнопкой мыши в ячейке Нужно построить следующее выражение: "Ccur([Сведения о заказе].[Цена]*[Количество]*(1-[Скидка])/100)*100"
Функция Ccur, используемая в выражении, преобразует результаты вычислений в денежный формат.
В первом столбце области элементов щелкните дважды на папке Функции (Functions), а затем щелкните на Встроенные функции (Build-In Functions). Во втором столбце отобразятся категории встроенных функций.
Во втором столбце щелкните на категории Преобразование (Conversion), чтобы ограничить список функций в третьем столбце этой категорией. Щелкните дважды на функции Ccur в третьем столбце.Построитель выражений
Выражения, используемые в фильтрах или запросах, обычно вводятся вручную или создаются с помощью функции Построитель выражений (Expression Builder). Чтобы открыть окно построителя, можно воспользоваться командой Построить (Build) в контекстном меню или щелкнуть на кнопке построителя : в конце поля, куда нужно ввести выражение.
Построитель выражений не является мастером, то есть, не проводит пользователя через процедуру построения выражения. Тем не менее он оказывает существенную помощь, предоставляя иерархические списки, содержащие большинство элементов (функций, операторов, полей), которые можно включить в выражение. Выбрав нужный элемент, можно скопировать его в поле выражения, а затем щелкнуть на кнопке ОК, чтобы поместить выражение в фильтр или запрос.
Функция преобразования в денежный формат вставлена в поле выражения. Вместо заполнителя <<expr>>, заключенного в скобки, нужно вставить выражение, вычисляющее число, которое будет преобразовано в денежный формат.
Щелкните на <<expr>>, чтобы выделить его. Элемент, который вы вставите следующим, заменит выделенный фрагмент.
Следующий элемент, который нужно вставить в выражение, - это поле В результате последнего действия курсор оказался в конце выражения, после поля Цена, что и требуется.
Теперь нужно умножить значение поля Цена на значение поля Количество. Щелкните на кнопке * (звездочка) в ряду операторов, расположенном под полем выражения. В выражении появится знак умножения и очередной заполнитель <<Выражение>> (<<expr>>).
Щелкните на заполнителе <<Выражение>> (<<expr>>), чтобы выделить его, и вставьте поле Количество, щелкнув дважды на нем во втором столбце. Введенное выражение вычисляет стоимость товара, умножая цену на количество. Но, чтобы получить окончательный результат, необходимо вычесть скидки, предлагаемые на отдельные товары. Скидки указаны в поле Скидки и составляют 10-20% от стоимости товара. Проще рассчитать процент, который нужно заплатить (80-90% от стоимости), чем вычислять скидку, а затем вычитать ее из стоимости товара.
Введите Хотя поле Скидка определено как процентное, в базе данных оно хранится в виде чисел от 0 до 1 (то есть, на экране отображается 10%, а в памяти хранится 0,1). Поэтому, если скидка составляет 10%, результат выражения (1- Скидка) равняется 0,9. Иначе говоря, при 10% скидке стоимость составит 0,9 от произведения цены на количество.
Щелкните на кнопке ОК. Окно построителя выражений закроется, и выражение будет скопировано в ячейку бланка запроса.
Нажмите на клавишу (Enter), чтобы завершить ввод выражения.Совет. Чтобы быстро подогнать ширину столбца под его содержимое, щелкните дважды на правой границе серой полосы вверху столбца.
Access присвоил выражению имя Выражение1 (Expr1). Щелкните на нем дважды, чтобы выделить, и введите более содержательное имя Окончательная цена.
Щелкните на кнопке Заказы теперь отсортированы по полю КодЗаказа, а в последнем столбце указана вычисленная стоимость товара.
Прокрутите таблицу вниз, чтобы просмотреть несколько записей со скидками. Как видите, стоимость рассчитана правильно.
Закройте окно запроса и щелкните на кнопке Да (Yes) в ответ на предложение сохранить изменения. Введите Сведения о заказе (окончательная цена) в качестве имени запроса и щелкните на кнопке ОК.
Закройте базу данных.
Создание запроса с помощью мастера
Процесс создания простого запроса с помощью мастера Создание запросов (Query Wizard) практически не отличается от создания формы с помощью мастера. Суть его заключается в выборе полей из существующих таблиц и запросов и добавлении их в новый запрос.
Для эффективной работы с несколькими таблицами необходимо установить между ними отношения. Поэтому, прежде чем запускать мастер запросов, откройте окно Схема данных (Relationships) и свяжите совпадающие поля в нужных таблицах.
GardenCo
В этом упражнении вы воспользуетесь мастером, чтобы создать запрос, извлекающий сведения о заказах из таблиц Клиенты и Заказы. Записи этих таблиц связаны через поле КодКлиента. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\QueryWiz. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries), а затем щелкните дважды на команде Создание запроса с помощью мастера (Create query by using wizard). Откроется первая страница мастера Создание простых запросов (Simple Query Wizard).Совет. Можно также запустить мастер, щелкнув на команде Запросы (Queries) в меню Вставка (Insert) или щелкнув на кнопке Новый объект (New Object), а затем щелкнув дважды на Мастер простых запросов (Simple Query Wizard).
В списке Таблицы и запросы (Tables/Queries) выделите Таблица: Заказы (Tables: Orders).
Щелкните на кнопке >>, чтобы переместить все доступные поля в список Выбранные поля (Selected Fields).
В списке Таблицы и запросы (Tables/Queries) выделите Таблица: Клиенты (Tables: Customers).
Щелкните дважды на полях Адрес, Город, Штат, ПочтовыйИндекс и Страна, чтобы переместить их в список Выбранные поля (Selected Fields), а затем щелкните на кнопке Далее (Next).Совет. Если взаимосвязь между таблицами не установлена, будет предложено установить связь, а потом снова запустить мастер.
Щелкните на кнопке Далее (Next), чтобы принять подробный вариант, заданный по умолчанию.
Введите имя запроса Запрос на заказы, оставьте выделенным вариант Открыть запрос для просмотра данных (Open Query to view information) и щелкните на кнопке Готово (Finish).Access выполнит запрос и отобразит результаты в виде таблицы. Прокрутите записи, чтобы убедиться, что отображаются сведения обо всех заказах.
Щелкните на кнопке , чтобы переключиться в режим конструктора. Обратите внимание, что для всех полей выделены флажки в ячейках Вывод на экран (Show). Очистив флажок, можно отменить отображение поля, которое включено в запрос для сортировки или создания условия отбора, но не требуется при просмотре.
Очистите флажки Вывод на экран (Show) для полей КодЗаказа, КодКлиента и КодСотрудника, а затем щелкните на кнопке Вид (View), чтобы переключиться в режим таблицы. Как видите, все три поля исключены из результатов запроса.
Щелкните на кнопке Вид (View), чтобы вернуться в режим конструктора. Этот запрос извлекает все записи из таблицы Заказы. Можно ограничить просмотр заказами, сделанными в определенный период, преобразовав запрос в параметрический, который запрашивает диапазон дат при запуске.
В столбце ДатаРазмещения щелкните в ячейке Условие отбора (Criteria) и введите Between [Введите начальную дату:] And [Введите конечную дату:].
Щелкните на кнопке , чтобы выполнить запрос. Access отобразит следующее диалоговое окно.
Введите 1/1/01 и нажмите на клавишу (Enter).
Во втором диалоговом окне Введите значение параметра (Enter Parameter Value) введите 1/31/01 и нажмите на клавишу (Enter). Появятся результаты запроса, содержащие только те заказы, которые были сделаны в указанный период.
Закройте таблицу и щелкните на кнопке Да (Yes), чтобы сохранить запрос.
Закройте базу данных.
Вычисления в запросе
Обычно запросы используются для поиска информации, удовлетворяющей заданным условиям. В некоторых ситуациях, однако, пользователя интересуют не столько конкретные данные, сколько итоговые значения (например, количество заказов, размещенных за год, или их общая стоимость). Проще всего получить такого рода сведения, создав запрос, который сгруппирует записи и выполнит необходимые вычисления. Это осуществляется с помощью функций группировки, представленных в следующей таблице.
| Функция | Назначение |
|---|
| Sum |
Вычисляет сумму значений, содержащихся в поле |
| Avg |
Вычисляет среднее арифметическое для всех значений поля |
| Count |
Определяет число значений поля, не считая пустых (Null) значений |
| Min |
Находит наименьшее значение поля |
| Max |
Находит наибольшее значение поля |
| StDev |
Определяет среднеквадратичное отклонение от среднего значения поля |
| Var |
Вычисляет дисперсию значений поля |
GardenCo
В этом упражнении вы создадите запрос, который вычисляет число наименований товаров, имеющихся в продаже, среднюю цену товара и общую стоимость всех товаров. В качестве рабочей будет использоваться папка Office XP SBS\Access\Chap12\Aggregate. Выполните следующие шаги.
Откройте базу данных GardenCo, расположенную в рабочей папке.
На панели объектов щелкните на Запросы (Queries), а затем щелкните дважды на команде Создание запроса в режиме конструктора (Create query in Design View). Откроется окно запроса и диалоговое окно Добавление таблицы (Show Table).
В диалоговом окне Добавление таблицы (Show Table) щелкните дважды на Товары, а затем щелкните на кнопке Закрыть (Close). Access добавит таблицу Товары в окно запроса и закроет диалоговое окно Добавление таблицы (Show Table).
В списке полей таблицы Товары щелкните дважды на КодТовара, а затем - на Цена. Оба поля переместятся на бланк запроса.
Щелкните на кнопке на панели инструментов.На бланке запроса появится дополнительная строка Групповая операция (Total), как показано ниже.
Щелкните в ячейке Групповая операция (Total) столбца КодТовара, щелкните на стрелке и выделите из списка Count. В ячейке Групповая операция (Total) появится слово Count. При выполнении запроса эта функция вычислит число записей, содержащих значение в поле КодТовара.
В ячейке Групповая операция (Total) столбца Цена задайте значение Avg.
Щелкните на кнопке . Результаты запроса представляют собой одну запись, содержащую число товаров и их среднюю цену.
Щелкните на кнопке , чтобы перейти в режим конструктора.
В ячейке Поле (Field) третьего столбца введите = Цена *МинимальныйЗаказ и нажмите на клавишу (Enter).Введенный текст будет преобразован в выражение, о чем свидетельствует префикс Выражение1: (Expr1:). Это выражение умножает цену товара на его количество.
В ячейке Групповая операция (Total) для третьего столбца задайте значение Sum, чтобы просуммировать значения, вычисленные с помощью заданного выражения.
Выделите текст Выражение1 (Expr1) и введите Общая стоимость.
Снова выполните запрос. Результаты показаны на следующем рисунке.
Закройте окно запроса, щелкнув на кнопке Нет (No) в ответ на предложение сохранить запрос.
Закройте базу данных и выйдите из Access.
