1 Щелкните в панели инструментов на кнопке Добавить таблицу.
2 Сделайте двойной щелчок мышью на именах таблиц, которые вы хотите добавить в запрос.
3 Закройте окно Добаление таблицы.
Объединение таблиц
Чтобы избежать некоторых проблем, возникающих при объединении таблиц, можно изменить тип их объединения. Дважды щелкните на линии связи, и на экране появится окно
диалога, представленное на рис. 6 Первый параметр (он с установлен по умолчанию) задает такое объединение, при котором выводятся записи только с совпадающими значениями связанных полей.
Рис.6
Выбор второго параметра в окне диалога приведет к отбору всех записей основной таблицы, удовлетворяющих всем условиям, даже если ключевые значения и не совпадают со значениями в подчиненной таблице. Например, если клиент не оставлял заказа, то поле Дата будет пустым. Выбрав этот параметр, вы увидите, что линия связи указывает на подчиненную таблицу. Если запустить запрос на выполнение, то в выборку попадут абсолютно все клиенты из Москвы, даже если они и не оставляли заказа.
При выборе третьего значения Access выведет все записи из подчиненной таблицы, удовлетворяющие условиям, и только те записи из основной таблицы, значения в которых совпадают со значениями связанных полей. Но так как при задании отношений между таблицами была выбрана поддержка ссылочной целостности, то первое и третье значения параметра объединения дадут нам один и тот же результат.
Универсальные запросы с параметрами
Задание в запросе конкретных значений в действительности ограничивает область его применения. Если вы вдруг решите связаться с клиентами во время поездки на пляжи в Сочи, этот запрос вам не поможет — он всегда будет возвращать список клиентов из Москвы. Вместо того чтобы задавать конкретное условие, можно воспользоваться параметром.Например, можно так преобразовать запрос Клиенты в Москве, чтобы город можно было выбрать при запуске запроса. В окне конструктора в строке Условие отбора поля Город надо удалить текст, а затем либо непосредственно, либо в окне Областьввода (клавиши SHIFT+F2) задать подсказку в виде [Введите город]. (Для ввода длинного условия можно и обойтись без окна область ввода, ведь его можно набрать прямо в строке Условие отбора.) Параметр служит подсказкой, благодаря которой при запуске запроса вы узнаете, чего от вас хотят, обрамляющие его квадратные скобки — это просто правило оформления.
При запуске измененного запроса (со страницы Запросы или из окна базы данных) Access выводит следующее
окно диалога, требующее ввода название города:
Если теперь ввести Ижевск, то появиться список клиентов
Из Ижевска.
Выборку также можно сделать, используя оператор LIKE .
Допустим, нам необходимо найти названия фирм находящихся в городах, которые начинаются на букву ”Н”
Используем Построитель выражений .Окно построителя выражений состоит из трех разделов, располагающихся сверху вниз
Поле выражения. В верхней части окна построителя расположено поле, в котором создается выражение (Выражение. Сочетание математических и логических операторов, констант, функций, имен полей, элементов управления и свойств, в результате обработки которого может получаться только одно значение. Выражение может выполнять вычисления, манипулировать знаками или проверять данные.). Ниже находится раздел, предназначенный для создания элементов выражения и их последующей вставки в поле выражения. Допускается непосредственный ввод части выражения в поле выражения
Кнопки операторов. В средней части окна построителя находятся кнопки с часто используемыми операторами (Оператор. Знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.). При нажатии на одну из этих кнопок построитель вставит соответствующий оператор в текущую позицию поля выражения. Чтобы вывести полный список операторов, выберите папку Операторы в нижнем левом поле и нужный тип в среднем поле. В правом поле будут выведены все операторы выбранного типа.
Элементы выражения. В нижней части окна построителя находятся три поля.
. В левом поле выводятся папки, содержащие таблицы, запросы, формы, объекты базы данных, встроенные и определенные пользователем функции, константы (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Формула и результат вычисления формулы константами не являются.), операторы и общие выражения
В среднем поле задаются определенные элементы или типы элементов для папки, заданной в левом поле. Например, если выбрать в левом поле Встроенные функции, то в среднем поле появится список всех типов функций Microsoft Access.
В правом поле выводится список значений (если они существуют) для элементов, заданных в левом и среднем полях. Например, если выбрать в левом поле Встроенные функции и тип функции в среднем, то в правом поле будет выведен список всех встроенных функций выбранного типа.
Для того же поля или для других полей можно ввести дополнительные условия отбора. Если выражения вводятся в несколько ячеек Условие отбора, то они автоматически объединяются с помощью операторов And или Or. Если выражения находятся в разных ячейках, но в одной строке, то Microsoft Access использует оператор And. Это означает, что будут возвращены только записи, отвечающие условиям отбора, указанным во всех ячейках. Если же выражения находятся в разных строках бланка запроса, то Microsoft Access использует оператор Or, что означает, что возвращены будут записи, отвечающие условиям отбора, указанным любой ячейке.
Рассмотрим пример когда нам нужно найти названия фирм и фамилии клиентов ,находящихся в городах , которые начинаются на букву “М”.Запрос на выборку будет выглядеть так:
В поле выражения строим выражение Like М*, где “М” первая буква в названии города
Запускаем запрос и видим результат :
Можно также использовать несколько условий выбора .
Примечание. При вставке идентификатора (Идентификатор (выражения). Элемент выражения, определяющий ссылку на значение поля, элемента управления или свойства. Например, Forms![Заказы]![КодЗаказа] является идентификатором для значения элемента управления «КодЗаказа» в форме «Заказы».) в выражение построитель вставляет только те его части, которые требуются в текущем контексте. Например, при запуске построителя выражений из окна свойств (Окно свойств. Окно, предназначенное для просмотра и изменения свойств таблиц, запросов, полей, форм, отчетов, страниц доступа к данным и элементов управления.) формы «Клиенты» и вставке идентификатора для свойства Вывод на экран (Visible) будет вставлено только имя свойства Visible. При использовании данного выражения вне контекста формы необходимо включать полный идентификатор: Forms![Клиенты].Visible
Использование нескольких параметров
Старайтесь создавать как можно более гибкие запросы. Для еще большей гибкости при отборе записей можнс задать не один, а несколько параметров — ровно столько сколько вам потребуется. Например, вам нужен список клиентов из определенного города, сделавших заказы в некоторый интервал времени. Здесь понадобятся три параметра: название города, начальная и конечная даты периода оформлена заказов. В запросе, который мы изменяли выше, один параметр уже есть, поэтому остается только ввести в поле Дате следующее условие: BETWEEN [Введите начальную дату]AND[Введите конечную дату]
.
Теперь при открытии запроса появятся три окна диалога. В первом можно ввести Санкт-Петербург, востальных — начальную и конечную даты, например1.01.2002 и 2.01.2002 . Есть и другие способы использования двух параметров для одного поля. Конструкция BETWEEN ... AND пригодна для проверки вхождения в диапазон числа или денежной единицы например BETWEEN [Введите минимальную зарплату] АND [Введите максимальную зарплату]: это подходит для выборки выводящей список сотрудников с определенным уровнем доходов.
Оператор OR (ИЛИ) не менее полезен для задани множественных параметров. При запуске запроса, в которое поле Город содержит один параметр, в ответ на запрос BЫ можете ввести только одно значение; указания Москва ил Санкт-Петербург Access не поймет. Но если вам известно, что проверить надо два значения, то параметры можно задать так : [Введите один город] OR [Введите другой город]. Вы увидите две подсказки, и Access использует параметры в условии оператором OR.
Изменение порядка следования параметров
При открытии запроса, составленного в предыдущем примере, Access сперва запросит город, а затем даты, так как в списке полей запроса параметры задавались именно в такой последовательности. Однако есть два способа изменить порядок, в котором запрашиваются параметры. Во-первых
изменить порядок размещения полей в списке полей запрос перетаскиванием их на новое место. Но в этом случс изменится и порядок их размещения в выборке. Если выбор* вам трогать не хочется, то воспользуйтесь вторым способа (окном Параметры запроса (Parameters)).
Для вывода на экран приведенного ниже окна выберите меню Запрос (Query) команду Параметры (Parameters):
Дпя вывода на экран приведенного ниже окна выберите в меню Запрос команду Параметры:
Рис.7
Введите параметры в том порядке, в котором вы хотите чтобы они появлялись . . Для каждого параметра раскройте список Тип данных (Data Type) и выберите тот же тип, что и у поля. Затем щелкните на кнопке ОК. При открытии запроса параметры будут запрашиваться в соответствии с их положением в списке.
ВЫЧИСЛЕНИЕ ЗНАЧЕНИЙ ПО ПОЛЯМ
Пока вы пользовались запросами для отбора и просмотра данных из таблицы. Но таблица может дать гораздо больше информации. Имеется ввиду вычисление значений на основании существующих полей. Например, таблица СведенияЗаказов содержит поле Количество, а в таблице Склад есть поле Цена. Количество сообщает, как много изделий заказал клиент. Цена — стоимость одного изделия. Для вычисления общей стоимости необходимо перемножить значения этих полей. (Она понадобится также при расчете полной суммы заказа.) На рис. 8 представлен запрос, который может стать основой для отчета о состоянии дел на складе. Он содержит поле КодКлиента из таблицы Клиенты, поля КодЗаказа и Дата из таблицы Заказы, поле КодИзделия из таблицы СведенияЗаказов. В запросе также участвует таблица Склад , так что ее поля доступны для вычисления стоимости заказа. Обратите внимание, что вычисляемые поля , могут участвовать в сортировке и фильтрации данных.
Рис.8
Чтобы произвести вычисления, в строке Поле (Field) вводится выражение (математическое). В нашем примере в первом пустом столбце введите следующее: Сумма:AVG[CведенияЗаказов]![Коли-
чество]*[Цена].
«Сумма:»
— это название будущего столбца; за ней
следует двоеточие. Если не указать
заголовка, то Access создаст его сам, причем
весьма бессмысленный. Имена полей
вводятся в том виде, как они указаны в
таблице, только заключенные в квадратные
скобки, а для вычислений используются
математические операторы.
Если какое-то поле встречается только в одной таблице, то ее имя указывать необязательно. Однако в нашем случае две используемые таблицы содержат поле Количество. Если бы в выражении использовались только имена полей, то Access выдал бы сообщение о неоднозначной ссылке на поле. Мы не хотим огорчать Access, поэтому указываем имя таблицы, заключенное в квадратные скобки, восклицательный знак, затем имя поля: [СведенияЗаказов]![Количество]. Это гарантирует, что в поисках поля Количество программа обратится к таблице СведенияЗаказов, а не к таблице Склад
Выполните запрос и посмотрите на вычисленное значение. (При добавлении нового поля Access автоматически устанавливает флажок.)
Установка свойств
Для вывода значений Access использует заданный в таблице формат соответствующих полей. Например, при выводе значения поля, содержащего данные о какой-то сумме, будет использован денежный стандарт. Однако при задании вычислемого поля формат отображения данных надо определять наряду с прочими свойствами.
Чтобы установить свойства поля, щелкните на любой строке столбца этого поля правой кнопкой мыши и выберите во всплывающем меню команду Свойства (Properties). Можно также выбрать поле и щелкнуть на кнопке Свойства (Properties) в панели инструментов. Вы увидите следующее окно диалога.
Рис.9
Большая часть свойств предназначена для задания дополнительных возможностей. Свойство Маска ввода (Input Mask) определяет формат ввода. Чтобы задать формат отображения, раскройте список Формат (Format) и выберите то, что вам нравится, после чего закройте окно диалога.
ГРУППОВАЯ ОБРАБОТКА ДАННЫХ
Мастер Простой запрос дает возможность групповой обработки информации. Те же результаты можно получить и в запросах, создаваемых с помощью конструктора запросов. Например, мы только что рассмотрели запрос, который выводит стоимость каждого товара в заказе. Но вас скорее всего заинтересует не подробная информация, а итоговое значение для заказа в целом. Чтобы получить эти данные, в список полей запроса необходимо добавить еще одну строку и указать, по каким полям производить группировку и какие математические операции применить к полям.
Для того чтобы добавить эту строку, щелкните мышью на кнопке Групповые операции (Totals), и в списке полей запроса появится новая строка с названием Групповая операция (Total). По умолчанию у всех полей выбрано Группировка (Group By). Если вы желаете вычислить суммарные значения по счетам, то придется выполнить группировку по полям, которые однозначно определяют заказ, а именно КодКлиента и КодЗаказа, так как только они обеспечивают в этом случае уникальность.
Так как нас больше не интересуют отдельные товары, удалите поля Дата и Изделие; чтобы выделить столбец, щелкните мышью на серой полоске над ним и нажмите клавишу DEL,чтобы удалить его. В полях, не участвующих в группировке, указывается характер вычислений. Например, чтобы вывести итог по каждому счету, щелкните мышью в строке групповая операция вычисляемого поля, раскройте список и выберите Sum (сумма). Когда вы выполните запрос, Access выведет сумму для каждого заказа. В списке Групповая операция содержатся и другие операции, которые можно выполнить над содержимым полей. Можно
сменить статистическую математическую функцию, использовать Условие (Where) или задать выражение. Для задания условия создания группы используется значение Условие. Например, если вы хотите сгруппировать клиентов из Москвы, добавьте в список полей запроса Город, выберите в качестве оператора Условие и введите в строке Условие отбора (Criteria) значение Москва. (При выборе значения Условие Access сам сбросит ( флажок Вывод на экран)
Оператор Условие определяет, какие записи будут входить в группы. Можно выбрать также, по каким группам выводить результаты. Например, вы хотите ограничить список только теми заказами, общая сумма которых превышает $500. Для этого в строке Условие отбора для вычисляемого поля нужно задать значение 500.
СВОЙСТВА ЗАПРОСА
Есть и другие способы, позволяющие указать, какие записи или группы надо выводить в запросе. Вы, должно быть, заметили на панели инструментов Запросы поле со значением Все (All).
Это поле Набор значений (Top Values). По умолчанию Access выводит все записи, удовлетворяющие поставленным условиям. При сортировке выборки вас тем не менее могут интересовать не все записи, а, например, пять наиболее крупных заказов или десять наименее успевающих студентов.
Чтобы ограничить количество выводимых записей, введите нужное число в поле Набор значений или раскройте список и выберите из него одно из следующих значений
Контролировать вывод данных можно и через свойства запроса. Чтобы увидеть свойства запроса, щелкните мышью на свободном месте в серой области окна запроса, где располагаются окна со списками полей, затем щелкните на кнопке Свойства (или щелкните правой кнопкой мыши и выберите из появившегося меню команду Свойства). Вы увидите следующее окно диалога:
Введите значение в поле Набор значений или выберите его из раскрывающегося списка.
Двумя другими весьма полезными свойствами являются Уникальные значения (Unique Values) и Уникальные записи (Unique Records). Хотя в таблице и не должно быть повторяющихся записей, сочетания используемых в запросе полей могут привести к появлению в выборке повторяющихся строк. Если значение свойства Уникальные значения — Нет (No), Access будет выводить все записи, даже если значения полей в некоторых из Свойство Уникальные записи похоже, но оно учитывает все поля, включая и те, которые не используются в запросе. Если в нем установлено значение Да, то дублирующиеся записи в запросе не появятся.
СОЗДАНИЕ ПЕРЕКРЕСТНЫХ ЗАПРОСОВ
Перекрестный запрос (Crosstab Query Wizard, Crosstab — сокращение от cross tabulation — перекрестный) требует хотя бы краткого обсуждения. Запросы этого рода предназначены для анализа информации, основанного на двух и более полях из одной или более таблиц. Например, вам хочется узнать, что предпочитают заказывать клиенты. Вас интересует, какие товары и в каком количестве они заказывают. Значит, данные будут просматриваться и по клиентам, и по товарам.
К сожалению, мастер перекрестных запросов использует поля только из одной таблицы или запроса; связанные таблицы ему недоступны. Чтобы обойти это ограничение, придется сначала создать обычный запрос по нескольким таблицам. Итак, давайте создадим в конструкторе запрос с полем КодКлиента из таблицы Клиенты, Количество из таблицы СведенияЗаказов и Изделие из таблицы Склад. Окно запроса будет включать также и таблицу Заказы, так как она связывает остальные таблицы. Этот простой запрос предоставит нам список товаров и их количество во всех заказах.
После того как вы создадите и сохраните этот запрос —
назовите его как-нибудь так: Для перекрестного запроса
, начните создание еще одного, выбрав для мастер Перекрестный запрос.
В первом окне мастер просит указать запрос или таблицу, по которой вы хотите построить перекрестный запрос. Если в списке перечислены таблицы, то выберите в группе Показать позицию Запросы (Queries), чтобы увидеть список запросов базы данных, и щелкните на запросе, созданном вами на первом шаге, а затем щелкните на кнопке Далее.
Во втором окне укажите поле, которое будет использоваться для заголовков строк.
Если вы проводите анализ заказов клиентов, дважды щелкните на поле КодКлиента и щелкните на кнопке Далее. В следующем окне мастер запрашивает поле для заголовков столбцов.
В нашем примере в качестве второго измерения при анализе используется поле Изделие.
В следующем окне мастеру надо указать поле, по которому производятся вычисления, и характер вычислений. По умолчанию установлено вычисление среднего значения. Наш пример требует суммарного значения, поэтому в списке функций мы выберем Sum (сумма) и убедимся, что установлен флажок Да (Yes, include summary rows), расположенный под текстом Вычислить итоговое значение для каждой строки.
В последнем окне от вас требуется придумать запросу имя (назовем его Товары) и сообщить, желаете ли вы продолжить разработку , запроса или просмотреть его результаты.
Результат:
Как вы видите, отличие перекрестного запроса от того, что мы делали чуть раньше, заключается в дополнительной строке Перекрестная таблица (Crosstab).
Раскройте в ней список и выберите способ использования данного пола в запросе. На выбор предлагаются следующие значения: Заголовки строк (Row heading), Заголовки столбцов (Column heading), Значение (Value) и (не отображается) (Not shown). Нам необходимы по крайней мере одно поле заголовков
строк, одно — заголовков столбцов и одно — значений. Выбор значения (не отображается) используется для тех полей, которые не включаются в выборку, но по которым производится сортировка или задаются условия отбора. Поля заголовков строк и столбцов должны участвовать в группировке, а для поля значения должен использоваться математический оператор.
Как и для Простого (поискового) запроса , так и для Перекрестного запроса запрос можно сделать с помощью оператора Like , запрос делается точно также, как в Простом запросе.
Допустим нам нужно сделать запрос по издилию , которое начинается на букву “Б” , для этого открываем
Построитель выражений и в Поле выражения заполняем :
конструктор будет выглядеть следующим образом :
Результат по Перекрестному запросу будет иметь следующий вид:
Допустим , нам нужно сделать запрос сразу по нескольким изделиям, а точнее по первым буквам названия изделия :
Такой запрос будет выглядеть таким образом :
ФИЛЬТРЫ И ЗАПРОСЫ
Фильтры и запросы делают примерно одно и то же. И те и другие используют список полей для определения, какие записи окажутся на экране и как они будут отсортированы. Любой запрос можно быстро преобразовать в фильтр и наоборот. Когда на экране находится окно фильтра или расширенного фильтра, в меню Файл можно выбрать одно из следующих действий:
• Сохранить как запрос (Save As Query) сохраняет фильтр как запрос. Затем этот запрос можно открыть и изменять, как если бы он был создан конструктором запросов.
• Загрузить из запроса (Load From Query) дает возможность использовать для задания фильтра готовый запрос.
Цель работы: освоить создание средствами Access запросов по реляционной базе данных.
Задачи работы: освоить понятие запроса в Access, научиться создавать с помощью конструктора или мастера поисковые (простые) и перекрестные запросы
Материальное оснащение: ПК с установленным ПО не ниже MicroSoft Access 97.
Краткие теоретические сведения даны выше.
Методика выполнения работы:
Создать поисковый запрос с заданием нескольких параметров.
Создать перекрестный запрос.
В отчете должны быть отображены все этапы создания запросов с конкретными примерами в виде рисунков, показывающих этапы создания запросов и результаты работы.
Литература: Алан Нейбауэр “Access для занятых”