Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.

Покажем, как создавать запросы с параметрами на примере запроса "Отсортированный список товаров", который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:

1. Откройте данный запрос в режиме Конструктора.

2. Чтобы определить параметр запроса, введите в строку Условие отбора (Criteria) для столбца "Название" (CompanyName) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.

3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите из контекстного меню команду Параметры (Parameters) или выполните команду меню Запрос, Параметры (Query, Parameters). Появляется диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 4.31.

Рис. 4.31. Диалоговое окно Параметры запроса

4. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования через буфер обмена), только можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.

5. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое нужно ввести значение, например Tokyo Traders. Результат выполнения запроса представлен на рис. 4.33. В него попадают только те товары, которые поставляются данным поставщиком.

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

Рис. 4.32. Диалоговое окно Введите значение параметра

Рис. 4.33. Результат выполнения запроса с параметром

Совет

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

Запрос с параметром (параметрический запрос)

Как правило, запросы с параметром (рис. 10.34) создаются в тех случаях, когда предполагается выполнять этот запрос многократно, изменяя лишь условия отбора.

Чтобы определить параметр, введите в строку "Условие отбора" вместо конкретного значения имя или фразу, заключенную в квадратные скобки (). То, что заключено внутри квадратных скобок, Access рассматривает как имя параметра. Оно выводится в окне диалога при выполнении запроса, поэтому в качестве имени параметра разумно использовать содержательную фразу. В запросе можно задать

Рис. 10.34.

несколько параметров; при этом имя каждого из них должно быть уникальным и информативным.

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

Перекрестный запрос

Перекрестный запрос – особый тип итогового запроса. Он позволяет вывести вычисляемые значения в перекрестной таблице, напоминающей электронную.

Для построения перекрестного запроса выполните следующие действия.

В режиме конструктора сформируйте запрос на выборку.

Измените тип запроса на Перекрестный (вкладка Работа с запросами , группа Тип запроса ) (рис. 10.35).

Рис. 10.35.

В нижней части бланка запроса появятся строки "Групповая операция" и "Перекрестная таблица" (рис. 10.36).

В строке "Перекрестная таблица" для каждого поля запроса выберите одну из четырех установок: Заголовки строк, Заголовки столбцов. Значение (выводимое в ячейках перекрестной таблицы) или Не отображается.

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

Определите одно (и только одно) поле значений. Это поле должно быть итоговым (т.е. в строке "Групповая операция" должна быть задана одна из итоговых функций, на-

Рис. 10.36.

пример суммирования (Sum), определения среднего значения (Avg) или количества (Count)).

В качестве примера рассмотрим создание перекрестного запроса, отображающего количество товаров, хранящихся на складах. В заголовках строк укажем наименования товаров, в заголовках столбцов – номера складов (рис. 10.37).

Рис. 10.37.

Вычисляемые поля в запросах

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

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

Вычисляемое поле может также содержать результат конкатенации (объединения) значений текстовых полей. Для этого заключите текст в кавычки, в качестве оператора конкатенации используется символ "&". Например, можно создать поле, которое будет содержать результат объединения поля [Фамилия] и поля [Имя].

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

Создание вычисляемого поля осуществляется путем простого ввода выражения для вычисления в ячейку "Поле" пустого столбца бланка запроса (рис. 10.38).

Рис. 10.38.

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

В режиме конструктора запроса измените имя "Выражение!" на более содержательное. В примере "Выражение!" изменено на "ЦенаСоСкидкой".

Для того чтобы ввести сложные вычисления, используйте окно Построитель выражений (рис. 10.39), которое вызывается командой , расположенной на вкладке Работа с запросами (в режиме конструктора) в группе Настройка запроса , либо соответствующей командой контекстного меню.

Рис. 10.39.

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

Построитель поможет правильно построить выражение. Щелкните на кнопке ОК , и введенное выражение будет перенесено в бланк запроса.

Как правило, запросы с параметром создаются в тех случаях, когда предполагается выполнять этот запрос многократно, изме­няя лишь условия отбора. В отличие от запроса на выборку, где для каждого условия отбора создается свой запрос и все эти за­просы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный запрос и вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз по­лучая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые бу­дут выведены в запросе. Значение параметра задается в специаль­ном диалоговом окне. В случае, когда значение выводимых дан­ных должно быть больше или меньше указываемого значения параметра, в поле «Условие отбора» бланка запроса перед пара­метром, заключенным в квадратные скобки ставится соответст­вующий знак. Можно также создавать запрос с несколькими па­раметрами, которые связываются друг с другом логическими опе­рациями И и ИЛИ. В момент запуска запроса на выполнение MS Access отобразит на экране диалоговое окно для каждого из пара­метров. Помимо определения параметра в бланке запроса, необ­ходимо указать с помощью команды Запрос– Параметры соот­ветствующий ему тип данных:

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

2. В качестве условия введите параметр, заключенный в квад­ратные скобки (например, [Введите название] или >[Выше какого роста?]).

3. Выберите команду Запрос > Параметры.

4. В появившемся окне Параметры запроса введите без квад­ратных скобок параметр (для точности ввода воспользуй­тесь «быстрыми» клавишами копирования и вставки из бу­фера обмена) и укажите соответствующий ему тип данных. Нажмите ОК.

5. Нажмите кнопку Запуск панели инструментов.

6. В появившемся окне укажите значение параметра.

7. Результат запроса будет содержать только те записи, кото­рые удовлетворяют заданному значению параметра.

Вычисляемые поля в запросах

Запрос можно использовать для выполнения расчетов и под­ведения итогов из исходных таблиц. Для создания вычисляемых полей используются математические и строковые операторы. При этом Access проверяет синтаксис выражения и автоматиче­ски вставляет следующие символы:

Квадратные скобки ([...]), в них заключаются имена элемен­тов управления;

Знаки номеров (ft), в них заключаются распознанные даты;

Кавычки (""), в них заключается текст, не содержащий про­белов или знаков пунктуации.

Поле, содержимое которого, является результатом расчета по содержимому других полей, называетсявычисляемым полем. Вы­числяемое поле существует только в результирующей таблице. Общий формат вычисляемого поля выглядит так: Имя вычисляе­мого поля: Выражение для создания вычисляемого поля.

Например: Прибыль: [Доход]-[Расход].

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

1. В строку «Поле» пустого столбца бланка запроса введите выражение, начинающееся со знака «=» и состоящее из имен полей, записанных в квадратные скобки и какой-либо арифметической или другой операции.

2. После выполнения запроса в результирующей таблице поя­вится новое поле с названием «Выражение!», используемым в качестве имени вычисления выражения.

3. В режиме конструктора запроса измените имя «Выраже­ние!» на более значимое.

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

Итоговые запросы

Запросы позволяют производить итоговые вычисления. Для этих целей в Access 97 предусмотреныстатистические функции SQL (рис. IV.6). Статистическую функцию задают в строке Груп­повая операция бланка запросов, которая появляется при выпол­нении команды Вид > Групповые операции.

Функция SQL	Действие
Sum	Суммирование значений определенного поля
Avg	Вычисление среднего значения данных определен­ного поля
Min	Вычисление минимального значения поля
Max	Вычисление максимального значения поля
Count	Вычисление количества записей, отобранных запро­сом по условию
First	Определяется первое значение в указанном поле за­писей, отобранных запросом
Last	Определяется последнее значение в указанном поле записей, отобранных запросом
StDev	Вычисляется стандартное отклонение значений дан­ного поля, для всех записей, отобранных запросом
Var	Вычисляется вариация значений данного поля для всех записей, отобранных запросом

Рис. 6 Функции SQL

Для выполнения итогового запроса:

1. Находясь в режиме Конструктора запроса, выберите коман­ду Видä Групповая операция или нажмите кнопку Группо­вая операция панели инструментов. В результате чего в бланке запроса появится строка «Групповая операция».

2. Для соответствующего поля выберите нужную функцию из списка «Группировка».

Перекрестный запрос

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

1. В режиме Конструктора сформируйте запрос, добавив таб­лицу, которая должна лежать в его основе.

2. Выберите команду Запрос – Перекрестный. Строка запроса «Вывод на экран» в бланке запроса изменится на новую строку «Перекрестная таблица» и перед ней появится строка «Групповая операция».

3. В строке «Поле» укажите поле, значения которого в новой таблице должны появиться в виде строк; поле, значения кото­рого в новой таблице должны появиться в виде столбцов, и поле, содержимое которого в перекрестной таблице необходи­мо индицировать в качестве значения. Полей, которые будут использованы в качестве заголовков может быть несколько.

4. Щелкните мышью в строке «Перекрестная таблица» и выбе­рите соответствующие значениям данных полей опции из разворачивающегося списка.

5. Для поля, содержимое которого индицируется в качестве значений, в строке «Групповая операция» введите необхо­димую функцию, например, автосуммирования (Sum), оп­ределения среднего значения (Avg) или количества (Count). На основе данных перекрестного запроса можно строить диа­граммы, представленные в виде формы (см. п. IV. 14.1. Создание формы).

Создайте запрос на выборку с параметром, в котором должны отображаться фамилии, имена, отчества преподавателей и преподаваемые ими дисциплины, а в качестве параметра задайте фамилию преподавателя и выполните запрос для преподавателя Гришина . Для создания запроса на выборку с параметром:

· Создайте запрос для следующих полей таблицы Преподаватели: Фамилия, Имя, Отчество, Дисциплина . Запрос создайте как Простой запрос аналогично тому, как это делалось в п.1.

· Задайте имя запросу Преподаваемые дисциплины. Щелкните по кнопке Готово. На экране появится таблица с результатами.

· Перейдите в режим конструктора, щелкнув по кнопке или выполнив команду Вид/ Конструктор .

· В строке Условия отбора для поля Фамилия введите фразу (скобки тоже вводить): [Введите фамилию преподавателя:].

· Выполните запрос, щелкнув по кнопке на панели инструментов или выполните команду Запрос/ Запуск .

· В появившемся окне введите фамилию Гришин и щелкните по кнопке ОК .

· На экране появится таблица с данными о преподавателе Гришине – его имя, отчество и преподаваемая дисциплина.

· Сохраните запрос, щелкнув по кнопке или выполнив команду Файл/ Сохранить

· Закройте окно запроса.

Отчеты.

Отчет – форматированное представление данных для вывода на принтер, экран или в файл.

На основе таблицы Преподаватели создайте отчет с группированием данных по должностям. Для создания отчета:

· В окне базы данных Преподаватели выберите объект Отчеты и щелкните по кнопке Создать .

· В открывшемся окне Новый отчет выберите пункт Мастер отчетов .

· Щелкните по значку раскрывающегося списка в нижней части окна. Выберите из появившегося списка таблицу Преподаватели . Щелкните по кнопке ОК .

· В появившемся окне выберите поля, которые будут присутствовать в отчете. В данном примере будут присутствовать все поля из таблицы, поэтому щелкните по кнопке . Щелкните по кнопке Далее .

· В появившемся окне присутствует перечень полей. Выделите поле Должность . Щелкните по кнопке . Таким образом вы задаете группировку данных по должности. Щелкните по кнопке Далее .

· В появившемся окне выберите порядок сортировки: сначала по полю Фамилия , затем по полю Имя , затем по полю Отчество .

· Нажмите на кнопку Итоги . Подведите итоги по Зарплате , выбрав функцию Sum . ОК .

· В появившемся окне выберите макет для отчета.

· В появившемся окне выберите стиль оформления отчета. Щелкните по кнопке Далее .

· В появившемся окне введите название отчета Преподаватели .

· Щелкните по кнопке Готово. На экране появится сформированный отчет.

· Просмотрите отчет. Зайдите в режим конструктора отчетов (кнопка ) и измените название поля Sum на ИТОГО:. Для этого в режиме конструктора щелкните правой кнопкой мыши полю Sum. В раскрывшемся контекстном меню выберите Свойства .

· Выберите вкладку Макет , затем свойство Подпись . Удалите слово и введите Итого :.

· Перейдите в режим просмотра отчета, нажав кнопку или выбрав команду меню Вид/ Предварительный просмотр.

· Просмотрите, а затем закройте отчет.

· Завершите работу с СУБД MS Access.

Занятие 3. Связи между таблицами.

Виды связей.

3.1.1. Связь 1:1 (один к одному).

При связи 1:1 (один к одному) каждой записи первой таблицы соответствует одна запись второй и наоборот.

Например:

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

Такой вид связи встречается редко. Графически изображается:

3.1.2. Связь 1:M (один ко многим).

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

Например:

Таблицы связаны по трем полям «Код.фак.», «Номер группы» и «Фамилия И.О.», в первой таблице по этим полям должен существовать уникальный индекс или ключ, во второй таблице индекс, начинающийся с этих полей.

Это основной вид связи, встречается наиболее часто. Графически изображается:

Первая таблица называется родительской (предком), вторая дочерней (потомком).

3.1.3. Связь M:M (много ко многим).

Одной записи первой таблицы может соответствовать одна или несколько записей второй таблицы, либо не соответствовать ни одной записи и наоборот.

Например:

Каждый студент сдает зачеты и экзамены по многим предметам. По каждому предмету сдают зачеты и экзамены многие студенты.

Графическое изображение:

Например:

3.2. Создание базы данных со связью 1:М.

Создание таблиц.

Создадим базу данных «Продажа автомобилей», состоящую из двух таблиц: «Поставщики» и «Автомобили», связанную отношением 1:M.

14. Запустите MS Access: Пуск/ Программы/ Microsoft Access .

15. В диалоговом окне при старте Access выберите опцию Создание базы данных – Новая база данных и щелкните ОК . В диалоговом окне Файл новой базы данных выберите свою папку и задайте имя базы данных Автомагазин.mdb .

16. В окне СУБД Access выберите объект Таблицы , в правой области окна выберите вариант Создание таблицы в режиме конструктора.

17. В режиме конструктора таблицы в столбце Имя поля введите имя Марка . В столбце Тип данных оставьте тип Текстовый . В столбце Описание введите описание данных, которые будет содержать это поле, например, марка автомобиля . Перейдите в бланк Свойства поля в нижней части окна и задайте значения Размер поля: 30 символов .

18. Действуя аналогично, задайте названия для полейОбъем двигателя, Цвет , укажите тип и свойства данных для этих полей, в соответствии с таблицей:

6. Щелкните по полю «Тип кузова». Перейдите на вкладку Подстановка. Выберите тип элемента управленияСписок.

7. Выберите Тип источника строк - Список значений . В стоку Источник строк введите: седан;комби;хетчбек .

8. После ввода описания всех полей таблицы укажите ключевое поле «номер кузова», для чего щелкнув область выделения строки с записью поля Номер кузова , нажмите кнопку Ключевое поле на панели инструментов. После этого в области выделения поля Номер кузова появится знак ключевого поля – ключ .

9. Сохраните структуру таблицы командой Файл/ Сохранить как . В диалоговом окне Сохранение задайте имя таблицы Автомобили , в поле Как выберите вариант Таблица и щелкните ОК для сохранения.

10. Закройте окно конструктора таблицы. После этого в окне базы данных Автомагазин на вкладке Таблицы появится новый объект – таблица Автомобили .

11. Сохраните таблицу, щелкнув кнопку Сохранить на панели инструментов, и закройте ее.

12. Создайте таблицу Поставщики , описав поля следующим образом:

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

Создание связи между таблицами.

Установим связь между таблицами Автомобили и Поставщики. Для этого:

1. Выберем команду Схема данных в меню Сервис . После этого раскроется пустое окноСхема данных , а в главном меню Access появится новый пункт меню Связи .

2. В диалоговом окне Добавление таблицы выберем вкладкуТаблицы . Выбирая из списка таблиц открытой базы данных Автомагазин и щелкая кнопкуДобавить , добавим в окно схемы данных таблицы Автомобили и Поставщики.

3. Закроем окно Добавление таблицы , щелкнув кнопку Закрыть .

4. Для установки связи между двумя таблицами выделим имя поля с первичным ключом (Фирма) главной таблицы Поставщики и перетащим его, используя левую кнопку мыши, на поле Поставщик подчиненной таблицыАвтомобили. Как только вы отпустите левую кнопку мыши, на экране появится диалоговое окно Изменение связей .

5. Для включения механизма поддержки целостности данных в связываемых таблицах установите флажок Обеспечение целостности данных. Активизируем флажок Обеспечение целостности данных, а затем включим переключатели каскадной модификации – обновления и удаления связанных записей.

6. Завершим создание связи, щелкнув кнопку Создать . В окне Схема данных появится графическое изображение установленной связи. Пометки у конца линии связи означают, что одна запись таблицы Поставщики может иметь сколько угодно связанных записей в таблицеАвтомобили.

Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы демонстрировали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором пользователь может ввести конкретное значение и затем получить нужный результат.

Покажем, как создавать запросы с параметрами на примере запроса "Отсортированный список товаров", который мы создавали ранее. Теперь мы с помощью этого запроса попробуем отобрать товары, поставляемые определенным поставщиком. Для этого:

1. Откройте данный запрос в режиме Конструктора.
2. Чтобы определить параметр запроса, введите в строку Условие отбора (Criteria) для столбца "Название" (CompanyName) вместо конкретного значения слово или фразу и заключите их в квадратные скобки, например [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
3. Если вы хотите, чтобы Access проверяла данные, вводимые в качестве параметра запроса, нужно указать тип данных для этого параметра. Обычно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Если же данные в поле запроса представляют собой даты или числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой кнопкой мыши на свободном поле в верхней части запроса и выберите из контекстного меню команду Параметры (Parameters) или выполните команду меню Запрос, Параметры (Query, Parameters). Появляется диалоговое окно Параметры запроса (Query Parameters), представленное на рис. 4.31.

Параметры запроса" width="441" height="261">

Рис. 4.31. Диалоговое окно Параметры запроса

1. В столбец Параметр (Parameter) нужно ввести название параметра точно так, как он определен в бланке запроса (легче всего это сделать путем копирования через буфер обмена), только можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося списка необходимый тип данных. Нажмите кнопку ОК.
2. Нажмите кнопку Запуск (Run) на панели инструментов, чтобы выполнить запрос. При выполнении запроса появляется диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое нужно ввести значение, например Tokyo Traders. Результат выполнения запроса представлен на рис. 4.33. В него попадают только те товары, которые поставляются данным поставщиком.

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